服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - SQL Server查看login所授予的具体权限问题

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

2020-05-31 15:18潇湘隐者 Sql Server

在SQL Server数据库中如何查看一个登录名(login)的具体权限呢，下面脚本之家小编给大家带来了SQL Server查看login所授予的具体权限问题，感兴趣的朋友一起看看吧

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
--==================================================================================================================
--    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所授予的具体权限问题,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！

原文链接：https://www.cnblogs.com/kerrycode/archive/2019/09/25/11588227.html

  • SQLSERVER
  • 权限
  • login

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    2005 　 18052 　 1355 　 3456 　 29506 　 9003 　 5120 　 21002 　 5173 　 15138 　 15404 　 错误5123 　 SQL导入 　 sql导出 　 新增 　 sp_executesql 　 xp_cmdshell 　 Transact_SQL 　 手册 　 SQL Server2005 　 网络备份 　 日期查询 　 数据库开发 　 SQL Server 2000 　 经典语句 　 删除重复记录 　 收缩日志 　 排序规则 　 基本函数 　 加密函数 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图