那么要如何正确的删除这些Windows认证账号呢

前言

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

下面这种方式,仅仅是删除登录名,然而并没有删除用户是分离开来,但是又有关联的。所以正确的姿势: 在删除登录名后,还必须去每个数据库,删除对应的用户,否则后面就会遇到一些问题:

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

The job failed. The owner (xxxxx) 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 'xxxxxxx' 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

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

必须修改数据库的Owner后,才能删除登录名

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 [xxxkonglb];

GO

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

USE [UserDatabase];

GO

ALTER AUTHORIZATION ON SCHEMA::[xxx] TO [dbo];

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

所以正确的删除登录名,可以用脚本生成对应的SQL

DECLARE @login_name sysname;SET @login_name='GFG1chenzhenh'SELECT d.name AS database_name,owner_sid AS owner_sid ,l.name AS database_ownerFROM sys.databases dLEFT JOIN sys.syslogins l ON l.sid = d.owner_sidWHERE 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_cmdFROM sys.databases dLEFT JOIN sys.syslogins l ON l.sid = d.owner_sidWHERE 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_MODIFIEDFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_idINNER JOIN sys.syslogins l ON l.sid = j.owner_sidWHERE l.name= @login_nameORDER BY j.nameDECLARE @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_cmdFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_idINNER JOIN sys.syslogins l ON l.sid = j.owner_sidWHERE l.name = @login_nameORDER BY j.nameSELECT 'USE [master]GODROP LOGIN ' + QUOTENAME(@login_name) + 'GO' AS drop_login_user;

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

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 NULLDROP TABLE dbo.#databases;CREATE TABLE #databases(database_id INT,database_name sysname)INSERT INTO #databasesSELECT database_id ,nameFROM sys.databasesWHERE 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= 1BEGINSELECT TOP 1 @database_name= database_name FROM #databasesORDER BY database_id;IF @@ROWCOUNT =0 BREAK;SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)SELECT @cmdText += 'INSERT INTO #removed_userSELECT name FROM sys.sysusersWHERE sid NOT IN (SELECT sid FROM sys.syslogins WHERE isntname=1 AND name LIKE ''GFG1%'')AND isntname=1 AND name NOT IN (''NT AUTHORITYSYSTEM'')' + CHAR(10);EXEC SP_EXECUTESQL @cmdTextSELECT @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_MODIFIEDFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_idINNER JOIN sys.syslogins l ON l.sid = j.owner_sidINNER JOIN #removed_user r ON l.name = r.usernameORDER 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_descFROM sys.databases dINNER JOIN sys.syslogins l ON d.owner_sid = l.sidINNER JOIN #removed_user r ON r.username = l.nameSET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)SET @cmdText += 'SELECT * FROM sys.schemas sINNER 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 sINNER 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_cmdFROM INFORMATION_SCHEMA.SCHEMATA sINNER 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_cmdFROM #removed_user;TRUNCATE TABLE #removed_user;DELETE FROM #databases WHERE database_name=@database_name;ENDDROP TABLE #databases;DROP TABLE #removed_user;

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

本文由快3平台发布于快3正规平台数据库,转载请注明出处:那么要如何正确的删除这些Windows认证账号呢

相关阅读