在SQL Server資料庫中，有時候會建立一些Windows認證的賬號（域賬號）,例如，我們公司習慣給開發人員和Support同事開通NT賬號權限，如果有離職或負責事宜變更的話，那么要如何正確的洗掉這些Windows認證賬號呢？這篇文章就是來探討一下如何正確的洗掉Windows認證賬號，如下所示：
下面這種方式，僅僅是洗掉登錄名（login），然而并沒有洗掉用戶（User)
USE [master]GODROP 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;GOALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dbo];
USE [YourSQLDba]GO DROP USER [xxx\konglb];
GO
當然要根據實際情況來處理
USE [UserDatabase];GOALTER AUTHORIZATION ON SCHEMA::[xxx] TO [dbo];
另外一種是用戶創建的Schema,這個根上面情況沒有差別，
所以正確的洗掉登錄名,可以用腳本生成對應的SQL（當然也可以執行對應的SQL，但是這種高危操作，建議生成腳本，人工判斷后，手工執行）
DECLARE @login_name sysname;SET @login_name='xxx\xxxx'
SELECT d.name AS database_name,
owner_sid AS owner_sid , l.name AS database_ownerFROM sys.databases d
LEFT 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 d
LEFT 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 j
INNER 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_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_idINNER JOIN sys.syslogins l ON l.sid = j.owner_sidWHERE 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，可以留言反饋，
--注意：但是之前腳本也有弊端，有時候login對應多個資料庫的User，需要在多個資料庫運行，所以個人優化了SQL，你可以忽略上面的SQL，直接執行下面SQL即可（2020-03-10修改過下面腳本）
--==================================================================================================================
-- ScriptName : drop_logins_users.sql-- Author : 瀟湘隱者
-- CreateDate : 2015-12-18
-- Description : 徹底、完全的洗掉某個login以及對應的user等權限
-- Note :
/******************************************************************************************************************* Parameters : 引數說明******************************************************************************************************************** @login_name : 你要洗掉的登錄名(Windows認證賬號或SQL認證賬號)******************************************************************************************************************** Notice ： ******************************************************************************************************************** Modified Date Modified User Version Modified Reason******************************************************************************************************************** 2018-08-03 瀟湘隱者 V01.00.00 新建該腳本，*******************************************************************************************************************/--==================================================================================================================
DECLARE @database_id INT;
DECLARE @database_name sysname;DECLARE @cmdText NVARCHAR(MAX);
DECLARE @prc_text NVARCHAR(MAX);
DECLARE @RowIndex INT;
DECLARE @login_name NVARCHAR(128);DECLARE @job_owner NVARCHAR(32);SET @login_name='xxxx';
SET @job_owner='sa';
IF OBJECT_ID('tempdb.dbo.#databases') IS NOT NULL
DROP TABLE dbo.#databases;
CREATE TABLE #databases
(
database_id INT ,database_name sysname
);
INSERT INTO #databasesSELECT database_id ,name
FROM sys.databasesWHERE name NOT IN ( 'tempdb', 'model', 'YourSQLDba' )
AND state = 0; --state_desc=ONLINE
IF OBJECT_ID('tempdb.dbo.#removed_user') IS NOT NULL
DROP TABLE dbo.#removed_user;
CREATE TABLE #removed_user
(
username sysname
)
IF OBJECT_ID('tempdb.dbo.#sql_text') IS NOT NULL
DROP TABLE dbo.#sql_text
CREATE TABLE #sql_text
(
sql_type NVARCHAR(32),
sql_cmd NVARCHAR(MAX));
--查看作業的OWNER為當前賬戶的詳細資訊
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_id
INNER JOIN sys.syslogins l ON l.sid = j.owner_sid
WHERE l.name=@login_nameORDER BY j.name;
--修改作業的Owner
INSERT INTO #sql_textSELECT 'change_job_owner' AS sql_type
,'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 jINNER 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_nameORDER BY j.name;
--查看資料庫的Owner=@login_name的詳細資訊
SELECT d.database_id AS database_id,
d.name AS database_name, suser_sname(d.owner_sid) AS database_owner, d.create_date AS create_date, d.collation_name AS collation_name, d.state_desc AS state_descFROM sys.databases dWHERE suser_sname(d.owner_sid)=@login_name;--修改資料庫的Owner為sa
INSERT INTO #sql_textSELECT 'change_db_owner' AS sql_type
,'USE ' +name +'; '+ CHAR(10) +'GO' + CHAR(10) +'exec sp_changedbowner ''sa'';' +CHAR(10) + 'GO' +CHAR(10)
FROM sys.databases dWHERE suser_sname(d.owner_sid)=@login_name;IF OBJECT_ID('tempdb.dbo.#schema_info') IS NOT NULL
DROP TABLE dbo.#schema_info;
CREATE TABLE #schema_info
(
database_name NVARCHAR(64)
,users_name NVARCHAR(64)
,schemas_name NVARCHAR(64)
,schema_id INT ,principal_id INT);
IF OBJECT_ID('tempdb.dbo.#schema_detail') IS NOT NULL
DROP TABLE dbo.#schema_detail;
CREATE TABLE #schema_detail
(
database_name NVARCHAR(64)
,objects_id INT,objects_name NVARCHAR(64)
,schemas_name NVARCHAR(64)
,object_type sysname
,create_date DATETIME
,modify_date DATETIME
);
--開始回圈每一個用戶資料庫（排除了上面相關資料庫）
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);
--這里有個bug，如果用戶洗掉了對應的login，那么這里查詢結果就會沒有記錄
--SELECT @cmdText += 'INSERT INTO #removed_user
-- SELECT name FROM sys.sysusers
-- WHERE sid IN (SELECT sid FROM sys.syslogins WHERE isntname=1 AND name='''+ @login_name + ''');'
SELECT @cmdText += 'INSERT INTO #removed_user
SELECT name FROM sys.sysusers WHERE name='''+ @login_name + ''';'
--PRINT(@cmdText); EXEC SP_EXECUTESQL @cmdText;SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)
SET @cmdText += 'SELECT DB_NAME(), r.username,s.name, s.schema_id, s.principal_id FROM sys.schemas s
INNER JOIN #removed_user r ON s.name =r.username Collate Database_Default' + CHAR(10); INSERT INTO #schema_info 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);'
SET @cmdText += 'SELECT DB_NAME(),o.object_id, o.name,SCHEMA_NAME(o.schema_id), o.type_desc, o.create_date,o.modify_date FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
INNER JOIN #removed_user r ON s.name =r.username Collate Database_Default'
INSERT INTO #schema_detail(
database_name
,objects_id
,objects_name
,schemas_name
,object_type
,create_date
,modify_date
)
EXEC SP_EXECUTESQL @cmdText;SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)
SET @cmdText += 'SELECT ''change_schema_cmd'' AS sql_type , ''USE ' + @database_name + ';'' + CHAR(10) +''GO'' + CHAR(10) +''ALTER AUTHORIZATION ON SCHEMA::'' +QUOTENAME(s.name) +'' TO [dbo];'' + CHAR(10) + ''GO'' AS change_schema_cmd FROM sys.schemas s
INNER JOIN #removed_user r ON s.name =r.username Collate Database_Default ' + CHAR(10); PRINT @cmdText; INSERT INTO #sql_textEXEC SP_EXECUTESQL @cmdText, N'@database_name sysname',@database_name ;
SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)
SET @cmdText += 'SELECT ''drop_schema_cmd'' AS drop_schema_cmd, ''USE ' + @database_name + ';'' + CHAR(10) +''GO'' + CHAR(10) +''DROP SCHEMA '' +QUOTENAME(s.SCHEMA_NAME) +'';'' +CHAR(10) + ''GO'' AS drop_schema_cmd
FROM INFORMATION_SCHEMA.SCHEMATA s
INNER JOIN #removed_user r ON s.SCHEMA_OWNER =r.username Collate Database_Default' + CHAR(10); INSERT INTO #sql_textEXEC SP_EXECUTESQL @cmdText, N'@database_name sysname',@database_name ;
INSERT INTO #sql_textSELECT 'drop_user_cmd' AS drop_schema_cmd,
'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--洗掉login名
INSERT INTO #sql_textSELECT 'drop_login_cmd' AS drop_schema_cmd,
'USE [master]GO
DROP LOGIN ' + QUOTENAME(name) + CHAR(10) +'GO' AS drop_login_user
FROM sys.syslogins WHERE name=@login_name;
SELECT * FROM #schema_detail;
SELECT * FROM #sql_text;
DROP TABLE #databases;
DROP TABLE #removed_user;
DROP TABLE #schema_detail;
DROP TABLE #sql_text;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/39243.html
標籤：SQL Server
下一篇：資料庫設計優化