| MSDE FunClub |
|
最終更新日 : 2000/08/22 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.22
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（下巻） |
|
【第１２章１３２ｐ 〜 １３４ｐ掲載】 |
-- [SQL12_03.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- ユーザ定義のデータベースロールの作成
-- ユーザ定義のデータベースロールの削除
-- を理解します
--
-- カレントデータベースを MySampleTest にする
USE MySampleTest
GO
-- (件処理されました)のメッセージを抑止する
SET NOCOUNT ON
GO
-- *************************************************
-- 私は、db_owner または db_securityadmin ですか？
-- *************************************************
IF( IS_MEMBER('db_owner') <> 1
AND
IS_MEMBER('db_securityadmin') <> 1
)
BEGIN
RAISERROR('私にはロールメンバ操作の権限がありません',
16 , 127 )
RETURN
END
GO
-- ******************************************************************
-- データベースにロールDRIDenyが存在したら、所属メンバを全部削除して
-- ロールを削除します
-- メンバを削除するときは、カーソルを使うと便利です
-- ******************************************************************
DECLARE @UID SMALLINT
DECLARE @MEMID SMALLINT
DECLARE @usernm varchar(20)
--ロールがありますか？ロールのIDを取得
SELECT @UID = uid FROM SYSUSERS WHERE (issqlrole = 1 AND name = 'DRIDeny' )
IF ( @UID IS NOT NULL )
BEGIN
-- データベースにロールが存在しました
-- 所属メンバに対して、カーソルを設定します
DECLARE MEM CURSOR
FOR SELECT MEMBERUID FROM SYSMEMBERS WHERE( GROUPUID = @UID)
-- カーソルを開く
OPEN MEM
-- 先頭レコードの取得に成功ですか？
FETCH NEXT FROM MEM INTO @MEMID
-- @@FETCH_STATUS外部変数が=0の間は、レコード取得に成功
WHILE( @@FETCH_STATUS = 0 )
BEGIN
-- 今取得したロールのメンバを削除します
-- PRINT USER_NAME(@MEMID)
SELECT @usernm = USER_NAME(@MEMID)
EXEC sp_droprolemember 'DRIDeny', @usernm
-- 次のレコードを取得する
FETCH NEXT FROM MEM INTO @MEMID
END
-- カーソルを閉じる
CLOSE MEM
-- 参照関係を解除する
DEALLOCATE MEM
-- ユーザ定義のロールを削除します
EXEC sp_droprole 'DRIDeny'
END
GO
-- **************************************************
-- データベースロール DRIDeny を作成しメンバを登録
-- **************************************************
EXEC sp_addrole 'DRIDeny' -- ロール作成
EXEC sp_addrolemember 'DRIDeny' , 'user01' -- メンバ登録
EXEC sp_addrolemember 'DRIDeny' , 'user02'
GO
-- **************************************************
-- データベースロール DRIDeny にセキュリティを設定する
-- 外部参照を拒否する
-- **************************************************
DENY REFERENCES( ID ) ON DRIGrantCheck to DRIDeny
GO
PRINT 'DRIDenyロールの設定が終了しました'
GO
