怎么在SQL Server中處理數(shù)據(jù)庫(kù)角色權(quán)限

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)怎么在SQL Server中處理數(shù)據(jù)庫(kù)角色權(quán)限，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

    1：遍歷所有用戶數(shù)據(jù)庫(kù)（排除了系統(tǒng)數(shù)據(jù)庫(kù)以及一些特殊數(shù)據(jù)庫(kù)），發(fā)現(xiàn)該數(shù)據(jù)庫(kù)不存在這些通用數(shù)據(jù)庫(kù)角色，那么就創(chuàng)建相關(guān)數(shù)據(jù)庫(kù)角色。

    2：遍歷所有用戶數(shù)據(jù)庫(kù)，為相關(guān)數(shù)據(jù)庫(kù)角色授權(quán)，例如，如果發(fā)現(xiàn)某個(gè)新增的存儲(chǔ)過(guò)程，沒(méi)有授權(quán)給db_procedure_execute數(shù)據(jù)庫(kù)角色。那么就執(zhí)行授權(quán)操作。

當(dāng)然目前還在測(cè)試、應(yīng)用階段，以后會(huì)根據(jù)具體相關(guān)需求，不斷完善相關(guān)功能。

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 
 
 
--開(kāi)始循環(huán)每一個(gè)用戶數(shù)據(jù)庫(kù)（排除了上面相關(guān)數(shù)據(jù)庫(kù)）
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 中切換數(shù)據(jù)庫(kù)不能當(dāng)參數(shù)傳入。
 
 --創(chuàng)建數(shù)據(jù)庫(kù)角色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ù)庫(kù)角色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ù)庫(kù)角色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ù)庫(kù)角色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ù)庫(kù)角色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ù)庫(kù)角色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(標(biāo)量函數(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;
 
 
  --查看存儲(chǔ)過(guò)程定義授權(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

上述就是小編為大家分享的怎么在SQL Server中處理數(shù)據(jù)庫(kù)角色權(quán)限了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。