General database role privilege handling in SQL Server

Recently, we have been doing database privilege cleanup with colleagues, mainly by deleting some accounts, removing the larger privileges of some accounts, etc. For example, some have db_owner privileges, we remove the database role db_owner of the accounts and grant the minimum required related privileges.But it's all manual, unpleasant, and slowly advancing.In addition, for ease of management and refinement, we have added 6 common database roles in addition to the common database roles.As shown in the following screenshot.

In addition, to reduce the authorization workload and some duplicate physical effort, we created a job that executes a stored procedure, db_common_role_grant_rigths, periodically every day, with the following logic:

1: Traverse through all user databases (excluding system databases and some special databases) and find that these common database roles do not exist in the database, then create the relevant database roles.

2: Traverse all user databases to authorize related database roles, for example, if a new stored procedure is found, the db_procedure_execute database role is not authorized.Then perform the authorization operation.

Of course, it is still in the testing and application stage, and will continue to improve related functions in the future according to specific related needs.

--==================================================================================================================

--        ScriptName            :            db_common_role_grant_rigths.sql

--        Author                :            Xiao Xiang Reclusive    

--        CreateDate            :            2018-09-13

--        Description           :            Create Database Roles db_procedure_execute And grant relevant privileges to roles.

--        Note                  :            

/******************************************************************************************************************

        Parameters              :                                    Parameter Description

********************************************************************************************************************

             @RoleName          :            Role Name

********************************************************************************************************************

   Modified Date    Modified User     Version                 Modified Reason

********************************************************************************************************************

    2018-09-12       Xiao Xiang Yin Zhi * V01.00.00 * Create a new script.

    2018-09-12       Xiao Xiang Yin Li V01.00.01 Note the effective range of @@ROWCOUNT; solve the circular logic problem.

    2018-09-26       Xiao Xiangyin  V01.00.02  Correct the function problem of FT(CLR_TABLE_VALUED_FUNCTION).Assembly (CLR) table-valued functions

*******************************************************************************************************************/

--===================================================================================================================

USE YourSQLDba;

GO

IF EXISTS (SELECT 1 FROM sys.procedures WHERE type='P' AND name='db_common_role_grant_rigths')

BEGIN

    DROP PROCEDURE Maint.db_common_role_grant_rigths;

END

GO

CREATE PROCEDURE Maint.db_common_role_grant_rigths

AS

BEGIN

DECLARE @database_id    INT;

DECLARE @database_name  sysname;

DECLARE @cmdText        NVARCHAR(MAX);

DECLARE @prc_text        NVARCHAR(MAX);

DECLARE @RowIndex        INT;

IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL

    DROP TABLE dbo.#databases;

CREATE TABLE #databases

(

    database_id        INT,

    database_name   sysname

)

IF OBJECT_ID('TempDB.dbo.#sql_text') IS NOT NULL

    DROP TABLE dbo.#sql_text;

CREATE TABLE #sql_text

(

    sql_id      INT IDENTITY(1,1),

    sql_cmd     NVARCHAR(MAX)

)

INSERT  INTO #databases

SELECT  database_id ,

        name

FROM    sys.databases

WHERE   name NOT IN ( 'master', 'tempdb', 'model', 'msdb',

                        'distribution', 'ReportServer',

                        'ReportServerTempDB', 'YourSQLDba' )

        AND state = 0; --state_desc=ONLINE 

--Start looping through each user database (excluding related databases above)

WHILE 1= 1

