SQL Server中通用資料庫角色許可權處理

SQL Server中通用資料庫角色許可權處理

最近和同事在做資料庫許可權清理的事情，主要是刪除一些賬號；取消一些賬號的較大的許可權等，例如，有一些有db_owner許可權，我們取消賬號的資料庫角色db_owner，授予最低要求的相關許可權。但是這種工作完全是一個體力活，而且是吃力不討好，而且推進很慢。另外，為了管理方便和細化，我們又在常用的資料庫角色外，新增了6個通用的資料庫角色。如下截圖所示。

另外，為了減少授權工作量和一些重覆的體力活，我們創建了一個作業，每天定期執行一個存儲過程db_common_role_grant_rigths，這個存儲過程的邏輯如下：

    1：遍歷所有用戶資料庫（排除了系統資料庫以及一些特殊資料庫），發現該資料庫不存在這些通用資料庫角色，那麼就創建相關資料庫角色。

    2：遍歷所有用戶資料庫，為相關資料庫角色授權，例如，如果發現某個新增的存儲過程，沒有授權給db_procedure_execute資料庫角色。那麼就執行授權操作。

當然目前還在測試、應用階段，以後會根據具體相關需求，不斷完善相關功能。

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

--        ScriptName            :            db_common_role_grant_rigths.sql

--        Author                :            瀟湘隱者    

--        CreateDate            :            2018-09-13

--        Description           :            創建資料庫角色db_procedure_execute等，並授予相關許可權給角色。

--        Note                  :            

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

        Parameters              :                                    參數說明

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

             @RoleName          :            角色名

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

   Modified Date    Modified User     Version                 Modified Reason

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

    2018-09-12       瀟湘隱者         V01.00.00      新建該腳本。

    2018-09-12       瀟湘隱者         V01.00.01      註意@@ROWCOUNT的生效範圍;解決迴圈邏輯問題。

    2018-09-26       瀟湘隱者         V01.00.02      修正類型為FT(CLR_TABLE_VALUED_FUNCTION)的函數問題。程式集 (CLR) 表值函數

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

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

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 

--開始迴圈每一個用戶資料庫（排除了上面相關資料庫）

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 中切換資料庫不能當參數傳入。

    --創建資料庫角色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);

    --創建資料庫角色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);

    --創建資料庫角色db_view_table_definition

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

                        BEGIN