SQL Server 数据库帐号密码生成

一、背景

在某天晚上凌晨1点，我收到领导的通知：需要把我们所有的数据库的密码都需要进行一次修改，原因是我们放到Web服务器的配置文件config中明文了我们的数据库帐号和密码，而且这份配置文件可能泄露了，更糟糕的是我们的数据库是可以通过外网进行访问的。虽然有端口进行映射，但是我们的数据依然处在危险的状态，所以这个时候DBA需要争分夺秒修改数据库的帐号密码。

我们的数据库服务器大概有30台，而且每台机器上跑了很多个数据库。有创建数据库帐号经验的同学你会发现：使用SSMS进行创建帐号密码是件多么痛苦的事情，需要点击很多checkbox，特别是在创建一个可以访问整个数据库实例里的所有数据库的时候（虽然我们提倡每个数据库的帐号和密码都不一样，更严格的要求可能需要设置同一个数据库下需要有不同权限的帐号进行管理）

如果我们对数据库的帐号进行有效管理的话，我们可以很轻松的完成这个任务，只需要一条SQL就能管理了。在这里我提倡一种安全、易维护的方案给大家：SQL Server数据库帐号密码安全设计

在看这篇文章之前，建议你先看看：SQL Server 2005控制用户权限访问表 

 

二、设计概要

我们首先要明白我们出现的问题是什么：

1.      我们厌烦了一个个去点击checkbox；

2.      点击那么多次checkbox，我们不能确保所有的点击都是正确的；

所以我们第一步要想的就是有没什么办法可以解决上面这些问题？上面的这些操作SSMS是能让用户把操作保存为脚本的，我们可以自己编写SQL脚本来完成类似的工作。为了模拟SSMS上的操作，我们需要获取到数据库实例中所包含的所有数据库，再对每个数据库创建帐号和密码。我们可以通过游标的形式循环数据库，并创建帐号和密码。最后我们需要禁用掉sa这个帐号。

修改下面【通用SQL模板】的@user和@password的值，拷贝到下面的代码到SSMS中执行，这样就可以生成出适合本数据库实例的SQL脚本【生成的脚本代码】了，我们再拷贝生成的SQL代码到SSMS中执行就可以了。

执行下面的两个脚本不过秒级，所以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.      这里再次推荐大家使用同一的数据库帐号密码的管理，无论是安全还是方便维护都是大有好处的。