https://ask.sqlservercentral.com/questions/16078/schema-and-role-permissions-for-all-users-in-a-dat.html ...
--https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/managing-logins-users-and-schemas-how-to-topics -- ---SQL Server quering roles, schemas, users,logins 查詢三者的關係 --SQL Server 2000 system table --select * from sysusers --select * from syslogins --SQL Server 2005 and later catalog VIEW SELECT * FROM sys.database_principals SELECT * FROM sys.schemas SELECT * FROM sys.server_principals ---SQL Server 2000 - Using sysprotects SELECT su.name AS 'User' , CASE sp.protecttype WHEN 204 THEN 'GRANT w/ GRANT' WHEN 205 THEN 'GRANT' WHEN 206 THEN 'DENY' END AS 'Permission' , CASE sp.action WHEN 26 THEN 'REFERENCES' WHEN 193 THEN 'SELECT' WHEN 195 THEN 'INSERT' WHEN 196 THEN 'DELETE' WHEN 197 THEN 'UPDATE' WHEN 224 THEN 'EXECUTE' END AS 'Action' , so.name AS 'Object' FROM sysprotects sp INNER JOIN sysusers su ON sp.uid = su.uid INNER JOIN sysobjects so ON sp.id = so.id WHERE sp.action IN (26, 193, 195, 196, 197, 224) ORDER BY su.name, so.name; GO ---SQL Server 2005/2008 - Using sys.database_permissions SELECT USER_NAME(grantee_principal_id) AS 'User' , state_desc AS 'Permission' , permission_name AS 'Action' , CASE class WHEN 0 THEN 'Database::' + DB_NAME() WHEN 1 THEN OBJECT_NAME(major_id) WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable' FROM sys.database_permissions dp WHERE class IN (0, 1, 3) AND minor_id = 0; GO IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[SQL_DB_REP]') ) DROPTABLE [tempdb].[dbo].[SQL_DB_REP] ; /*I intentionally left out the space */ GO CREATE TABLE [tempdb].[dbo].[SQL_DB_REP] ( [Server] [varchar](100) NOT NULL, [DB_Name] [varchar](70) NOT NULL, [User_Name] [nvarchar](90) NULL, [Group_Name] [varchar](100) NULL, [Account_Type] [varchar](22) NULL, [Login_Name] [varchar](80) NULL, [Def_DB] [varchar](100) NULL ) ON [PRIMARY] INSERT INTO [tempdb].[dbo].[SQL_DB_REP] Exec sp_MSForEachDB 'SELECT CONVERT(varchar(100), SERVERPROPERTY(''Servername'')) AS Server, ''?'' as DB_Name, usu.name u_name ,CASE WHEN (usg.uid is null) then ''public'' ELSE usg.name END as Group_Name ,CASE WHEN usu.isntuser=1 then ''Windows Domain Account'' WHEN usu.isntgroup = 1 then ''Windows Group'' WHEN usu.issqluser = 1 then ''SQL Account'' WHEN usu.issqlrole = 1 then ''SQL Role'' END as Account_Type ,lo.loginname ,lo.dbname as Def_DB FROM [?]..sysusers usu LEFT OUTER JOIN ([?]..sysmembers mem INNER JOIN [?]..sysusers usg ON mem.groupuid = usg.uid) ON usu.uid = mem.memberuid LEFT OUTER JOIN master.dbo.syslogins lo on usu.sid = lo.sid WHERE (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1) and (usg.issqlrole = 1 or usg.uid is null)' GO SELECT [Server], [DB_Name], [User_Name], [Group_Name], [Account_Type], [Login_Name], [Def_DB] FROM [tempdb].[dbo].[SQL_DB_REP] GO
https://ask.sqlservercentral.com/questions/16078/schema-and-role-permissions-for-all-users-in-a-dat.html