--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