--==================================================================================================================--  ScriptName   :   db_common_role_grant_rigths.sql--  Author    :   瀟湘隱者 --  CreateDate   :   2018-09-13--  Description   :   創(chuàng)建數(shù)據(jù)庫角色db_procedure_execute等，并授予相關(guān)權(quán)限給角色。--  Note     :   /******************************************************************************************************************  Parameters    :         參數(shù)說明********************************************************************************************************************    @RoleName   :   角色名******************************************************************************************************************** Modified Date Modified User  Version     Modified Reason******************************************************************************************************************** 2018-09-12  瀟湘隱者   V01.00.00  新建該腳本。 2018-09-12  瀟湘隱者   V01.00.01  注意@@ROWCOUNT的生效范圍;解決循環(huán)邏輯問題。 2018-09-26  瀟湘隱者   V01.00.02  修正類型為FT(CLR_TABLE_VALUED_FUNCTION)的函數(shù)問題。程序集 (CLR) 表值函數(shù)*******************************************************************************************************************/--===================================================================================================================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;ENDGO CREATE PROCEDURE Maint.db_common_role_grant_rigthsASBEGIN 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 #databasesSELECT database_id ,  nameFROM sys.databasesWHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb',      'distribution', 'ReportServer',      'ReportServerTempDB', 'YourSQLDba' )  AND state = 0; --state_desc=ONLINE   --開始循環(huán)每一個用戶數(shù)據(jù)庫（排除了上面相關(guān)數(shù)據(jù)庫）WHILE 1= 1BEGIN   SELECT TOP 1 @database_name= database_name  FROM #databases ORDER BY database_id;   IF @@ROWCOUNT =0   BREAK;  --PRINT(@database_name);  -- SP_EXECUTESQL 中切換數(shù)據(jù)庫不能當(dāng)參數(shù)傳入。  --創(chuàng)建數(shù)據(jù)庫角色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);    --創(chuàng)建數(shù)據(jù)庫角色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);   --創(chuàng)建數(shù)據(jù)庫角色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);  --創(chuàng)建數(shù)據(jù)庫角色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);  --創(chuàng)建數(shù)據(jù)庫角色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);   --創(chuàng)建數(shù)據(jù)庫角色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);    --給角色db_procedure_execute授權(quán)  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;      --給角色db_function_execute(標量函數(shù)授權(quán))   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;     --給角色db_function_execute(表值函數(shù)授權(quán))  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;    --查看存儲過程定義授權(quán)  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);   --查看函數(shù)定義的授權(quán)  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;    --查看表定義的授權(quán)  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;    --查看視圖定義的授權(quán)  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