手机凤凰-凤凰彩票手机app-凤凰彩票下载
做最好的网站
您的位置:手机凤凰 > 凤凰彩票手机app数据库 > 那么要如何正确的删除这些Windows认证账号呢

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

2020-03-29 05:52

前言

在SQL Server数据库中,不经常候会树立部分Windows认证的账号,举个例子,大家公司习于旧贯给开采人士和Support同事开通NT账号权限,假使有离职或肩负事宜退换的话,那么要哪些科学的去除那么些Windows认证账号呢?那篇小说就是来探求一下怎么着科学的删除Windows认证账号。如下所示:

上面这种办法,仅仅是去除登陆名,不过并从未去除顾客是分别开来,可是又有涉嫌的。所以正确的姿态: 在剔除登陆名后,还必需去各样数据库,删除相应的客户,不然前边就能够际遇有个别主题素材:

1:假使除去Windows认证客商前,未有更动作业的OWNE大切诺基(假如此作业的OWNE本田CR-V为此Windows顾客来说,那么删除Windows认证客户后,作业就能够报相似下边这种错误。

The job failed. The owner (xxxxx) of job syspolicy_purge_history does not have server access.

据此在剔除Windows认证顾客前,必须检查并校勘作业的Owner,防止这种景况出现。

2:删除Windows认证客户前,确认是不是有数据库的OWNEEnclave为此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卡塔尔,此脚本也得以清理这个登陆名已经去除,但是相应的USE大切诺基未有清理的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;

总结

如上就是那篇作品的全体内容了,希望本文的从头到尾的经过对大家的就学大概干活有所自然的参阅学习价值,谢谢我们对台本之家的支撑。

本文由手机凤凰发布于凤凰彩票手机app数据库,转载请注明出处:那么要如何正确的删除这些Windows认证账号呢

关键词: