SQL Server正确删除Windows认证用户的方法

USE [master]GODROP LOGIN [xxx/xxxx]GO

DECLARE @login_name sysname;SET @login_name='GFG1/chenzhenh'SELECT d.name AS database_name,owner_sid AS owner_sid ,l.name AS database_ownerFROM sys.databases dLEFT 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 dLEFT 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 jINNER 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_nameORDER BY j.nameDECLARE @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_cmdFROM msdb.dbo.sysjobs jINNER 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_nameORDER BY j.nameSELECT 'USE [master]GODROP LOGIN ' + QUOTENAME(@login_name) + 'GO' AS drop_login_user;

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 NULLDROP TABLE dbo.#databases;CREATE TABLE #databases(database_id INT,database_name sysname)INSERT INTO #databasesSELECT database_id ,nameFROM sys.databasesWHERE 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= 1BEGINSELECT TOP 1 @database_name= database_name FROM #databasesORDER BY database_id;IF @@ROWCOUNT =0 BREAK;SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)SELECT @cmdText += 'INSERT INTO #removed_userSELECT name FROM sys.sysusersWHERE 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 @cmdTextSELECT @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_MODIFIEDFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_idINNER JOIN sys.syslogins l ON l.sid = j.owner_sidINNER JOIN #removed_user r ON l.name = r.usernameORDER 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_descFROM sys.databases dINNER JOIN sys.syslogins l ON d.owner_sid = l.sidINNER JOIN #removed_user r ON r.username = l.nameSET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)SET @cmdText += 'SELECT * FROM sys.schemas sINNER 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 sINNER 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_cmdFROM INFORMATION_SCHEMA.SCHEMATA sINNER 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_cmdFROM #removed_user;TRUNCATE TABLE #removed_user;DELETE FROM #databases WHERE database_name=@database_name;ENDDROP TABLE #databases;DROP TABLE #removed_user;