一、背景
在某天晚上凌晨1點，我收到領導的通知：需要把我們所有的數(shù)據(jù)庫的密碼都需要進行一次修改，原因是我們放到Web服務器的配置文件config中明文了我們的數(shù)據(jù)庫帳號和密碼，而且這份配置文件可能泄露了，更糟糕的是我們的數(shù)據(jù)庫是可以通過外網(wǎng)進行訪問的。雖然有端口進行映射，但是我們的數(shù)據(jù)依然處在危險的狀態(tài)，所以這個時候DBA需要爭分奪秒修改數(shù)據(jù)庫的帳號密碼。
我們的數(shù)據(jù)庫服務器大概有30臺，而且每臺機器上跑了很多個數(shù)據(jù)庫。有創(chuàng)建數(shù)據(jù)庫帳號經(jīng)驗的同學你會發(fā)現(xiàn)：使用SSMS進行創(chuàng)建帳號密碼是件多么痛苦的事情，需要點擊很多checkbox，特別是在創(chuàng)建一個可以訪問整個數(shù)據(jù)庫實例里的所有數(shù)據(jù)庫的時候（雖然我們提倡每個數(shù)據(jù)庫的帳號和密碼都不一樣，更嚴格的要求可能需要設置同一個數(shù)據(jù)庫下需要有不同權限的帳號進行管理）
如果我們對數(shù)據(jù)庫的帳號進行有效管理的話，我們可以很輕松的完成這個任務，只需要一條SQL就能管理了。在這里我提倡一種安全、易維護的方案給大家：SQL Server數(shù)據(jù)庫帳號密碼安全設計
二、設計概要
我們首先要明白我們出現(xiàn)的問題是什么：
1. 我們厭煩了一個個去點擊checkbox；
2. 點擊那么多次checkbox，我們不能確保所有的點擊都是正確的；
所以我們第一步要想的就是有沒什么辦法可以解決上面這些問題？上面的這些操作SSMS是能讓用戶把操作保存為腳本的，我們可以自己編寫SQL腳本來完成類似的工作。為了模擬SSMS上的操作，我們需要獲取到數(shù)據(jù)庫實例中所包含的所有數(shù)據(jù)庫，再對每個數(shù)據(jù)庫創(chuàng)建帳號和密碼。我們可以通過游標的形式循環(huán)數(shù)據(jù)庫，并創(chuàng)建帳號和密碼。最后我們需要禁用掉sa這個帳號。
修改下面【通用SQL模板】的@user和@password的值，拷貝到下面的代碼到SSMS中執(zhí)行，這樣就可以生成出適合本數(shù)據(jù)庫實例的SQL腳本【生成的腳本代碼】了，我們再拷貝生成的SQL代碼到SSMS中執(zhí)行就可以了。
執(zhí)行下面的兩個腳本不過秒級，所以20臺服務器對你來說，簡單啦。
三、通用SQL模板
--創(chuàng)建數(shù)據(jù)庫帳號
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)
四、生成的腳本代碼
--創(chuàng)建數(shù)據(jù)庫帳號密碼
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腳本中包含了系統(tǒng)數(shù)據(jù)庫：master、model、msdb、tempdb，為了方便我就沒對這些數(shù)據(jù)庫進行限制的，希望以后可以修正下這個腳本。
2. 這里再次推薦大家使用同一的數(shù)據(jù)庫帳號密碼的管理，無論是安全還是方便維護都是大有好處的。SQL Server數(shù)據(jù)庫帳號密碼安全設計，大家可以提些其它的想法。
喜歡 
頂
難過
囧
圍觀
無聊