BEGIN

    SELECT TOP 1 @database_name= database_name   

    FROM #databases

    ORDER BY database_id;

    IF @@ROWCOUNT =0 

        BREAK;

    --PRINT(@database_name);

    -- SP_EXECUTESQL Switching database in cannot be passed in as a parameter.

    --Create Database Roles db_procedure_execute

    SET @cmdText =  'USE ' + @database_name + ';' +CHAR(10)

    SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_procedure_execute'')

                        BEGIN

                            CREATE ROLE [db_procedure_execute] AUTHORIZATION [dbo];

                        END ' + CHAR(10);

    --Create Database Roles db_function_execute

    SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_function_execute'')

                        BEGIN

                            CREATE ROLE [db_function_execute] AUTHORIZATION [dbo];

                        END' + CHAR(10);

    --Create Database Roles db_view_table_definition

    SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_view_table_definition'')

                        BEGIN

                            CREATE ROLE [db_view_table_definition] AUTHORIZATION [dbo];

                        END ' + CHAR(10);

    --Create Database Roles db_view_view_definition

    SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_view_view_definition'')

                         BEGIN

                            CREATE ROLE [db_view_view_definition] AUTHORIZATION [dbo];

                         END ' + CHAR(10);

    --Create Database Roles db_view_procedure_definition

    SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_view_procedure_definition'')

                        BEGIN

                            CREATE ROLE [db_view_procedure_definition] AUTHORIZATION [dbo];

                        END ' + CHAR(10);

     --Create Database Roles db_view_function_definition

    SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_view_function_definition'')

                        BEGIN

                            CREATE ROLE [db_view_function_definition] AUTHORIZATION [dbo];

                        END ' + CHAR(10);

    --PRINT @cmdText;

    -- EXECUTE SP_EXECUTESQL @cmdText;

    EXECUTE (@cmdText);

    --Give Role db_procedure_execute To grant authorization

    SET @cmdText ='USE ' + QUOTENAME(@database_name) + ';' 

    SET @cmdText +='INSERT INTO #sql_text(sql_cmd)

                    SELECT  ''GRANT EXECUTE  ON '' + SCHEMA_NAME(schema_id) + ''.''

                       + QUOTENAME(name) + '' TO db_procedure_execute;''

                       FROM   sys.procedures s

                       WHERE     NOT EXISTS ( SELECT 1

                                             FROM   sys.database_permissions p

                                             WHERE  p.major_id = s.object_id 

                                                    AND  p.grantee_principal_id = USER_ID(''db_procedure_execute''))';

     EXECUTE SP_EXECUTESQL @cmdText;

     --Give Role db_function_execute(Scalar Function Authorization)

     SET @cmdText ='USE ' + QUOTENAME(@database_name) + ';' 

     SET @cmdText += 'INSERT INTO #sql_text(sql_cmd)

                     SELECT  ''GRANT EXEC ON '' + SCHEMA_NAME(schema_id) + ''.'' + QUOTENAME(name) + '' TO  db_function_execute; ''  

                     FROM    sys.all_objects s

                     WHERE  SCHEMA_NAME(schema_id) NOT IN (''sys'', ''INFORMATION_SCHEMA'')  

                        AND    NOT EXISTS ( SELECT 1

                                                FROM   sys.database_permissions p

                                                WHERE  p.major_id = s.object_id 

                                                AND  p.grantee_principal_id =USER_ID(''db_function_execute'') )

                                                AND ( s.[type] = ''FN''

                                                        OR s.[type] = ''AF''

                                                        OR s.[type] = ''FS''

                                                        --OR s.[type] = ''FT''

                                                    ) ;'

        EXECUTE SP_EXECUTESQL @cmdText;

      --Give Role db_function_execute(Table-valued Function Authorization)

      SET @cmdText ='USE ' + @database_name + ';'

      SET @cmdText += 'INSERT INTO #sql_text(sql_cmd)

                       SELECT  ''GRANT SELECT ON '' + SCHEMA_NAME(schema_id) + ''.'' + QUOTENAME(name) + '' TO  db_function_execute;''

                       FROM    sys.all_objects s

                       WHERE  SCHEMA_NAME(schema_id) NOT IN (''sys'', ''INFORMATION_SCHEMA'')   

                          AND    NOT EXISTS ( SELECT 1

                                             FROM   sys.database_permissions p

                                             WHERE  p.major_id = s.object_id 

                                                    AND  p.grantee_principal_id = USER_ID(''db_function_execute''))

                                  AND ( s.[type] = ''TF''

                                        OR s.[type] = ''IF''

                            ) ;    '

      EXECUTE SP_EXECUTESQL @cmdText;

      --View stored procedure definition authorization

      SET @cmdText ='USE ' + @database_name + ';'

      SET @cmdText +=' INSERT INTO #sql_text(sql_cmd)

                       SELECT  ''GRANT VIEW DEFINITION ON '' + SCHEMA_NAME(schema_id) + ''.''

                       + QUOTENAME(name) + '' TO db_view_procedure_definition;'' 

                       FROM   sys.procedures s

                       WHERE     NOT EXISTS ( SELECT 1

                                             FROM   sys.database_permissions p

                                             WHERE  p.major_id = s.object_id 

                                                    AND  p.grantee_principal_id = USER_ID(''db_view_procedure_definition''))'

       EXECUTE(@cmdText);

       --View authorization for function definitions

       SET @cmdText ='USE ' + @database_name + ';'

       SELECT   @cmdText += 'INSERT INTO #sql_text(sql_cmd)

                            SELECT ''GRANT VIEW DEFINITION ON '' + SCHEMA_NAME(schema_id) + ''.''

                            + QUOTENAME(name) + '' TO  db_view_function_definition;'' 

                            FROM sys.objects s

                            WHERE type_desc IN (''SQL_SCALAR_FUNCTION'', ''SQL_TABLE_VALUED_FUNCTION'',

                                 ''AGGREGATE_FUNCTION'' )

                                    AND    NOT EXISTS ( SELECT 1

                                             FROM   sys.database_permissions p

                                             WHERE  p.major_id = s.object_id 

                                                    AND  p.grantee_principal_id = USER_ID(''db_view_function_definition''))';

        EXECUTE SP_EXECUTESQL @cmdText;

       --View authorization defined in table

       SET @cmdText ='USE ' + @database_name + ';'

       SET @cmdText +='INSERT INTO #sql_text(sql_cmd)

                      SELECT ''GRANT VIEW DEFINITION ON '' + SCHEMA_NAME(schema_id) + ''.''

                      + QUOTENAME(name) + '' TO db_view_table_definition ;'' 

                      FROM  sys.tables s

                      WHERE  NOT EXISTS ( SELECT 1

                                             FROM   sys.database_permissions p

                                             WHERE  p.major_id = s.object_id 

                                                    AND  p.grantee_principal_id = USER_ID(''db_view_table_definition''))';

       EXECUTE SP_EXECUTESQL @cmdText;

       --View authorization defined by view

       SET @cmdText ='USE ' + @database_name + ';'

       SET @cmdText +='INSERT INTO #sql_text(sql_cmd)

                      SELECT  ''GRANT VIEW DEFINITION ON '' + SCHEMA_NAME(schema_id) + ''.''

                             + QUOTENAME(name) + '' TO db_view_view_definition; ''

                      FROM    sys.views s

                      WHERE  NOT EXISTS ( SELECT 1

                                             FROM   sys.database_permissions p

                                             WHERE  p.major_id = s.object_id 

                                                    AND  p.grantee_principal_id = USER_ID(''db_view_view_definition''))';

       EXECUTE SP_EXECUTESQL @cmdText;

        WHILE 1= 1

        BEGIN

            SELECT TOP 1 @RowIndex=sql_id, @cmdText =  'USE ' + @database_name + '; '+ sql_cmd FROM #sql_text ORDER BY sql_id;

            IF @@ROWCOUNT =0 

                BREAK;

            PRINT(@cmdText);

            EXECUTE(@cmdText);

            DELETE FROM #sql_text WHERE sql_id =@RowIndex

        END

     DELETE FROM #databases WHERE database_name=@database_name;

END

     DROP TABLE #databases;

     DROP TABLE #sql_text;

END