> 数据库 > SQL Server 2019 >

SQL Server 登陆账户/包含用户密码批量修改

时间:2023-03-03 10:14  来源:linux.it.net.cn   作者:IT

SQL Server 数据库管理中，账户安全是数据库安全的一道重要屏障。除对密码复杂度有严格要求外，周期性修改账户密码，是保证数据库安全的一个重要举措。如下给出修改登陆账户/包含用户密码的脚本：

--更换登陆帐户密码
USE master
GO
ALTER LOGIN Jack WITH PASSWORD='I8@^mV7(Tyz0Jov',CHECK_POLICY=ON,CHECK_EXPIRATION=OFF
--修改包含用户密码
USE test
GO
ALTER USER Jack WITH PASSWORD='I8@^mV7(Tyz0Jov'
但对于生产数据库，这样直接修改原账户的密码是存在问题的。因为我们不能做到数据库和应用同时修改密码，这样是会影响业务的正常运行，而导致不必要的损失。而较为合理的做法是创建新账户，赋予原账户相同的权限，替换原账户。下面将使用《SQL Server 登陆账户权限克隆——sp_DBA_LoginClone 升级版》、《克隆SQL Server 用户及权限》两篇文章中叙述的SQL Server登陆账户、SQL Server 数据库用户克隆的方式给出新账户的创建及权限赋予脚本。

为更方便的给创建新账户提供随机密码，这里首先给出生成随机密码的脚本，如下：

--创建字符集表，并插入生成密码的字符
USE master
CREATE TABLE AsciiCode(id INT IDENTITY(1,1),characters CHAR(1))
DECLARE @i INT=33
WHILE @i<127
BEGIN
      IF @i NOT IN(34，39) --密码中不能有单引号和双引号
            INSERT INTO AsciiCode(characters)
            SELECT CHAR(@i)
      SET @i=@i+1
END
GO
--创建生成随机密码的存储过程
USE master
GO
CREATE PROC pwdGenerate(
      @num smallint --密码长度
      ,@pwd VARCHAR(50) OUTPUT
      )
AS
BEGIN
      DECLARE @i SMALLINT=1
      SET @pwd=''
      WHILE @i<=@num
      BEGIN
            SELECT TOP 1 @pwd=@pwd+characters
            FROM master.dbo.AsciiCode
            --WHERE id=RAND()*94
            ORDER BY NEWID()
            SET @i=@i+1
      END
      --RETURN @pwd
END
GO
替换账户信息
接下来确定需要替换的账户、新账户，并使用上面的脚本生成密码，如下：

USE master
GO
--创建新旧帐户密码对应表
CREATE TABLE login_create
    (
    id INT IDENTITY(1,1)
    ,newLoginName  sysname NULL
    ,loginName sysname NULL
    ,pwd VARCHAR(50)
    ,dbName sysname NULL
    ,userName sysname NULL
    ,newUserName sysname NULL
    )
----插入需要替换的账户(一般针对生产环境使用的帐户、用户)、新帐户、密码(密码可以由存储过程pwdGenerate生成)
INSERT INTO login_create(loginName)
SELECT  name
FROM    sys.sql_logins
WHERE   name NOT IN ( 'NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT','##MS_PolicyTsqlExecutionLogin##','##MS_PolicyEventProcessingLogin##' )
      AND is_disabled=0
--select * from login_create
--TRUNCATE TABLE login_create
UPDATE login_create SET newLoginName=loginName+'_V1'
--生成新帐户的密码
DECLARE @id INT=1,@maxNum INT
DECLARE @pwd VARCHAR(50)
SELECT
      @maxNum=MAX(id)
from login_create
WHILE @id<=@maxNum
BEGIN
      EXEC pwdGenerate 15,@pwd OUTPUT
      UPDATE login_create SET pwd=@pwd
      WHERE id=@id
      SET @id=@id+1
END
GO
注意：login_create中，对于登陆账户，DBName、userName、newUserName字段为NULL；包含用户newLoginName、loginName为空。

