代码改变世界

SQL Server如何正确的删除Windows认证用户

  潇湘隐者  阅读(1027)  评论(0编辑  收藏  举报

 

SQL Server数据库中,有时候会建立一些Windows认证的账号(域账号),例如,我们公司习惯给开发人员和Support同事开通NT账号权限,如果有离职或负责事宜变更的话,那么要如何正确的删除这些Windows认证账号呢?这篇文章就是来探讨一下如何正确的删除Windows认证账号。如下所示:

 

下面这种方式,仅仅是删除登录名(login),然而并没有删除用户(User)

 

USE [master]
GO
 
DROP LOGIN [xxx\xxxx]
GO

 

你删除登录名的时候,就会遇到类似下面的告警信息:

 

Deleting server logins does not delete the database users associated with the logins. To complete the process, delete the users in each database. It may be necessary to first transfer the ownership of schemas to new users.

 

 

也就是说,虽然你删除了登录名,但是对应用户数据库或系统数据库相关的User权限并没有清理,在SQL Server中登录名(Server Login)跟数据库的用户(database User)是分离开来,但是又有关联的。所以正确的姿势: 在删除登录名(login)后,还必须去每个数据库,删除对应的用户(user). 在删除登录名前必须检查,有那些作业的OWNER或数据库的OWNER的为该Windows认证账号(NT账号),否则后面就会遇到一些问题:

 

1:如果删除Windows认证用户前,没有修改作业的OWNER(如果此作业的OWNER为此Windows用户的话,那么删除Windows认证用户后,作业就会报类似下面这种错误。

 

The job failed.  The owner (xx\xxx) of job syspolicy_purge_history does not have server access.

 

    所以在删除Windows认证用户前,必须检查并修改作业的Owner,避免这种情况出现。

 

2:删除Windows认证用户前,确认是否有数据库的OWNER为此Windows认证用户。否则删除登录名时会报错

 

Msg 15174, Level 16, State 1, Line 4

Login 'xxx\xxxx' owns one or more database(s). Change the owner of the database(s) before dropping the login.

 

Msg 15174, Level 16, State 1, Line 4

登录名 'xxx\xxx' 拥有一个或多个数据库。在删除该登录名之前,请更改相应数据库的所有者。

 

必须修改数据库的Owner后(一般将数据库的owner改为sa),才能删除登录名

 

sp_changedbowner 'sa'

 

 

3:有时候删除用户时,报下面错误,必须修改后,才能删除对应的用户。

 

 

 

遇到下面错误:

 

Msg 15138, Level 16, State 1, Line 3

数据库主体在该数据库中拥有 架构,无法删除。

 

Msg 15138, Level 16, State 1, Line 3

The database principal owns a schema in the database, and cannot be dropped.

 

clip_image001

 

 

USE YourSQLDba;
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner]  TO [dbo];
 
 
USE [YourSQLDba]
GO 
DROP USER [xxx\konglb];
GO
 

   当然要根据实际情况来处理

 

 

 
USE [UserDatabase];
GO
ALTER AUTHORIZATION ON SCHEMA::[xxx]  TO [dbo];
 

另外一种是用户创建的Schema,这个根上面情况没有差别。

 

 

所以正确的删除登录名,可以用脚本生成对应的SQL(当然也可以执行对应的SQL,但是这种高危操作,建议生成脚本,人工判断后,手工执行)

 

DECLARE @login_name  sysname;
SET @login_name='xxx\xxxx'
 
SELECT  d.name        AS database_name,
        owner_sid    AS owner_sid ,
        l.name        AS database_owner
FROM    sys.databases d
        LEFT JOIN sys.syslogins l ON l.sid = d.owner_sid
WHERE l.name=@login_name;
 
 
 
SELECT  'USE ' + d.name + CHAR(10) 
        + 'GO' + CHAR(10)
        + 'EXEC dbo.sp_changedbowner @loginame =N''sa'', @map = false' AS change_db_owner_cmd
FROM    sys.databases d
        LEFT JOIN sys.syslogins l ON l.sid = d.owner_sid
WHERE   l.name = @login_name;
 
 
SELECT j.job_id                                       AS JOB_ID            
      ,j.name                                         AS JOB_NAME          
      ,CASE WHEN [enabled] =1 THEN 'Enabled'
                              ELSE 'Disabled'  END    AS JOB_ENABLED    
      ,l.name                                         AS JOB_OWNER   
      ,j.category_id                                  AS JOB_CATEGORY_ID
      ,c.name                                         AS JOB_CATEGORY_NAME
      ,[description]                                  AS JOB_DESCRIPTION   
      ,date_created                                   AS DATE_CREATED      
      ,date_modified                                  AS DATE_MODIFIED
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
INNER JOIN sys.syslogins l ON l.sid = j.owner_sid
WHERE l.name= @login_name
ORDER BY j.name
 
 
 
DECLARE  @job_owner   NVARCHAR(32);
 
SET @job_owner='sa';
 
SELECT  'EXEC msdb.dbo.sp_update_job @job_name=N''' +j.name + ''', @owner_login_name=N''' + RTRIM(LTRIM(@job_owner)) + ''';' AS change_job_owner_cmd
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
INNER JOIN sys.syslogins l ON l.sid = j.owner_sid
WHERE l.name = @login_name
ORDER BY j.name
 
 
SELECT  '
USE [master]
GO
DROP LOGIN  ' + QUOTENAME(@login_name) +  
'
GO
' AS  drop_login_user;

 

然后删除用户(User),此脚本也可以清理那些登录名已经删除,但是对应的USER没有清理的Windows 认证用户。此脚本可能有一些逻辑上的Bug,个人也是fix掉了一些Bug后,才发布这篇博客。如果遇到什么Bug,可以留言反馈。


   --注意:但是之前脚本也有弊端,有时候login对应多个数据库的User,需要在多个数据库运行。所以个人优化了SQL,你可以忽略上面的SQL,直接执行下面SQL即可(2020-03-10修改过下面脚本)

--==================================================================================================================
--        ScriptName            :            drop_logins_users.sql
--        Author                :            潇湘隐者    
--        CreateDate            :            2015-12-18
--        Description           :            彻底、完全的删除某个login以及对应的user等权限
--        Note                  :            
/*******************************************************************************************************************
        Parameters              :                                    参数说明
********************************************************************************************************************
            @login_name         :            你要删除的登录名(Windows认证账号或SQL认证账号)
********************************************************************************************************************
        Notice                  :            脚本不会去删除user对应的schema,如果要删除对应的schema.需要修改脚本。
********************************************************************************************************************
   Modified Date    Modified User     Version                 Modified Reason
********************************************************************************************************************
    2015-12-18        潇湘隐者         V01.00.00        新建该脚本。
    2020-03-18        潇湘隐者         V01.01.00        Fix掉脚本在SQL Server 2005下存在Incorrect syntax near '+'.
                                                    Incorrect syntax near '='.这类错误
    2021-02-25        潇湘隐者         V01.01.00      解决sp_changeobjectowner修改对象owner遇到"Msg 15001, Level 16,..."错误
*******************************************************************************************************************/
--==================================================================================================================
DECLARE @database_id    INT;
DECLARE @database_name  sysname;
DECLARE @cmdText        NVARCHAR(MAX);
DECLARE @prc_text       NVARCHAR(MAX);
DECLARE @RowIndex       INT;
DECLARE @login_name     NVARCHAR(128);
DECLARE @job_owner      NVARCHAR(32);
 
 
SET @login_name='klb';
SET @job_owner='sa';
 
 
IF OBJECT_ID('tempdb.dbo.#databases') IS NOT NULL
    DROP TABLE dbo.#databases;
 
CREATE TABLE #databases
(
      database_id INT ,
      database_name sysname
);
 
 
INSERT  INTO #databases
SELECT  database_id ,
        name
FROM    sys.databases
WHERE   name NOT IN'tempdb', 'model', 'YourSQLDba' )
        AND state = 0; --state_desc=ONLINE 
 
 
IF OBJECT_ID('tempdb.dbo.#removed_user') IS NOT NULL
    DROP TABLE dbo.#removed_user;
 
CREATE TABLE #removed_user
(
    username sysname
)
 
 
IF OBJECT_ID('tempdb.dbo.#sql_text') IS NOT NULL
    DROP TABLE dbo.#sql_text
 
CREATE TABLE #sql_text
(    
    sql_type  NVARCHAR(32),
    sql_cmd   NVARCHAR(MAX)
);
 
 
--查看作业的OWNER为当前账户的详细信息
SELECT j.job_id                                              AS JOB_ID            
        ,j.name                                           AS JOB_NAME          
        ,CASE WHEN [enabled] =1 THEN 'Enabled'
                                ELSE 'Disabled'  END      AS JOB_ENABLED    
        ,l.name                                           AS JOB_OWNER   
        ,j.category_id                                    AS JOB_CATEGORY_ID
        ,c.name                                           AS JOB_CATEGORY_NAME
        ,[description]                                    AS JOB_DESCRIPTION   
        ,date_created                                     AS DATE_CREATED      
        ,date_modified                                    AS DATE_MODIFIED
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
INNER JOIN sys.syslogins l ON l.sid = j.owner_sid
WHERE l.name=@login_name
ORDER BY j.name;
 
 
 
--修改作业的Owner
INSERT INTO #sql_text
SELECT   'change_job_owner'  AS sql_type
        ,'EXEC msdb.dbo.sp_update_job @job_name=N''' 
        +j.name + ''', @owner_login_name=N''' 
        + RTRIM(LTRIM(@job_owner)) + ''';' 
                            AS change_job_owner_cmd
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
INNER JOIN sys.syslogins l ON l.sid = j.owner_sid
WHERE l.name = @login_name
ORDER BY j.name;
 
 
--查看数据库的Owner=@login_name的详细信息
SELECT d.database_id                AS database_id,
       d.name                        AS database_name,
       suser_sname(d.owner_sid)        AS database_owner,
       d.create_date                AS create_date,
       d.collation_name                AS collation_name,
       d.state_desc                    AS state_desc
FROM sys.databases d
WHERE suser_sname(d.owner_sid)=@login_name;
 
 
 
--修改数据库的Owner为sa
INSERT INTO #sql_text
SELECT  'change_db_owner' AS sql_type
       ,'USE ' +name +'; '+ CHAR(10) +'GO' + CHAR(10) +'exec sp_changedbowner ''sa'';' +CHAR(10) + 'GO' +CHAR(10)
FROM sys.databases d
WHERE suser_sname(d.owner_sid)=@login_name;
 
--IF OBJECT_ID('tempdb.dbo.#db_owner_info') IS NOT NULL
--    DROP TABLE dbo.#db_owner_info;
 
--CREATE TABLE #db_owner_info
--(    
--     database_name      NVARCHAR(64)
--    ,database_owner   NVARCHAR(64)
--    ,create_date      DATETIME
--    ,collcation_name  NVARCHAR(64)
--    ,state_desc          NVARCHAR(60)
--);
 
 
IF OBJECT_ID('tempdb.dbo.#schema_info') IS NOT NULL
    DROP TABLE dbo.#schema_info;
 
CREATE TABLE #schema_info
(    
     database_name      NVARCHAR(64)
    ,users_name          NVARCHAR(64)
    ,schemas_name      NVARCHAR(64)
    ,schema_id          INT
    ,principal_id      INT
);
 
IF OBJECT_ID('tempdb.dbo.#schema_detail') IS NOT NULL
    DROP TABLE dbo.#schema_detail;
 
CREATE TABLE #schema_detail
(    
     database_name      NVARCHAR(64)
    ,objects_id          INT
    ,objects_name      NVARCHAR(64)
    ,schemas_name      NVARCHAR(64)
    ,object_type      sysname
    ,create_date      DATETIME
    ,modify_date      DATETIME
);
 
 
 
 
--开始循环每一个用户数据库(排除了上面相关数据库)
WHILE 1= 1
BEGIN
 
 
    SELECT TOP 1 @database_name= database_name   
    FROM #databases
    ORDER BY database_id;
 
    IF @@ROWCOUNT =0 
        BREAK;
 
 
    SET @cmdText =  'USE ' + @database_name + ';' +CHAR(10);
 
    --这里有个bug,如果用户删除了对应的login,那么这里查询结果就会没有记录
    --SELECT @cmdText += 'INSERT INTO #removed_user
    --                    SELECT  name FROM sys.sysusers
    --                    WHERE sid IN (SELECT sid FROM sys.syslogins WHERE isntname=1 AND name='''+ @login_name + ''');'
    
    SELECT @cmdText =@cmdText+ 'INSERT INTO #removed_user
                        SELECT  name FROM sys.sysusers WHERE  name='''+ @login_name + ''';'
    
    --PRINT(@cmdText);
    EXEC SP_EXECUTESQL @cmdText;
 
     
    --SELECT @database_name AS database_name;
 
 
 
    --INSERT INTO #db_owner_info
 --   SELECT  d.name                AS database_name ,
 --           l.name                AS database_owner ,
 --           d.create_date         AS create_date ,
 --           d.collation_name      AS collcation_name ,
 --           d.state_desc          AS state_desc
 --   FROM    sys.databases d
 --           INNER JOIN sys.syslogins l ON d.owner_sid = l.sid
 --           INNER JOIN #removed_user r ON r.username = l.name
 
    
    SET @cmdText =  'USE ' + @database_name + ';' +CHAR(10)
 
    
    SET @cmdText =@cmdText+ 'SELECT DB_NAME(), r.username,s.name, s.schema_id, s.principal_id FROM sys.schemas  s
                        INNER JOIN  #removed_user r ON s.name =r.username Collate Database_Default' + CHAR(10);
     
    INSERT INTO #schema_info
    EXEC SP_EXECUTESQL @cmdText;
 
 
    SET @cmdText =  'USE ' + @database_name + ';' +CHAR(10)
 
    --SET @cmdText += 'SELECT * FROM sys.objects WHERE schema_id IN (SELECT s.schema_id FROM sys.schemas s INNER JOIN  #removed_user r ON s.name =r.username Collate Database_Default);'
 
    SET @cmdText = @cmdText+'SELECT  DB_NAME(),o.object_id, o.name,SCHEMA_NAME(o.schema_id), o.type_desc, o.create_date,o.modify_date  FROM sys.objects o
                        INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
                        INNER JOIN  #removed_user r ON s.name =r.username Collate Database_Default'
 
    INSERT INTO #schema_detail
            ( 
                 database_name      
                ,objects_id          
                ,objects_name      
                ,schemas_name      
                ,object_type      
                ,create_date      
                ,modify_date
            )
    EXEC SP_EXECUTESQL @cmdText;
    
    /****************************************************************************************************
    --不能先执行类似这样的脚本,否则sp_changeobjectowner修改owner会出现Msg 15001, Level 16, State 1这样地错误
    --具体参考https://www.cnblogs.com/kerrycode/p/14446167.html
        USE AdventureWorks2014;
        GO
        ALTER AUTHORIZATION ON SCHEMA::[klb]  TO [dbo];
        GO
    ****************************************************************************************************/
    /*
    SET @cmdText =  'USE ' + @database_name + ';' +CHAR(10)
    SET @cmdText =@cmdText+ 'SELECT  ''change_schema_owner'' AS sql_type , ''USE ' + @database_name + ';'' + CHAR(10) +''GO'' + CHAR(10) +''ALTER AUTHORIZATION ON SCHEMA::'' +QUOTENAME(s.name) +''  TO [dbo];'' + CHAR(10) + ''GO'' AS change_schema_cmd FROM sys.schemas  s
                        INNER JOIN  #removed_user r ON s.name =r.username Collate Database_Default '  + CHAR(10);
    
    INSERT INTO #sql_text
    EXEC SP_EXECUTESQL @cmdText, N'@database_name sysname',@database_name ;
    */
 
    
 
 
    SET @cmdText =  'USE ' + @database_name + ';' +CHAR(10)
    SET @cmdText =@cmdText+ 'SELECT  ''change_schema_cmd'' AS sql_type , ''USE ' + @database_name + ';'' + CHAR(10) +''GO'' + CHAR(10) +''exec sp_changeobjectowner '''''' + SCHEMA_NAME(o.schema_id) +''.'' + QUOTENAME(o.name) +''''''  , ''''dbo'''';'' + CHAR(10) + ''GO'' AS change_schema_cmd 
                        FROM sys.schemas  s
                        INNER JOIN sys.objects o  ON o.schema_id =s.schema_id
                        INNER JOIN  #removed_user r ON s.name =r.username Collate Database_Default '  + CHAR(10);
    
    PRINT @cmdText;
    INSERT INTO #sql_text 
    EXEC SP_EXECUTESQL @cmdText, N'@database_name sysname',@database_name ;
    
 
    SET @cmdText =  'USE ' + @database_name + ';' +CHAR(10)
    SET @cmdText =@cmdText+ 'SELECT ''drop_schema_cmd'' AS drop_schema_cmd, ''USE ' + @database_name + ';'' + CHAR(10) +''GO'' + CHAR(10) +''DROP SCHEMA '' +QUOTENAME(s.SCHEMA_NAME) +'';'' +CHAR(10) + ''GO'' AS drop_schema_cmd
                     FROM    INFORMATION_SCHEMA.SCHEMATA s
                     INNER JOIN  #removed_user r ON s.SCHEMA_OWNER =r.username Collate Database_Default'  + CHAR(10);
    PRINT @cmdText;
    INSERT INTO #sql_text
    EXEC SP_EXECUTESQL @cmdText, N'@database_name sysname',@database_name ;
 
    INSERT INTO #sql_text
    SELECT 'drop_user_cmd' AS drop_schema_cmd,
            'USE ' + QUOTENAME(@database_name) + CHAR(10)
          + 'GO '  + CHAR(10)
          + 'DROP USER ' + QUOTENAME(username) +';' + CHAR(10)
          + 'GO'  AS drop_user_cmd 
    FROM #removed_user;
 
    
    TRUNCATE TABLE #removed_user;
 
 
    DELETE FROM #databases WHERE database_name=@database_name;
 
END
 
 
 
 
 
 
--删除login名
INSERT INTO #sql_text
SELECT  'drop_login_cmd' AS drop_schema_cmd,
'USE [master]
GO
DROP LOGIN  ' + QUOTENAME(name) +  CHAR(10) +'GO' AS  drop_login_user
FROM sys.syslogins WHERE name=@login_name;
 
 
SELECT * FROM  #schema_detail;
SELECT * FROM  #sql_text;
 
DROP TABLE #databases;
DROP TABLE #removed_user;
DROP TABLE #schema_detail;
DROP TABLE #sql_text;

编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
历史上的今天:
2018-09-21 Key Lookup开销过大导致聚集索引扫描
2018-09-21 SQL Server OPTION (OPTIMIZE FOR UNKNOWN) 测试总结
2010-09-21 SQL Server 2008 打SP1 补丁遇到的问题
2010-09-21 SQL Server 2008 筛选器报错Microsoft.SqlServer.Management.Sdk.Sfc
点击右上角即可分享
微信分享提示