| MSDE FunClub |
|
最終更新日 : 2000/08/22 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.22
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（下巻） |
|
【第１２章１５０ｐ 〜 １５４ｐ掲載】 |
-- [SQL12_09.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- 【データベースのユーザを登録する】
--
-- [注意]
-- システム管理者の方に、このSQL文を実行して頂きましょう
-- Pubs と Northwind が存在しないときは、エラーが起こります
-- SELECT文などの実行によって、( 件処理されました)の表示抑制
SET NOCOUNT ON
GO
-- ***************************************************
-- 現在実行している人のログインセキュリティのチェック
-- ***************************************************
DECLARE @F_SYSADMIN int
-- サーバーロールsysadminに、あなたは所属していますか？
SELECT @F_SYSADMIN = IS_SRVROLEMEMBER( 'sysadmin' )
IF( @F_SYSADMIN=0 )
BEGIN
-- 127番にすると、osqlやisqlではスクリプト強制終了です
RAISERROR( '残念ですがユーザ登録権限がありません',16,127)
RETURN
END
GO
-- ***************************************************
-- 【データベースの存在確認】
-- Pubs と Northwind の確認
-- ***************************************************
IF NOT EXISTS( SELECT name FROM master..sysdatabases
WHERE( name = 'Pubs' ) )
RAISERROR('Pubs データベースが存在しません。適当に作成してください',0,1)
IF NOT EXISTS( SELECT name FROM master..sysdatabases
WHERE( name = 'Northwind' ) )
RAISERROR('Northwindデータベースが存在しません。適当に作成してください',0,1)
GO
-- ***********************************************************
-- ユーザ登録を行なうテーブルの定義とレコードの登録
-- ***********************************************************
CREATE TABLE #USER_LIST (
dbname sysname , --ユーザ登録を行うデータベース名
lognm sysname , --ログイン名
usernm sysname NULL DEFAULT NULL --ログイン名に対応したユーザ名
)
CREATE TABLE #USER_DBRL (
dbname sysname , --データベース
usernm sysname , --ロールを設定するユーザ名
rolenm sysname --許可するロール名
)
--ユーザ登録リストの定義
INSERT INTO #USER_LIST VALUES ( 'MySampleTest' , 'Guest01' , NULL )
INSERT INTO #USER_LIST VALUES ( 'Pubs' , 'Guest01' , NULL )
INSERT INTO #USER_LIST VALUES ( 'Northwind' , 'Guest01' , NULL )
INSERT INTO #USER_LIST VALUES ( 'MySampleTest' , 'Guest02' , NULL )
INSERT INTO #USER_LIST VALUES ( 'Pubs' , 'Guest02' , NULL )
INSERT INTO #USER_LIST VALUES ( 'Northwind' , 'Guest02' , NULL )
INSERT INTO #USER_LIST VALUES ( 'MySampleTest' , 'JTSD3\nm01' , NULL )
INSERT INTO #USER_LIST VALUES ( 'Pubs' , 'JTSD3\nm01' , NULL )
INSERT INTO #USER_LIST VALUES ( 'Northwind' , 'JTSD3\nm01' , NULL )
INSERT INTO #USER_LIST VALUES ( 'MySampleTest' , 'JTSD3\nm02' , 'usr02' )
INSERT INTO #USER_LIST VALUES ( 'Pubs' , 'JTSD3\nm02' , 'usr02' )
INSERT INTO #USER_LIST VALUES ( 'Northwind' , 'JTSD3\nm02' , 'usr02' )
--*************************
--データベースロールの定義
--*************************
INSERT INTO #USER_DBRL VALUES ( 'MySampleTest' , 'Guest01' , 'db_owner' )
INSERT INTO #USER_DBRL VALUES ( 'Pubs' , 'Guest01' , 'db_accessadmin' )
INSERT INTO #USER_DBRL VALUES ( 'Pubs' , 'Guest01' , 'db_ddladmin' )
INSERT INTO #USER_DBRL VALUES ( 'Pubs' , 'Guest01' , 'db_securityadmin' )
INSERT INTO #USER_DBRL VALUES ( 'Northwind' , 'Guest01' , 'db_owner' )
INSERT INTO #USER_DBRL VALUES ( 'MySampleTest' , 'usr02' , 'db_owner' )
INSERT INTO #USER_DBRL VALUES ( 'Pubs' , 'usr02' , 'db_accessadmin' )
INSERT INTO #USER_DBRL VALUES ( 'Pubs' , 'usr02' , 'db_ddladmin' )
INSERT INTO #USER_DBRL VALUES ( 'Pubs' , 'usr02' , 'db_securityadmin' )
INSERT INTO #USER_DBRL VALUES ( 'Northwind' , 'usr02' , 'db_owner' )
GO
-- ***********************************
-- データベースユーザの一括登録処理
-- ***********************************
DECLARE @dbname sysname --ユーザ登録を行うデータベース名
DECLARE @lognm sysname -- ログイン名
DECLARE @usernm sysname --ログイン名に対応したユーザ名
DECLARE @cmd varchar(256)
--カーソルを定義する
DECLARE User_CUR INSENSITIVE CURSOR
FOR SELECT * FROM #USER_LIST
FOR READ ONLY
-- カーソルを開く
OPEN User_CUR
-- 先頭行の読み込み
FETCH NEXT FROM User_CUR INTO @dbname , @lognm , @usernm
WHILE( @@FETCH_STATUS = 0 )
BEGIN
-- ユーザ登録用コマンド文字列の作成
SELECT @cmd = 'USE ' + @dbname + ' ' +
'EXEC sp_grantdbaccess ''' + @lognm + ''' , ' +
CASE
WHEN @usernm is null THEN 'NULL'
ELSE '''' + @usernm + ''''
END
--PRINT @cmd
--１件分のユーザを登録します
EXEC( @cmd )
-- 次行の読み込み
FETCH NEXT FROM User_CUR INTO @dbname , @lognm , @usernm
END
-- カーソルを閉じる
CLOSE User_CUR
-- カーソルを破棄する
DEALLOCATE User_CUR
-- 一時テーブルを破棄する
DROP TABLE #USER_LIST
GO
-- ***********************************
-- データベースロールの設定を行なう
-- ***********************************
DECLARE @dbname sysname --ユーザ登録を行うデータベース名
DECLARE @usernm sysname --ユーザ名
DECLARE @role sysname --登録を行うデータベースロール名
DECLARE @cmd varchar(256)
--カーソルを定義する
DECLARE User_CUR INSENSITIVE CURSOR
FOR SELECT * FROM #USER_DBRL
FOR READ ONLY
-- カーソルを開く
OPEN User_CUR
-- 先頭行の読み込み
FETCH NEXT FROM User_CUR INTO @dbname , @usernm , @role
WHILE( @@FETCH_STATUS = 0 )
BEGIN
-- ロール登録用コマンド文字列の作成
SELECT @cmd = 'USE ' + @dbname + ' ' +
'EXEC sp_addrolemember ''' + @role + ''' , ' +
'''' + @usernm + ''''
--PRINT @cmd
--１件分のロールを登録します
EXEC( @cmd )
-- 次行の読み込み
FETCH NEXT FROM User_CUR INTO @dbname , @usernm , @role
END
-- カーソルを閉じる
CLOSE User_CUR
-- カーソルを破棄する
DEALLOCATE User_CUR
-- 一時テーブルを破棄する
DROP TABLE #USER_DBRL
GO
