艾锑知识 | SQL Server正确删除Windows认证用户的方法

2020-03-04 16:31 作者：艾锑无限 浏览量：

   
疫情放慢了管理者的脚步,却没有放慢管理者的心,对于企业来说,在富有的时候,可以说说情怀,说说理想,但在贫穷受困的时候,没有饭吃的时候,情怀和理想其实并不重要,重要的是下一顿吃什么?在哪吃呢?
 
前几天,有篇文章报道某云南大型企业的人事总监被总经理怒骂的邮件很火,为什么会发生这样的事,我觉得身为一个企业的员工,无论你处在什么岗位,什么职位,一定要审时度势,要知道企业要什么,俗话说,大河有水小河满,只有企业活下来了,活好了,组织里的员工才能真正拿到自己想要的,如果在危难时机,你还在坚持自己观点,抱持着自己的思维不改变,不能与企业思想同步,那最终也不会有什么好的结果.
 
在这里艾锑无限给已经上班或即将上班的各个企业伙伴一些忠告:
 
1,困难时期,在为自己着想的同时多想想能为企业做点什么,今天你的付出,明天不一定会有收获,但长久来看,能量守恒定律,你是不会吃亏的.
 
2,在经济还没复苏的时期,企业还不太忙的时候,除了做好自己部门手头上的工作以外,把自己的大脑开动起来,如果你是职员,就想想假如你是这个部门经理,你如何做可以让部门的损失降到最低,让收益提升到最大.如果你是经理就想想假如你是企业的CEO,你如何做可以让企业快速恢复状态,让业务快速发展起来,让现金流可以流动起来?
 
3,如果你处在花钱的部门,就想想如何做可以省钱,又可以为部门创收,如果你是创收的部门,就想想原来从来就没想过的问题,因为你不能破旧就不能立新,不能创造就是在消耗.
 
4,管理团队间,如果不是能力问题,不仅行动要勤快,大脑更要勤快一些,除了思考自己部门的工作,也要思考其他部门的工作,你的团队可以做些什么更利于其他部门的发展,你可以做些什么更助于其他管理者达成目标,整个企业就像一台齿轮吻合的机器,只有彼此主动吻合才能让这台机器更好的运转,这也像打群架,如果每一个人都能照顾好自己,还能为他人着想,就不会受伤,也能全身而退,有一个人需要他人照顾,就很可能有人受伤,并导致全队受损.
 
5,无论什么时期,不要害怕困难,不要拒绝烦恼, 烦恼即菩提,菩提即智慧,伟大的洞见和上师都是来自于苦难和无数次的折磨.相信宇宙的安排,无论是事,还是人,来到你的身边就是成就你的,所以把心安于当下,尽一切努力做到尽善尽美.
 
接下来分享一则技术信息,以许会对你的企业有所帮助:

艾锑知识 |SQL Server正确删除Windows认证用户的方法

前言

在SQL Server数据库中，有时候会建立一些Windows认证的账号（域账号）,例如，我们公司习惯给开发人员和Support同事开通NT账号权限，如果有离职或负责事宜变更的话，那么要如何正确的删除这些Windows认证账号呢？这篇文章就是来探讨一下如何正确的删除Windows认证账号。如下所示：
下面这种方式，仅仅是删除登录名（login），然而并没有删除用户（User)

1 2 3 4 5

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.

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，但是这种高位操作，建议生成脚本，人工判断后，手工执行）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

DECLARE @login_name sysname; SET @login_name='GFG1\chenzhenh'   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，可以留言反馈。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127

DECLARE @database_id INT; DECLARE @database_name sysname; DECLARE @cmdText NVARCHAR(MAX); DECLARE @prc_text NVARCHAR(MAX); DECLARE @RowIndex INT; DECLARE @user_name NVARCHAR(128);     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 ( 'master', 'tempdb', 'model', 'msdb', 'distribution', 'ReportServer', 'ReportServerTempDB', 'YourSQLDba' ) AND state = 0; --state_desc=ONLINE     CREATE TABLE #removed_user ( username sysname )   --开始循环每一个用户数据库（排除了上面相关数据库） 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)   SELECT @cmdText += 'INSERT INTO #removed_user SELECT name FROM sys.sysusers WHERE sid NOT IN (SELECT sid FROM sys.syslogins WHERE isntname=1 AND name LIKE ''GFG1%'') AND isntname=1 AND name NOT IN (''NT AUTHORITY\SYSTEM'')' + CHAR(10);   EXEC SP_EXECUTESQL @cmdText   SELECT @database_name AS database_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 INNER JOIN #removed_user r ON l.name = r.username ORDER BY j.name;     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 += 'SELECT * FROM sys.schemas s INNER JOIN #removed_user r ON s.name =r.username Collate Database_Default' + CHAR(10);   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);'   EXEC SP_EXECUTESQL @cmdText;   SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10) SET @cmdText += 'SELECT ''USE ' + @database_name + ';'' + CHAR(10) +''GO'' + CHAR(10) +''ALTER AUTHORIZATION ON SCHEMA::'' +QUOTENAME(s.name) +'' TO [dbo];'' AS change_schema_cmd FROM sys.schemas s INNER JOIN #removed_user r ON s.name =r.username Collate Database_Default ' + CHAR(10);   EXEC SP_EXECUTESQL @cmdText, N'@database_name sysname',@database_name ;   SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10) SET @cmdText += 'SELECT ''USE ' + @database_name + ';'' + CHAR(10) +''GO'' + CHAR(10) +''ALTER AUTHORIZATION ON SCHEMA::'' +QUOTENAME(s.SCHEMA_NAME) +'' TO [dbo];'' AS change_schema_cmd FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN #removed_user r ON s.SCHEMA_OWNER =r.username Collate Database_Default' + CHAR(10);   EXEC SP_EXECUTESQL @cmdText, N'@database_name sysname',@database_name ;   SELECT '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   DROP TABLE #databases; DROP TABLE #removed_user;

总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值