SQL Server查看login所授予的具体权限问题

江南才子

　　在SQL Server数据库中如何查看一个登录名(login)的具体权限呢，如果使用SSMS的UI界面查看登录名的具体权限的话，用户数据库非常多的话，要梳理完它所有的权限，操作又耗时又麻烦，个人十分崇尚简洁、高效的方法，反感那些需要大量手工操作的UI界面操作方式，哪怕就是脚本，如果不能一次搞定，手工多操作几次(例如，切换数据库)，都是不可接受的。最近遇到这个需求，就完善了一下之前的脚本get_login_rights_script.sql，输入登录名参数，将这个登录名所拥有的服务器角色、数据库角色、以及所授予具体对象的相关权限使用脚本查询出来，脚本分享如下：

--==================================================================================================================
--  ScriptName    :    get_login_rights_script.sql
--  Author    :    潇湘隐者  
--  CreateDate    :    2015-12-18
--  Description    :    查看某个登录名被授予的数据库对象的权限的脚本（授权脚本和回收权限脚本）
--  Note     :    
/******************************************************************************************************************
  Parameters     :          参数说明
********************************************************************************************************************
    @login_name   :    你要查看权限的登录名(需要输入替换的参数)
********************************************************************************************************************
  Modified Date  Modified User   Version     Modified Reason
********************************************************************************************************************
  2018-08-03  潇湘隐者   V01.00.00  新建该脚本。
  2019-04-04  潇湘隐者   V01.01.00  Fix掉一个bug，某个表只允许更新某个字段，但是这里显示更新整个表。
  2019-09-25  潇湘隐者   V01.02.00  解决只能查看某个用户数据库，不能查看所有数据库的权限问题。
  2019-09-25  潇湘隐者   V01.03.00  解决数据库名包含中划线[-], 出现下面错误问题
-------------------------------------------------------------------------------------------------------------------
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 
--登录名授予的服务器角色
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)
  --登录名授予的数据库角色
  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);
  --查看具体对象的授权问题
  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;

　　总结
　　以上所述是小编给大家介绍的SQL Server查看login所授予的具体权限问题,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！