SQL Server正确删除Windows认证用户的方法
作者:潇湘隐者 发布时间:2024-01-25 23:06:15
前言
在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;
总结
来源:https://www.cnblogs.com/kerrycode/p/11563641.html
猜你喜欢
- 大多数网站维护都采用“多人协作,共同管理”方式。某个人负责一个(或者多个)栏目,他只能对他负责的栏目进
- 相关代码如下: 1. 创建sequence: 代码如下:CREATE SEQUENCE SEQU_DATA_DATAINFO IN
- 在使用操作XML文件时,我们可以使用Load方法直接加载xml文件即可,在ie和ff下通用。但是是XML字符串,则在两种浏览器下就会有所不同
- 1、MFCC概述在语音识别(Speech Recognition)和话者识别(Speaker Recognition)方面,最常用到的语音特
- Unittest1.环境Unittest为Python内嵌的测试框架,不需要特殊配置,仅需要在File---settings---Tools
- 目录前言环境依赖代码前言本文主要分享一个可以将图片或者视频模糊化的工具代码。技术路线主要是使用ffmpeg滤镜。环境依赖ffmpeg环境部署
- 在收发快递填写地址的时候,我们会经常手动输入地址让程序智能识别,标准的地址比如,xx省xx市xx县/区xx路xx号,不过有时候也可以简单写:
- 这是今天在温习lambda表达式的时候想到的问题,众所周知C系列语言中的 三元运算符(?:)是一个非常好用的语句,关于C中的三元运算符表达式
- 代码如下:<?php$a;$b = false;$c = '';$d = 0;$e = null;$f = array
- 推荐使用 Homebrew 来安装第三方工具。自己安装的python散落在电脑各处,删除起来比较麻烦。今天在此记录一下删除的过程(本人以Py
- 本文实例讲述了Python使用try except处理程序异常的三种常用方法。分享给大家供大家参考,具体如下:如果你在写python程序时遇
- 我最近花了一些时间在探索CPython,并且我想要在这里分享我的一些冒险经历。Allison Kaptur的excellent guide
- 首先给出展示结果,大体就是检测工业板子是否出现。采取检测的方法比较简单,用的OpenCV的模板检测。大体思路opencv读取视频将视频分割为
- 一、TensorFlow变量管理1. TensorFLow还提供了tf.get_variable函数来创建或者获取变量,tf.variabl
- 1.goroutine-看一个需求需求:要求统计1-900000000的数字中,那些是素数?分析:传统方法,就是使用一个循环,循环的判断各个
- 经常遇到百度网盘的压缩文件加密了,今天我们就破解它!实现思路上篇文章给大家介绍了爆破密码的思路,感兴趣的朋友可以了解下。其实都大同小异:无非
- 1. 集合 set 简介集合的最大特征是其每个元素都是唯一的,它可以删除、可以增加、也可以通过增删实现替换元素,但是它是没有下标的,你无法通
- 在sql语句中,如果查找某个文本字段值为空的可以用select * from 表 where 字段=''但是如果
- <%@ language=vbscript codepage=65001%> <
- 本文实例讲述了Python实现的列表排序、反转操作。分享给大家供大家参考,具体如下:排序:使用sorted方法和列表的sort方法:sort