克隆账户用户
使用前文中的过程sp_DBA_LoginPermissionsClone、sp_DBA_userPermisionsClone，生成新账户的创建脚本，及权限赋予脚本。注意执行下面脚本时需要先在master库下创建sp_DBA_LoginPermissionsClone、sp_DBA_userPermisionsClone两过程，并标记为系统过程。

USE master
GO
--生成新帐户的创建脚本，并生成克隆原帐户权限的赋予脚本
SET nocount ON
DECLARE @newLoginName SYSNAME
      ,@loginName SYSNAME
      ,@pwd CHAR(15)
      ,@userName sysname
      ,@dbName sysname
      ,@newUserName sysname
      ,@sql NVARCHAR(max)
DECLARE logins CURSOR
FOR
SELECT  newLoginName,loginName,pwd,dbName,userName,newUserName
FROM    login_create
OPEN logins
FETCH NEXT FROM logins INTO @newLoginName, @loginName, @pwd,@dbName,@userName,@newUserName
--循环登陆账户
WHILE @@FETCH_STATUS = 0
BEGIN
       if @loginName is not null
       begin
      EXEC dbo.sp_DBA_LoginPermissionsClone @loginName,@newLoginName,@pwd,@sql OUTPUT
      PRINT @sql
         set @userName=@loginName
       end
      --循环数据库，生成创建数据用户脚本及赋予用户权限脚本
    DECLARE @databaseName SYSNAME
    DECLARE databaseNames CURSOR
    FOR
    SELECT  name
    FROM    sys.sysdatabases
    OPEN databaseNames
    FETCH NEXT FROM databaseNames INTO @databaseName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = N'select @i=1 from ' + QUOTENAME(@databaseName,'[') + N'.sys.sysusers
                    where name=''' + @userName + ''''
        DECLARE @j INT= NULL
        EXEC sp_executesql @sql, N'@i int output', @i = @j OUTPUT
        IF ( @j IS NULL )     --数据库不存在用户，回到开始进行下次循环
        BEGIN
            FETCH NEXT FROM databaseNames INTO @databaseName
            CONTINUE
        END
        IF @userName IS NULL
                SET @userName=@loginName
        IF @newUserName IS NULL
                SET @newUserName=@newLoginName
        IF ( @j = 1 )
        BEGIN
                     if (@dbName is not null and @dbName=@databaseName) or @dbName is null
                  EXEC dbo.sp_DBA_userPermisionsClone @databaseName,@userName,@newUserName,@newLoginName,@pwd
        END
        FETCH NEXT FROM databaseNames INTO @databaseName
    END
    CLOSE databaseNames
    DEALLOCATE databaseNames
    FETCH NEXT FROM logins INTO @newLoginName, @loginName, @pwd,@dbName,@userName,@newUserName      
END
CLOSE logins
DEALLOCATE logins
注意：对于包含用户，如果存在涉及到跨库的包含用户，需要单独创建，具体参加《SQL Server 部分包含数据库》。

生成完脚本后，及时备份创建脚本或者执行生成的脚本。将新、旧账户对应关系及密码导出到本机加密文件中，更新密码字段为NULL。同时检查附近时点是否有对该库进行备份的作业，确保新账户密码没有备份到数据库备份文件中。

UPDATE login_create SET pwd=NULL
监控账户替换情况

新账户创建完成后，需要将旧、新账户及密码发相关人员替换（开发或者IT），开启就账户连接监控。我们可以执行使用查询连接的脚本，查看哪些旧账户还没有被替换掉：

--查询指定连接数据库账户的IP
SELECT distinct client_net_address,loginame,hostname,program_name
FROM sys.dm_exec_connections conn
join master..sysprocesses procs
on conn.session_id=procs.spid
where loginame IN(SELECT loginName from login_create)
但对于哪些非频繁连接的账户，使用上面的查询并不一定能捕获到，最好的方式是使用触发器来监控：

