SQL Serverデータベースアカウントパスワード生成

一、背景
ある夜の午前1時、私はリーダーの通知を受け取った:私たちのすべてのデータベースのパスワードを変更する必要があります.なぜなら、私たちはWebサーバのプロファイルconfigに私たちのデータベースのアカウントとパスワードを明記しているためです.そして、このプロファイルが漏れている可能性があります.さらに悪いことに、私たちのデータベースは外部ネットワークを通じてアクセスすることができます.ポートがマッピングされていますが、私たちのデータは依然として危険な状態にあるので、このときDBAはデータベースのアカウントパスワードを一刻を争って変更する必要があります.
私たちのデータベースサーバは約30台あり、各マシンには多くのデータベースが走っています.データベースアカウントを作成した経験のある人は、SSMSを使ってアカウントパスワードを作成するのはどんなにつらいことか、checkboxをたくさんクリックする必要があります.特に、データベース・インスタンス全体にアクセスできるすべてのデータベースを作成する場合(データベースごとにアカウントとパスワードが異なることを提唱していますが、同じデータベースの下で異なる権限を持つアカウントを設定して管理する必要がある場合があります)
もし私たちがデータベースのアカウントを有効に管理すれば、私たちは簡単にこの任務を完成することができて、1本のSQLだけで管理することができます.ここで私は安全でメンテナンスしやすい案を提唱します:SQL Serverデータベースアカウントパスワードセキュリティ設計
この文章を読む前に、SQL Server 2005制御ユーザー権限アクセステーブルを見ておくことをお勧めします.
 
二、設計概要
まず、私たちが直面している問題が何なのかを理解しなければなりません.
1.私たちはcheckboxをクリックするのにうんざりしています.
2.何度もcheckboxをクリックすると、すべてのクリックが正しいことを確保できません.
だから私たちが最初に考えなければならないのは、上記の問題を解決する方法がありますか?これらのSSMSは、ユーザーが操作をスクリプトとして保存できるようにしており、SQLスクリプトを自分で作成して類似の作業を完了することができます.SSMS上の操作をシミュレートするには、データベース・インスタンスに含まれるすべてのデータベースを取得し、各データベースにアカウントとパスワードを作成する必要があります.カーソル形式でデータベースをループし、アカウントとパスワードを作成できます.最後にsaというアカウントを無効にする必要があります.
以下の【汎用SQLテンプレート】の@userと@passwordの値を修正し、以下のコードをSSMSにコピーして実行することで、本データベースのインスタンスに適したSQLスクリプト【生成されたスクリプトコード】を生成することができ、生成されたSQLコードをSSMSにコピーして実行することができます.
次の2つのスクリプトを実行するのは秒レベルにすぎないので、20台のサーバはあなたにとって簡単です.
 
三、汎用SQLテンプレート

--       
DECLARE @dbname varchar(100)
DECLARE @user varchar(100)
DECLARE @password varchar(100)
DECLARE @sql varchar(max)
SET @user = 'UfranimdA_gz'
SET @password = 'o23#25R@8a8A!@23#@%'

SET @sql = '
USE [master]
GO
CREATE LOGIN ['+ @user + '] WITH PASSWORD=N'''+ @password +''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N'''+@user+''', @rolename = N''sysadmin''
GO'
PRINT(@sql)

DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR 
    SELECT name from sys.databases where state =0

OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @dbname
WHILE @@FETCH_STATUS=0
BEGIN

    SET @sql = '
USE ['+ @dbname + ']
GO
CREATE USER ['+@user+'] FOR LOGIN ['+@user+']
GO
USE ['+ @dbname + ']
GO
ALTER USER ['+@user+'] WITH DEFAULT_SCHEMA=[dbo]
GO
USE ['+ @dbname + ']
GO
EXEC sp_addrolemember N''db_owner'', N'''+@user+'''
GO'
    PRINT(@sql)

    FETCH NEXT FROM @itemCur INTO @dbname
END 

CLOSE @itemCur
DEALLOCATE @itemCur

--  sa  
SET @sql = '
USE [master]
GO
ALTER LOGIN [sa] DISABLE
GO'
PRINT(@sql)

 
四、生成したスクリプトコード

--         
USE [master]
GO
CREATE LOGIN [UfranimdA_gz] WITH PASSWORD=N'o23#25R@8a8A!@23#@%', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N'UfranimdA_gz', @rolename = N'sysadmin'
GO

USE [master]
GO
CREATE USER [UfranimdA_gz] FOR LOGIN [UfranimdA_gz]
GO
USE [master]
GO
ALTER USER [UfranimdA_gz] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [master]
GO
EXEC sp_addrolemember N'db_owner', N'UfranimdA_gz'
GO

USE [tempdb]
GO
CREATE USER [UfranimdA_gz] FOR LOGIN [UfranimdA_gz]
GO
USE [tempdb]
GO
ALTER USER [UfranimdA_gz] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [tempdb]
GO
EXEC sp_addrolemember N'db_owner', N'UfranimdA_gz'
GO

USE [model]
GO
CREATE USER [UfranimdA_gz] FOR LOGIN [UfranimdA_gz]
GO
USE [model]
GO
ALTER USER [UfranimdA_gz] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [model]
GO
EXEC sp_addrolemember N'db_owner', N'UfranimdA_gz'
GO

USE [msdb]
GO
CREATE USER [UfranimdA_gz] FOR LOGIN [UfranimdA_gz]
GO
USE [msdb]
GO
ALTER USER [UfranimdA_gz] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'db_owner', N'UfranimdA_gz'
GO

USE [DBA_DB]
GO
CREATE USER [UfranimdA_gz] FOR LOGIN [UfranimdA_gz]
GO
USE [DBA_DB]
GO
ALTER USER [UfranimdA_gz] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [DBA_DB]
GO
EXEC sp_addrolemember N'db_owner', N'UfranimdA_gz'
GO

USE [TestDB]
GO
CREATE USER [UfranimdA_gz] FOR LOGIN [UfranimdA_gz]
GO
USE [TestDB]
GO
ALTER USER [UfranimdA_gz] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [TestDB]
GO
EXEC sp_addrolemember N'db_owner', N'UfranimdA_gz'
GO

USE [master]
GO
ALTER LOGIN [sa] DISABLE
GO

 
五、特別説明
1.ここで生成されたSQLスクリプトには、システムデータベース:master、model、msdb、tempdbが含まれています.便宜上、これらのデータベースを制限していないので、後でこのスクリプトを修正してほしいです.
2.ここでは、同じデータベースアカウントパスワードの管理を再度お勧めします.安全であれ、メンテナンスが容易であれ、大きなメリットがあります.SQL Serverデータベースアカウントパスワードセキュリティ設計、他の考えを持ってもいいです.