欢迎投稿

今日深度:

SQL Server如何正确的删除Windows认证用户,正确退出windows

SQL Server如何正确的删除Windows认证用户,正确退出windows


?

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.

?

?

?

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='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,可以留言反馈。

?

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;

?

?

www.htsjk.Com true http://www.htsjk.com/Sql_Server/35992.html NewsArticle SQL Server如何正确的删除Windows认证用户,正确退出windows 在 SQL Server 大乐透追加倍投多少钱中,有时候会建立一些 Windows 认证的账号(域账号) , 例如,我们公司 习惯给开发人员和Support同事开通NT账号...
相关文章
    暂无相关文章
评论暂时关闭