--创建登陆信息日志表
CREATE TABLE [dbo].[Login_Monitor](
      [Spid] [int] NULL,
      [Posttime] [datetime] NULL,
      [Servername] [varchar](30) NULL,
      [Hostname] [varchar](30) NULL,
      [Loginame] [varchar](30) NULL,
      [Logintype] [varchar](20) NULL,
      [ClientHost] [varchar](20) NULL,
      [Stat] [int] NULL
)
use master
GO
--==================================
-- 服务器登监控账户信息
--==================================
CREATE TRIGGER [LoginMonitor]
ON all server WITH EXECUTE AS 'sa'
FOR LOGON 
AS
BEGIN
      DECLARE @Data XML 
      DECLARE @PostTime NVARCHAR(24) 
      DECLARE @Spid NVARCHAR(6) 
      DECLARE @LoginName NVARCHAR(100) 
      DECLARE @HostName NVARCHAR(100)
      DECLARE @ServerName NVARCHAR(20) 
      DECLARE @LoginType NVARCHAR(20) 
      DECLARE @ClientHost NVARCHAR(20)
      DECLARE @IP NVARCHAR(20)
      DECLARE @STAT INT = 0
      DECLARE  @IPint BIGINT=0
     
      SET @Data =             EVENTDATA()
      SET @HostName =         HOST_NAME()
      SET @PostTime =         @Data.value('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)') 
      SET @Spid =             @Data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)') 
      SET @LoginName =      @Data.value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(100)')
      SET @ServerName =      @Data.value('(/EVENT_INSTANCE/ServerName)[1]','NVARCHAR(100)')
      SET @ClientHost =      @Data.value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(100)')
      SET @LoginType =  @Data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(100)')
      SET @IP = @ClientHost
     
      IF @ClientHost<>'<local machine>'
      begin
            IF @LoginName in( SELECT loginName from login_create)
            BEGIN
                  INSERT INTO [Login_Monitor]
                  VALUES(@SPID, @posttime,@ServerName,@hostname, @loginname,@LoginType,@IP,@STAT)
            END
      END
END
经过一定周期的监控，确定所有账户已经替换后，接下来可以禁用原账户了。



禁用原账户

--生成禁用登陆帐户脚本
SELECT 'ALTER LOGIN '+QUOTENAME(loginName,']') +'DISABLE'
FROM login_create
生成脚本后直接执行即可。

清理禁用的旧账户

通过观察，没有账户有报错后，就可以清理旧的停用账户了。因为删除登陆账户时，没有办法直接关联删除其对应在各数据库中的用户，下面使用循环将停用旧登陆账户及对应各数据库中的用户删除，脚本如下：

--删除停用的帐户，及各数据库中对应的用户
DECLARE @user AS VARCHAR(200)
DECLARE @databaseName AS VARCHAR(200)
DECLARE @sql AS VARCHAR(800)
--循环删除停用的用户
DECLARE curUsers CURSOR
FOR
SELECT  QUOTENAME(loginName, ']')
FROM    login_create
     
OPEN curUsers
FETCH NEXT FROM curUsers INTO @user
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE databaseNameCur CURSOR
    FOR
    SELECT  name
    FROM    sys.databases
    WHERE   state = 0
    OPEN databaseNameCur
    FETCH NEXT FROM databaseNameCur INTO @databaseName
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql = 'use ' + @databaseName + ';'
            SET @sql =@sql + 'drop user ' + @user
            BEGIN TRY
                EXEC(@sql)
                PRINT @sql
            END TRY
            BEGIN CATCH
                PRINT '数据库' + @databaseName + '中不存在用户' + @user
            END CATCH
            FETCH NEXT FROM databaseNameCur INTO @databaseName
        END
    CLOSE databaseNameCur
    DEALLOCATE databaseNameCur
    USE master
    SET @sql = 'drop login ' + @user
    PRINT @sql
    EXEC(@sql)
  
    FETCH NEXT FROM curUsers INTO @user
END
CLOSE curUsers
DEALLOCATE curUsers


(责任编辑：IT)
    最近关注
    热点内容