sql server: quering roles, schemas, users,logins

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109

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