[SCRIPT] – Come assegnare i permessi a un ruolo (Database Role) per tutti gli oggetti

Con questo script è possibile verificare e, dopo, assegnare i permessi necessari a un database role (ruolo) specifico. È interessante anche,  esibire i risultati di questo script in modalità di testo per, dopo, eseguirli come una istruzione T-SQL (GRANT). Si deve informare il ruolo (database role) desiderato in SET @DBrole = 'INSERIRE_Nome_del_Role'  , sostituendo il nome del ruolo, ovviamente:

DECLARE @DBrole SYSNAME
-- DECLARAR VARIAVEIS
SET @DBrole = 'INSERIRE_Nome_del_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

    Fonte: https://technet.microsoft.com/it-it/library/cc645510.aspx