exec sp_MSforeachdb'insert 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
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16131092/viewspace-2143204/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16131092/viewspace-2143204/