SQL Server正確刪除Windows認證使用者的方法教程

潇湘隐者發表於2019-10-10
SQLServerWindows

文章主要給大家介紹了關於SQL Server正確刪除Windows認證使用者的相關資料，文中透過示例程式碼介紹的非常詳細，對大家學習或者使用SQL Server具有一定的參考學習價值，

前言

在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;

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值。

相關文章