SQL Server正確刪除Windows認(rèn)證用戶的方法
前言
在SQL Server數(shù)據(jù)庫中,有時(shí)候會(huì)建立一些Windows認(rèn)證的賬號(hào)(域賬號(hào)),例如,我們公司習(xí)慣給開發(fā)人員和Support同事開通NT賬號(hào)權(quán)限,如果有離職或負(fù)責(zé)事宜變更的話,那么要如何正確的刪除這些Windows認(rèn)證賬號(hào)呢?這篇文章就是來探討一下如何正確的刪除Windows認(rèn)證賬號(hào)。如下所示:
下面這種方式,僅僅是刪除登錄名(login),然而并沒有刪除用戶(User)
USE [master] GO DROP LOGIN [xxx\xxxx] GO
你刪除登錄名的時(shí)候,就會(huì)遇到類似下面的告警信息:
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.
也就是說,雖然你刪除了登錄名,但是對(duì)應(yīng)用戶數(shù)據(jù)庫或系統(tǒng)數(shù)據(jù)庫相關(guān)的User權(quán)限并沒有清理,在SQL Server中登錄名(Server Login)跟數(shù)據(jù)庫的用戶(database User)是分離開來,但是又有關(guān)聯(lián)的。所以正確的姿勢(shì): 在刪除登錄名(login)后,還必須去每個(gè)數(shù)據(jù)庫,刪除對(duì)應(yīng)的用戶(user). 在刪除登錄名前必須檢查,有那些作業(yè)的OWNER或數(shù)據(jù)庫的OWNER的為該Windows認(rèn)證賬號(hào)(NT賬號(hào)),否則后面就會(huì)遇到一些問題:
1:如果刪除Windows認(rèn)證用戶前,沒有修改作業(yè)的OWNER(如果此作業(yè)的OWNER為此Windows用戶的話,那么刪除Windows認(rèn)證用戶后,作業(yè)就會(huì)報(bào)類似下面這種錯(cuò)誤。
The job failed. The owner (xx\xxx) of job syspolicy_purge_history does not have server access.
所以在刪除Windows認(rèn)證用戶前,必須檢查并修改作業(yè)的Owner,避免這種情況出現(xiàn)。
2:刪除Windows認(rèn)證用戶前,確認(rèn)是否有數(shù)據(jù)庫的OWNER為此Windows認(rèn)證用戶。否則刪除登錄名時(shí)會(huì)報(bào)錯(cuò)
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' 擁有一個(gè)或多個(gè)數(shù)據(jù)庫。在刪除該登錄名之前,請(qǐng)更改相應(yīng)數(shù)據(jù)庫的所有者。
必須修改數(shù)據(jù)庫的Owner后(一般將數(shù)據(jù)庫的owner改為sa),才能刪除登錄名
sp_changedbowner 'sa'
3:有時(shí)候刪除用戶時(shí),報(bào)下面錯(cuò)誤,必須修改后,才能刪除對(duì)應(yīng)的用戶。
遇到下面錯(cuò)誤:
Msg 15138, Level 16, State 1, Line 3
數(shù)據(jù)庫主體在該數(shù)據(jù)庫中擁有 架構(gòu),無法刪除。
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
當(dāng)然要根據(jù)實(shí)際情況來處理
USE [UserDatabase];
GO
ALTER AUTHORIZATION ON SCHEMA::[xxx] TO [dbo];
另外一種是用戶創(chuàng)建的Schema,這個(gè)根上面情況沒有差別。
所以正確的刪除登錄名,可以用腳本生成對(duì)應(yīng)的SQL(當(dāng)然也可以執(zhí)行對(duì)應(yīng)的SQL,但是這種高位操作,建議生成腳本,人工判斷后,手工執(zhí)行)
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),此腳本也可以清理那些登錄名已經(jīng)刪除,但是對(duì)應(yīng)的USER沒有清理的Windows 認(rèn)證用戶。此腳本可能有一些邏輯上的Bug,個(gè)人也是fix掉了一些Bug后,才發(fā)布這篇博客。如果遇到什么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 ) --開始循環(huán)每一個(gè)用戶數(shù)據(jù)庫(排除了上面相關(guān)數(shù)據(jù)庫) 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;
總結(jié)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,謝謝大家對(duì)腳本之家的支持。
相關(guān)文章
sql server編寫archive通用模板腳本實(shí)現(xiàn)自動(dòng)分批刪除數(shù)據(jù)
這篇文章主要介紹了sql server編寫archive通用模板腳本實(shí)現(xiàn)自動(dòng)分批刪除數(shù)據(jù),需要的朋友可以參考下2019-10-10在SQL Server數(shù)據(jù)庫中為標(biāo)識(shí)(IDENTITY)列插入顯式值
SQL Server中的標(biāo)識(shí)列和ACCESS中的“自動(dòng)編號(hào)”相似,都是插入記錄的時(shí)候自動(dòng)生成,一般不允許也不需要我們?nèi)ナ謩?dòng)修改它2007-02-02SQL Server開放(或關(guān)閉)端口連接的實(shí)現(xiàn)步驟
為了保證系統(tǒng)的安全性,可以關(guān)閉/開啟端口,本文主要介紹了SQL Server開放(或關(guān)閉)端口連接的實(shí)現(xiàn)步驟,具有一定的參考價(jià)值,感興趣的可以了解一下2023-11-11當(dāng)恢復(fù)sqlserver bak文件時(shí),原始的用戶無法刪除的解決方法
當(dāng)你從現(xiàn)有的bak文件,恢復(fù)數(shù)據(jù)庫時(shí),如果數(shù)據(jù)庫本身帶有一個(gè)用戶:比如用戶叫:DemoUser.2010-06-06SQL使用登錄名連接數(shù)據(jù)庫報(bào)錯(cuò)(錯(cuò)誤代碼18456問題)
18456是因密碼或用戶名錯(cuò)誤而使身份驗(yàn)證失敗,如果用戶名密碼正確,則該登錄名沒有連接數(shù)據(jù)庫引擎權(quán)限,本文就來介紹一下解決方法,具有一定的參考價(jià)值,感興趣的可以了解一下2023-10-10SQL Server 開窗函數(shù) Over()代替游標(biāo)的使用詳解
這篇文章主要介紹了SQL Server 開窗函數(shù) Over()代替游標(biāo)的使用,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-10-10在SQL觸發(fā)器或存儲(chǔ)過程中獲取在程序登錄的用戶
每個(gè)用戶可以登錄系統(tǒng),在程序中操作數(shù)據(jù)(添加,更新和刪除)需要實(shí)現(xiàn)記錄操作跟蹤。是誰添加,更新和刪除的,這些信息將會(huì)插入至AuditLog表中2012-01-01