テーブル権限の比較

RG 機動戦士ガンダム RX-78-2 ガンダム Ver.2.0 1/144スケール 色分け済みプラモデル

(587)

￥4,999 (2025年4月30日 00:08 GMT +09:00 時点 - 詳細はこちら価格および発送可能時期は表示された日付/時刻の時点のものであり、変更される場合があります。本商品の購入においては、購入の時点で当該の Amazon サイトに表示されている価格および発送可能時期の情報が適用されます。)

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

権限の付与漏れの確認に利用ください。

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

SET @DATABASEA = ‘DBサーバ１’
SET @DATABASEB = ‘DBサーバ２’

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

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

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

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 where dbid>= 5'')
'

exec(@MYsql)

  -- データを取得
FETCH NEXT FROM serverlist_cur INTO @MYSERVER_NAME
END

CLOSE serverlist_cur
DEALLOCATE serverlist_cur

declare @sql as varchar(8000)
declare @DBAServer as varchar(100)
declare @DBBServer as varchar(100)
declare @DBADB as varchar(100)
declare @DBBDB as varchar(100)

DECLARE table_cs CURSOR FOR
select 
[DBAServer],[DBADB],[DBBServer],[DBBDB] FROM 
(
SELECT SERVER_NAME [DBAServer],DB_NAME [DBADB]
FROM #dbname where SERVER_NAME= @DATABASEA
) a
full outer join 
(
SELECT SERVER_NAME [DBBServer],DB_NAME [DBBDB]
FROM #dbname where SERVER_NAME= @DATABASEB
) b
on a.[DBADB]=b.[DBBDB]
 ORDER BY 1,2

OPEN table_cs

FETCH NEXT FROM table_cs INTO @DBAServer, @DBADB, @DBBServer, @DBBDB
WHILE @@FETCH_STATUS = 0
BEGIN


select @sql ='
select 
 ''' + @DBAServer +''' Server
,''' + @DBADB +''' DB
,DBA.principal_name
,DBA.principal_type_desc
,DBA.object_name
,DBA.permission_name
,DBA.permission_state_desc
,CASE WHEN
    ISNULL(DBA.principal_name       ,'''')  = ISNULL(DBB.principal_name        ,'''')
and ISNULL(DBA.principal_type_desc  ,'''')  = ISNULL(DBB.principal_type_desc   ,'''')
and ISNULL(DBA.object_name          ,'''')  = ISNULL(DBB.object_name           ,'''')
and ISNULL(DBA.permission_name      ,'''')  = ISNULL(DBB.permission_name       ,'''')
and ISNULL(DBA.permission_state_desc,'''')  = ISNULL(DBB.permission_state_desc ,'''')
then ''○''
else ''×''
END ''○/×''

,DBB.principal_name
,DBB.principal_type_desc
,DBB.object_name
,DBB.permission_name
,DBB.permission_state_desc

FROM OPENROWSET(''SQLNCLI'',''Server=' + @DBAServer +';Trusted_Connection=yes;'', 
''
select  dp.NAME AS principal_name,
        dp.type_desc AS principal_type_desc,
        o.NAME AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
from    
 ' + @DBADB + '.sys.database_permissions p
left    OUTER JOIN 
 ' + @DBADB + '.sys.all_objects o
on     p.major_id = o.OBJECT_ID
inner   JOIN 
 ' + @DBADB + '.sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id
''
) DBA

FULL OUTER JOIN
 OPENROWSET(''SQLNCLI'',''Server=' + @DBBServer +';Trusted_Connection=yes;'', 
''
select dp.NAME AS principal_name,
        dp.type_desc AS principal_type_desc,
        o.NAME AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
from    
 ' + @DBBDB + '.sys.database_permissions p
left    OUTER JOIN 
 ' + @DBBDB + '.sys.all_objects o
on     p.major_id = o.OBJECT_ID
inner   JOIN 
 ' + @DBBDB + '.sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id
''
) DBB

ON isnull(DBA.principal_name,'''') = isnull(DBB.principal_name,'''')
and isnull(DBA.principal_type_desc,'''')  =  isnull(DBB.principal_type_desc ,'''')
and isnull(DBA.object_name,'''') = isnull(DBB.object_name,'''')
and isnull(DBA.permission_name,'''') = isnull(DBB.permission_name,'''')

where 

CASE WHEN
    ISNULL(DBA.principal_name       ,'''')  = ISNULL(DBB.principal_name        ,'''')
and ISNULL(DBA.principal_type_desc  ,'''')  = ISNULL(DBB.principal_type_desc   ,'''')
and ISNULL(DBA.object_name          ,'''')  = ISNULL(DBB.object_name           ,'''')
and ISNULL(DBA.permission_name      ,'''')  = ISNULL(DBB.permission_name       ,'''')
and ISNULL(DBA.permission_state_desc,'''')  = ISNULL(DBB.permission_state_desc ,'''')
then ''○''
else ''×''
END = ''×''

'


exec(@sql)

  -- データを取得
FETCH NEXT FROM table_cs INTO @DBAServer, @DBADB, @DBBServer, @DBBDB
END

CLOSE table_cs
DEALLOCATE table_cs

Tweet