DBユーザの比較

DB移行などで、新・旧のデータベースサーバのユーザを比較する時に利用します。

サーバロールの付与漏れの確認に利用ください。

 

下記の個所を比較するDBサーバ名に書き換えてください。

SET @DATABASEA = ‘DBサーバ1’
SET @DATABASEB = ‘DBサーバ2’

 

サンプルでは、OPENROWSETにWindows認証を利用していますので、ダブルホップ接続にならない様に気を付けてください。

不明な場合は、DBサーバ1かDBサーバ2にログインして、ログインしたサーバに対して実行してください。

 

また、Ad Hoc Distributed Queriesを有効にする必要があります。

 

-- サーバロール比較 全DB

DECLARE @DATABASEA varchar(50)
DECLARE @DATABASEB varchar(50)

-----
--比較するサーバを指定します
-----
SET @DATABASEA = 'DBサーバ1'
SET @DATABASEB = 'DBサーバ2'

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]    ,'')

 


管理人 has written 30 articles