如何 列出 SQL Server 資料庫 的使用者權限

  • Article

情境:

列出資料庫使用者權限清單

語法:

USE <Database Name>

GO

DECLARE @Obj VARCHAR(4000)

DECLARE @T_Obj TABLE (UserName SYSNAME, ObjectName SYSNAME, Permission NVARCHAR(128))

SET @Obj='

SELECT Us.name AS username, Obj.name AS object,  dp.permission_name AS permission

FROM sys.database_permissions dp

JOIN sys.sysusers Us

ON dp.grantee_principal_id = Us.uid

JOIN sys.sysobjects Obj

ON dp.major_id = Obj.id '

INSERT @T_Obj

EXEC sp_MSforeachdb @Obj

SELECT distinct(a.UserName),a.ObjectName,a.Permission FROM @T_Obj a

 

Enjoy. Jacky