在SQL Server数据库中如何查看一个登录名(login)的具体权限呢，如果使用SSMS的UI界面查看登录名的具体权限的话，用户数据库非常多的话，要梳理完它所有的权限，操作又耗时又麻烦，个人十分崇尚简洁、高效的方法，反感那些需要大量手工操作的UI界面操作方式，哪怕就是脚本，如果不能一次搞定，手工多操作几次(例如，切换数据库)，都是不可接受的。最近遇到这个需求，就完善了一下之前的脚本get_login_rights_script.sql，输入登录名参数，将这个登录名所拥有的服务器角色、数据库角色、以及所授予具体对象的相关权限使用脚本查询出来，脚本分享如下：
--==================================================================================================================
-- scriptName : get_login_rights_script.sql
-- Author : 潇湘隐者
-- CreateDate : 2015-12-18
-- Description : 查看某个登录名被授予的数据库对象的权限的脚本（授权脚本和回收权限脚本）
-- Note :
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.NAMEAS [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所授予的具体权限问题,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对考高分网网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
