艾銻知識(shí) | SQL Server正確刪除Windows認(rèn)證用戶的方法

2020-03-04 16:31 作者：艾銻無(wú)限 瀏覽量：

   
疫情放慢了管理者的腳步,卻沒(méi)有放慢管理者的心,對(duì)于企業(yè)來(lái)說(shuō),在富有的時(shí)候,可以說(shuō)說(shuō)情懷,說(shuō)說(shuō)理想,但在貧窮受困的時(shí)候,沒(méi)有飯吃的時(shí)候,情懷和理想其實(shí)并不重要,重要的是下一頓吃什么?在哪吃呢?
 
前幾天,有篇文章報(bào)道某云南大型企業(yè)的人事總監(jiān)被總經(jīng)理怒罵的郵件很火,為什么會(huì)發(fā)生這樣的事,我覺(jué)得身為一個(gè)企業(yè)的員工,無(wú)論你處在什么崗位,什么職位,一定要審時(shí)度勢(shì),要知道企業(yè)要什么,俗話說(shuō),大河有水小河滿,只有企業(yè)活下來(lái)了,活好了,組織里的員工才能真正拿到自己想要的,如果在危難時(shí)機(jī),你還在堅(jiān)持自己觀點(diǎn),抱持著自己的思維不改變,不能與企業(yè)思想同步,那最終也不會(huì)有什么好的結(jié)果.
 
在這里艾銻無(wú)限給已經(jīng)上班或即將上班的各個(gè)企業(yè)伙伴一些忠告:
 
1,困難時(shí)期,在為自己著想的同時(shí)多想想能為企業(yè)做點(diǎn)什么,今天你的付出,明天不一定會(huì)有收獲,但長(zhǎng)久來(lái)看,能量守恒定律,你是不會(huì)吃虧的.
 
2,在經(jīng)濟(jì)還沒(méi)復(fù)蘇的時(shí)期,企業(yè)還不太忙的時(shí)候,除了做好自己部門手頭上的工作以外,把自己的大腦開(kāi)動(dòng)起來(lái),如果你是職員,就想想假如你是這個(gè)部門經(jīng)理,你如何做可以讓部門的損失降到最低,讓收益提升到最大.如果你是經(jīng)理就想想假如你是企業(yè)的CEO,你如何做可以讓企業(yè)快速恢復(fù)狀態(tài),讓業(yè)務(wù)快速發(fā)展起來(lái),讓現(xiàn)金流可以流動(dòng)起來(lái)?
 
3,如果你處在花錢的部門,就想想如何做可以省錢,又可以為部門創(chuàng)收,如果你是創(chuàng)收的部門,就想想原來(lái)從來(lái)就沒(méi)想過(guò)的問(wèn)題,因?yàn)槟悴荒芷婆f就不能立新,不能創(chuàng)造就是在消耗.
 
4,管理團(tuán)隊(duì)間,如果不是能力問(wèn)題,不僅行動(dòng)要勤快,大腦更要勤快一些,除了思考自己部門的工作,也要思考其他部門的工作,你的團(tuán)隊(duì)可以做些什么更利于其他部門的發(fā)展,你可以做些什么更助于其他管理者達(dá)成目標(biāo),整個(gè)企業(yè)就像一臺(tái)齒輪吻合的機(jī)器,只有彼此主動(dòng)吻合才能讓這臺(tái)機(jī)器更好的運(yùn)轉(zhuǎn),這也像打群架,如果每一個(gè)人都能照顧好自己,還能為他人著想,就不會(huì)受傷,也能全身而退,有一個(gè)人需要他人照顧,就很可能有人受傷,并導(dǎo)致全隊(duì)受損.
 
5,無(wú)論什么時(shí)期,不要害怕困難,不要拒絕煩惱, 煩惱即菩提,菩提即智慧,偉大的洞見(jiàn)和上師都是來(lái)自于苦難和無(wú)數(shù)次的折磨.相信宇宙的安排,無(wú)論是事,還是人,來(lái)到你的身邊就是成就你的,所以把心安于當(dāng)下,盡一切努力做到盡善盡美.
 
接下來(lái)分享一則技術(shù)信息,以許會(huì)對(duì)你的企業(yè)有所幫助:

艾銻知識(shí) |SQL Server正確刪除Windows認(rèn)證用戶的方法

前言

在SQL Server數(shù)據(jù)庫(kù)中，有時(shí)候會(huì)建立一些Windows認(rèn)證的賬號(hào)（域賬號(hào)）,例如，我們公司習(xí)慣給開(kāi)發(fā)人員和Support同事開(kāi)通NT賬號(hào)權(quán)限，如果有離職或負(fù)責(zé)事宜變更的話，那么要如何正確的刪除這些Windows認(rèn)證賬號(hào)呢？這篇文章就是來(lái)探討一下如何正確的刪除Windows認(rèn)證賬號(hào)。如下所示：
下面這種方式，僅僅是刪除登錄名（login），然而并沒(méi)有刪除用戶（User)

1 2 3 4 5

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.
也就是說(shuō)，雖然你刪除了登錄名，但是對(duì)應(yīng)用戶數(shù)據(jù)庫(kù)或系統(tǒng)數(shù)據(jù)庫(kù)相關(guān)的User權(quán)限并沒(méi)有清理，在SQL Server中登錄名（Server Login)跟數(shù)據(jù)庫(kù)的用戶（database User）是分離開(kāi)來(lái)，但是又有關(guān)聯(lián)的。所以正確的姿勢(shì)： 在刪除登錄名（login）后，還必須去每個(gè)數(shù)據(jù)庫(kù)，刪除對(duì)應(yīng)的用戶（user). 在刪除登錄名前必須檢查，有那些作業(yè)的OWNER或數(shù)據(jù)庫(kù)的OWNER的為該Windows認(rèn)證賬號(hào)（NT賬號(hào)），否則后面就會(huì)遇到一些問(wèn)題：

1：如果刪除Windows認(rèn)證用戶前，沒(méi)有修改作業(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ù)庫(kù)的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ù)庫(kù)。在刪除該登錄名之前，請(qǐng)更改相應(yīng)數(shù)據(jù)庫(kù)的所有者。

必須修改數(shù)據(jù)庫(kù)的Owner后（一般將數(shù)據(jù)庫(kù)的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ù)庫(kù)主體在該數(shù)據(jù)庫(kù)中擁有 架構(gòu)，無(wú)法刪除。

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í)際情況來(lái)處理
USE [UserDatabase];
GO
ALTER AUTHORIZATION ON SCHEMA::[xxx] TO [dbo];
另外一種是用戶創(chuàng)建的Schema,這個(gè)根上面情況沒(méi)有差別。
所以正確的刪除登錄名,可以用腳本生成對(duì)應(yīng)的SQL（當(dāng)然也可以執(zhí)行對(duì)應(yīng)的SQL，但是這種高位操作，建議生成腳本，人工判斷后，手工執(zhí)行）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

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沒(méi)有清理的Windows 認(rèn)證用戶。此腳本可能有一些邏輯上的Bug，個(gè)人也是fix掉了一些Bug后，才發(fā)布這篇博客。如果遇到什么Bug，可以留言反饋。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127

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 )   --開(kāi)始循環(huán)每一個(gè)用戶數(shù)據(jù)庫(kù)（排除了上面相關(guān)數(shù)據(jù)庫(kù)） 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é)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值