--==================================================================================================================
--    ScriptName      :      get_login_rights_script.sql
--    Author        :      瀟湘隱者  
--    CreateDate      :      2015-12-18
--    Description      :      查看某個(gè)登錄名被授予的數(shù)據(jù)庫對(duì)象的權(quán)限的腳本（授權(quán)腳本和回收權(quán)限腳本）
--    Note         :      
/******************************************************************************************************************
    Parameters       :                  參數(shù)說明
********************************************************************************************************************
      @login_name     :      你要查看權(quán)限的登錄名(需要輸入替換的參數(shù))
********************************************************************************************************************
  Modified Date  Modified User   Version         Modified Reason
********************************************************************************************************************
  2018-08-03    瀟湘隱者     V01.00.00    新建該腳本。
  2019-04-04    瀟湘隱者     V01.01.00    Fix掉一個(gè)bug，某個(gè)表只允許更新某個(gè)字段，但是這里顯示更新整個(gè)表。
  2019-09-25    瀟湘隱者     V01.02.00    解決只能查看某個(gè)用戶數(shù)據(jù)庫，不能查看所有數(shù)據(jù)庫的權(quán)限問題。
  2019-09-25    瀟湘隱者     V01.03.00    解決數(shù)據(jù)庫名包含中劃線[-], 出現(xiàn)下面錯(cuò)誤問題
-------------------------------------------------------------------------------------------------------------------
Msg 911, Level 16, State 1, Line 1
Database 'xxxx' does not exist. Make sure that the name is entered correctly.
-------------------------------------------------------------------------------------------------------------------
*******************************************************************************************************************/
DECLARE @login_name    NVARCHAR(32)= 'test1';
DECLARE @database_name   NVARCHAR(64);
DECLARE @cmdText      NVARCHAR(MAX);
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.#user_db_roles') IS NOT NULL 
  DROP TABLE dbo.#user_db_roles;
CREATE TABLE dbo.#user_db_roles
(
   [DB_NAME]    NVARCHAR(64)
  ,[USER_NAME]  NVARCHAR(64)
  ,[ROLE_NAME]  NVARCHAR(64)
);
IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL
  DROP TABLE dbo.#user_object_rights;
CREATE TABLE dbo.#user_object_rights
(  
  [DATABASE_NAME]    NVARCHAR(128),
  [SCHEMA_NAME]     NVARCHAR(64),
  [OBJECT_NAME]     NVARCHAR(128),
  [USER_NAME]      NVARCHAR(32),
  [PERMISSIONS_TYPE]   CHAR(12),
  [PERMISSION_NAME]   NVARCHAR(128),
  [PERMISSION_STATE]   NVARCHAR(64),
  [CLASS_DESC]      NVARCHAR(64),
  [COLUMN_NAME]     NVARCHAR(32),
  [STATE_DESC]      NVARCHAR(64),
  [GRANT_STMT]      NVARCHAR(MAX),
  [REVOKE_STMT]     NVARCHAR(MAX)
)
INSERT INTO #databases
SELECT database_id ,
    name
FROM  sys.databases
WHERE name NOT IN ('model') AND state = 0; --state_desc=ONLINE 
--登錄名授予的服務(wù)器角色
SELECT UserName    = u.name ,
    ServerRole   = g.name ,
    Type      = u.type,
    Type_Desc    = u.Type_Desc,
    Create_Date   = u.create_date,
    Modify_Date   = u.modify_date, 
    DenyLogin    = l.denylogin
FROM  sys.server_role_members m
    INNER JOIN sys.server_principals g ON g.principal_id = m.role_principal_id
    INNER JOIN sys.server_principals u ON u.principal_id = m.member_principal_id
    INNER JOIN sys.syslogins l ON u.name = l.name
