テーブル権限の比較

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