mysql login 权限_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所授予的具体权限问题,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！

如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！