WHERE l.name=@login_name
ORDER BY u.name,g.name;
WHILE 1= 1
BEGIN
  SELECT TOP 1 @database_name= database_name  
  FROM #databases
  ORDER BY database_id;
  IF @@ROWCOUNT =0 
    BREAK;
  SET @cmdText = N'USE ' + QUOTENAME(@database_name) + N';' +CHAR(10)
  --登錄名授予的數(shù)據(jù)庫角色
  SELECT @cmdText += N'INSERT INTO #user_db_roles
            SELECT DB_NAME()   AS [DB_NAME]
                ,M.NAME    AS [USER_NAME]
                ,R.NAME    AS [ROLE_NAME]
            FROM  sys.DATABASE_ROLE_MEMBERS RM
                INNER JOIN sys.DATABASE_PRINCIPALS R ON RM.ROLE_PRINCIPAL_ID = R.PRINCIPAL_ID
                INNER JOIN sys.DATABASE_PRINCIPALS M ON RM.MEMBER_PRINCIPAL_ID = M.PRINCIPAL_ID
            WHERE M.NAME=@p_login_name' + CHAR(10);
  EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name;
  SET @cmdText = N'USE ' +QUOTENAME(@database_name) + N';' +CHAR(10);
  --查看具體對(duì)象的授權(quán)問題
  SELECT @cmdText +=N'INSERT INTO dbo.#user_object_rights
            (  [DATABASE_NAME]   ,
              [SCHEMA_NAME]    ,
              [OBJECT_NAME]    ,
              [USER_NAME]     ,
              [PERMISSIONS_TYPE]  ,
              [PERMISSION_NAME]  ,
              [PERMISSION_STATE]  ,
              [CLASS_DESC]     ,
              [COLUMN_NAME]    ,
              [STATE_DESC]     ,
              [GRANT_STMT]     ,
              [REVOKE_STMT]     
            )
            SELECT DB_NAME()           AS  [DATABASE_NAME]
               , SYS.SCHEMAS.NAME       AS  [SCHEMA_NAME]
               , ob.NAME            AS  [OBJECT_NAME]
               , SYS.DATABASE_PRINCIPALS.NAME AS  [USER_NAME]
               , dp.TYPE            AS  [PERMISSIONS_TYPE]
               , dp.PERMISSION_NAME      AS  [PERMISSION_NAME]
               , dp.STATE           AS  [PERMISSION_STATE]
               , dp.CLASS_DESC         AS  [CLASS_DESC]
               , sc.name            AS  [COLUMN_NAME]
               , dp.STATE_DESC         AS  [STATE_DESC]
               , dp.STATE_DESC + '' '' + dp.PERMISSION_NAME + '' ON [''+ SYS.SCHEMAS.NAME + ''].['' + ob.NAME + ''] TO ['' + SYS.DATABASE_PRINCIPALS.NAME + ''];'' COLLATE LATIN1_GENERAL_CI_AS 
                               AS [GRANT_STMT] 
               , ''REVOKE '' + dp.PERMISSION_NAME + '' ON [''+ SYS.SCHEMAS.NAME + ''].['' + ob.NAME + ''] FROM ['' + SYS.DATABASE_PRINCIPALS.NAME + ''];'' COLLATE LATIN1_GENERAL_CI_AS 
                               AS [REVOKE_STMT]
            FROM SYS.DATABASE_PERMISSIONS dp
            LEFT OUTER JOIN SYS.OBJECTS ob ON dp.MAJOR_ID = ob.OBJECT_ID 
            LEFT OUTER JOIN SYS.SCHEMAS ON ob.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID 
            LEFT OUTER JOIN SYS.DATABASE_PRINCIPALS ON dp.GRANTEE_PRINCIPAL_ID = SYS.DATABASE_PRINCIPALS.PRINCIPAL_ID 
            LEFT OUTER JOIN SYS.columns sc ON ob.object_id = sc.object_id AND sc.column_id = dp.minor_id
            WHERE SYS.DATABASE_PRINCIPALS.NAME =@p_login_name
            ORDER BY PERMISSIONS_TYPE;'
  PRINT(@cmdText);
  EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name;
  DELETE FROM #databases WHERE database_name=@database_name;
END
SELECT * FROM tempdb.dbo.#user_db_roles;
SELECT * FROM dbo.#user_object_rights;
IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL
  DROP TABLE dbo.#databases;
IF OBJECT_ID('tempdb.dbo.#user_db_roles') IS NOT NULL 
  DROP TABLE dbo.#user_db_roles;
IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL
  DROP TABLE dbo.#user_object_rights;