MSSQL怎么看各个ID对应具体DB的角色

这篇文章主要为大家展示了“MSSQL怎么看各个ID对应具体DB的角色”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MSSQL怎么看各个ID对应具体DB的角色”这篇文章吧。

exec sp_MSforeachdbinsert into dbcenter..user_role_map ([DB_NAME],[uid],[uStatus],[uName],[rId],[rStatus],[rName])select ?,a.uid as uid,a.status as uStatus,a.name as uName,

  b.uid as rId,b.status as rStatus,b.name as rName

from sysusers a left join sysmembers m on m.memberuid = a.uid

    left join sysusers b on b.gid = m.groupuid

where a.issqluser =1

--select substring(name, 1, 70) Login Name,dbname from master..syslogins

--用户与角色关系

USE [DBCenter]

GO

--drop table [DBCenter].[dbo].[user_role_map]

CREATE TABLE [dbo].[user_role_map](

[DB_NAME] [varchar](20) NOT NULL,

[uid] [int] NULL,

[uStatus] [int] NULL,

[uName] [sysname] NOT NULL,

[rId] [int] NULL,

[rStatus] [int] NULL,

[rName] [sysname] NULL

) ON [PRIMARY]

use master 

go

exec sp_MSforeachdb

insert into dbcenter..user_role_map([DB_NAME],[uid],[uStatus],[uName],[rId],[rStatus],[rName])

select ? as db_name,a.uid as uid,a.status as uStatus,a.name as uName,

  b.uid as rId,b.status as rStatus,b.name as rName

from sysusers a left join sysmembers m on m.memberuid = a.uid

    left join sysusers b on b.gid = m.groupuid

where a.issqluser =1

go

select * from dbcenter..user_role_map

以上是“MSSQL怎么看各个ID对应具体DB的角色”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！