DB移行などで、新・旧のデータベースサーバのユーザを比較する時に利用します。
サーバロールの付与漏れの確認に利用ください。
下記の個所を比較するDBサーバ名に書き換えてください。
SET @DATABASEA = ‘DBサーバ１’
SET @DATABASEB = ‘DBサーバ２’
サンプルでは、OPENROWSETにWindows認証を利用していますので、ダブルホップ接続にならない様に気を付けてください。
不明な場合は、DBサーバ１かDBサーバ２にログインして、ログインしたサーバに対して実行してください。
また、Ad Hoc Distributed Queriesを有効にする必要があります。
-- サーバロール比較 全DB
DECLARE @DATABASEA varchar(50)
DECLARE @DATABASEB varchar(50)
-----
--比較するサーバを指定します
-----
SET @DATABASEA = 'DBサーバ１'
SET @DATABASEB = 'DBサーバ２'
IF object_id('tempdb..#dbname') IS NOT NULL
BEGIN
drop table #dbname
END
IF object_id('tempdb..#user_itiran') IS NOT NULL
BEGIN
drop table #user_itiran
END
create table #dbname (
[SERVER_NAME] varchar(256)
,[DB_NAME] varchar(256)
PRIMARY KEY CLUSTERED
(
[SERVER_NAME] ASC,
[DB_NAME] ASC
)
)
create table #user_itiran(
[Server名] varchar(256),
[DB名] varchar(256),
[Login Type] sysname,
[srvLogin] varchar(100),
[srvRole] varchar(100),
[dbUser] varchar(100),
[dbRole] varchar(100)
)
declare @MYSERVER_NAME as varchar(256)
declare @MYDB_NAME as varchar(256)
declare @MYsql as varchar(4000)
declare serverlist_cur CURSOR FOR
SELECT @DATABASEA DBNAME
UNION SELECT @DATABASEB
OPEN serverlist_cur
FETCH NEXT FROM serverlist_cur INTO @MYSERVER_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @MYsql='
insert into #dbname
select *
FROM OPENROWSET(''SQLNCLI'', ''Server='+@MYSERVER_NAME+';Trusted_Connection=yes;'',
''select @@SERVERNAME,name from sys.sysdatabases '')
'
exec(@MYsql)
-- データを取得
FETCH NEXT FROM serverlist_cur INTO @MYSERVER_NAME
END
CLOSE serverlist_cur
DEALLOCATE serverlist_cur
------
--取得したサーバ、DBリストをもとにユーザ、権限を取得
declare serverlist_cur CURSOR FOR
SELECT * from #dbname
OPEN serverlist_cur
FETCH NEXT FROM serverlist_cur INTO @MYSERVER_NAME, @MYDB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @MYsql='
insert into #user_itiran
select *
FROM OPENROWSET(''SQLNCLI'', ''Server='+@MYSERVER_NAME+';database=' + @MYDB_NAME + ';Trusted_Connection=yes;'',
''
select
''''' + @MYSERVER_NAME + ''''' as サーバ名,
''''' + @MYDB_NAME + ''''' as DB名,
[Login Type]=
case sp.type
when ''''u'''' then ''''WIN''''
when ''''s'''' then ''''SQL''''
when ''''g'''' then ''''GRP''''
end,
convert(varchar(100),sp.name) as srvLogin,
convert(varchar(100),sp2.name) as srvRole,
convert(varchar(100),dbp.name) as dbUser,
convert(varchar(100),dbp2.name) as dbRole
from
sys.server_principals as sp left join
sys.database_principals as dbp on sp.sid=dbp.sid left join
sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id left join
sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join
sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id
where case sp.type
when ''''u'''' then ''''WIN''''
when ''''s'''' then ''''SQL''''
when ''''g'''' then ''''GRP''''
end is not null '')
'
EXEC(@MYsql)
FETCH NEXT FROM serverlist_cur INTO @MYSERVER_NAME, @MYDB_NAME
END
CLOSE serverlist_cur
DEALLOCATE serverlist_cur
-------
--一致していないもののリスト
select * from
(
select
[Server名] TBAservername
,[DB名] TBAdbname
,[Login Type] [TBALogin Type]
,[srvLogin] [TBAsrvLogin]
,[srvRole] [TBAsrvRole]
,[dbUser] [TBAdbUser]
,[dbRole] [TBAdbRole]
FROM #user_itiran where [Server名] =@DATABASEA
)a
full outer join
(
select
[Server名] TBBservername
,[DB名] TBBdbname
,[Login Type] [TBBLogin Type]
,[srvLogin] [TBBsrvLogin]
,[srvRole] [TBBsrvRole]
,[dbUser] [TBBdbUser]
,[dbRole] [TBBdbRole]
FROM #user_itiran where [Server名] =@DATABASEB
) b
on isnull(a.TBAdbname ,'')=isnull(b.TBBdbname ,'')
and isnull(a.[TBALogin Type],'')=isnull(b.[TBBLogin Type] ,'')
and isnull(a.[TBAsrvLogin] ,'')=isnull(b.[TBBsrvLogin] ,'')
and isnull(a.[TBAsrvRole] ,'')=isnull(b.[TBBsrvRole] ,'')
and isnull(a.[TBAdbUser] ,'')=isnull(b.[TBBdbUser] ,'')
and isnull(a.[TBAdbRole] ,'')=isnull(b.[TBBdbRole] ,'')
where isnull(a.[TBALogin Type],'') <> isnull(b.[TBBLogin Type],'')
or isnull(a.[TBAsrvLogin] ,'') <> isnull(b.[TBBsrvLogin] ,'')
or isnull(a.[TBAsrvRole] ,'') <> isnull(b.[TBBsrvRole] ,'')
or isnull(a.[TBAdbUser] ,'') <> isnull(b.[TBBdbUser] ,'')
or isnull(a.[TBAdbRole] ,'') <> isnull(b.[TBBdbRole] ,'')
