Com este script, é possível verificar e, em seguida, atribuir as permissões necessárias para um Role específica.
É interessante também exibir os resultados deste script como texto para depois executá-los como uma instrução T-SQL (Grants)
Deve-se informar a Role desejada em SET @DBrole = 'INSERIR_Nome_da_Role', substituindo pelo nome da Role desejada:
DECLARE @DBrole SYSNAME
-- DECLARAR VARIAVEIS
SET @DBrole = 'INSERIR_Nome_da_Role'
-- PRINT COM LEGENDA AUXILIAR
PRINT 'LEGENDA'
PRINT '-------------------------------'
PRINT 'U = TABELA'
PRINT 'P = PROCEDURE'
PRINT 'V = VIEW'
PRINT 'FN = FUNÇÃO ESCALAR'
PRINT 'IF = FUNÇÃO DE TABELA EM LINHA'
PRINT 'TF = FUNÇÃO DE TABELA'
PRINT '--------------------------------'
-- SELECT DE TODOS OS OBJETOS SEM PERMISSÃO
SELECT xtype as 'TIPO', USER_NAME(uid)+'.'+ name as 'NOME DOS OBJETOS SEM PERMISSÕES'
FROM sysobjects
WHERE xtype IN ('FN','P','U','IF','V', 'TF')
AND base_schema_ver < 16
AND id NOT IN (SELECT o.id FROM syspermissions p LEFT JOIN sysusers u ON p.grantee = u.uid LEFT JOIN sysobjects o ON p.id = o.id
WHERE lower(u.name) = lower(@DBrole))
order by xtype
-- INICIO DAS GRANTS
-- GRANT EXECUTE ON … [FUNCTION E PROCEDURE]
PRINT '[FUNCTION E PROCEDURE]- GRANT EXECUTE'
SELECT 'GRANT EXECUTE ON ' + QUOTENAME(USER_NAME(uid)) + '.' + QUOTENAME(name) + ' TO ' + QUOTENAME(@DBrole)
FROM sysobjects
WHERE xtype IN ('FN', 'P')
AND base_schema_ver <> 16
AND id NOT IN (SELECT o.id
FROM syspermissions p LEFT JOIN sysusers u ON p.grantee = u.uid LEFT JOIN sysobjects o ON p.id = o.id
WHERE lower(u.name) = lower(@DBrole))
order by name
-- GRANT SELECT ON … [VIEW, FUNCAO ESCALAR]
PRINT '[VIEW, TABLE FUNCTION E IN-LINED TABLE FUNCTION] - GRANT SELECT'
SELECT 'GRANT SELECT ON ' + QUOTENAME(USER_NAME(uid)) + '.' + QUOTENAME(name) + ' TO ' + QUOTENAME(@DBrole)
FROM sysobjects
WHERE xtype IN ('V','IF','TF')
AND base_schema_ver <> 16
AND id NOT IN (SELECT o.id
FROM syspermissions p LEFT JOIN sysusers u ON p.grantee = u.uid LEFT JOIN sysobjects o ON p.id = o.id
WHERE lower(u.name) = lower(@DBrole))
order by name
-- GRANT SELECT INSERT UPDATE DELETE ON … [TABELA]
PRINT '[TABELA] - GRANT SELECT, INSERT, UPDATE, DELETE'
SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' + QUOTENAME(USER_NAME(uid)) + '.' + QUOTENAME(name) + ' TO ' + QUOTENAME(@DBrole)
FROM sysobjects
WHERE xtype = 'U'
AND base_schema_ver <> 16
AND id NOT IN (SELECT o.id
FROM syspermissions p LEFT JOIN sysusers u ON p.grantee = u.uid LEFT JOIN sysobjects o ON p.id = o.id
WHERE lower(u.name) = lower(@DBrole))
order by name
