Sql Server中孤立的SQL用户查找和删除-阿里云开发者社区

开发者社区> 郭生生> 正文

Sql Server中孤立的SQL用户查找和删除

简介: 问题 最近公司很多数据库在上云,也有一部分在下云。这期间出现了很多问题,其中一个比较恶心的问题就是“孤立用户”。当数据库备份还原以后用以前的用户发现不能登录。一开始以为是登录账号没有创建,然后重新创建登录账号,然后再授权给数据库,此时又出错,说用户已经存在。
+关注继续查看

问题

最近公司很多数据库在上云,也有一部分在下云。这期间出现了很多问题,其中一个比较恶心的问题就是“孤立用户”。当数据库备份还原以后用以前的用户发现不能登录。一开始以为是登录账号没有创建,然后重新创建登录账号,然后再授权给数据库,此时又出错,说用户已经存在。我这才引起注意,开始搜索这个资料,原来这就是因为臭名昭著的孤立用户引起的。

什么是孤立的SQL用户?

那么孤立用户又是什么东西?一个孤立用户就是一个数据库用户,同时没有SQL Server的登录权限。

在实际生产中有很多产生孤立用户的原因,最为主要的方式就是备份还原到不同的服务器实例时。还原数据库的时候回将数据库和用户一同还原到新的数据库上,但是服务器的登录账户却没有一同还原(也不需要这么做)。如果数据库相同服务器那么皆大欢喜,因为用户没有变。如果是不同服务器,此时登录账户中没有了数据库用的名称,即使你创建了相同的名称但是他们的ID也是不同的导致他们不能关联起来。此时就导致了数据库的用户被孤立,也不能访问。此时我们需要做的就是找出孤立用户修改或者删除重建。

下图中是外国网友列出可能的产生孤立用户的原因(很详细):
1

查找数据库中的孤立用户

我打算写一个脚本实现两个主要目的,一是找到一个实例内所有的孤立用户;第二是按需求删除这些用户。从网上找了不少脚本和博客发现都不能实现。所以我自己写了一个亲测可用。这个脚本的麻烦在于当删除用户时,这个用户拥有自己的对象,并且不能drop掉,只能先删除这个对象或者改变对象和用户之间的关系。在下面的例子中所有的用户拥有一个架构,脚本必须去处理这个用户的架构。用脚本实现把孤立用户存储到一个临时表内,然后根据临时表的用户信息删除架构和用户。

查找孤立用户的脚本

Use master
Go
Create Table #Orphans 
 (
 RowID int not null primary key identity(1,1) ,
 TDBName varchar (100),
 UserName varchar (100),
 UserSid varbinary(85)
 )
SET NOCOUNT ON 
 DECLARE @DBName sysname, @Qry nvarchar(4000)
 SET @Qry = ''
 SET @DBName = ''
 WHILE @DBName IS NOT NULL
 BEGIN
 SET @DBName = 
 (
 SELECT MIN(name) 
 FROM master..sysdatabases 
 WHERE
 /** to exclude named databases add them to the Not In clause **/
 name NOT IN 
 (
 'model', 'msdb', 
 'distribution'
 ) And 
 DATABASEPROPERTY(name, 'IsOffline') = 0 
 AND DATABASEPROPERTY(name, 'IsSuspect') = 0 
 AND name > @DBName
 )
 IF @DBName IS NULL BREAK
 
 Set @Qry = 'select ''' + @DBName + ''' as DBName, name AS UserName, 
 sid AS UserSID from [' + @DBName + ']..sysusers 
 where issqluser = 1 and (sid is not null and sid <> 0x0) 
 and suser_sname(sid) is null order by name'
 Insert into #Orphans Exec (@Qry)
 
 End
Select * from #Orphans

如何删除用户(这部分切记酌情使用,先与使用人员或者DBA确认孤立用户已经用了再进行删除。并确认其架构对象不收影响)

接着上面的脚本,我们把用户从临时表中取出来进行循环处理。

Declare @SQL as varchar (200)
Declare @DDBName varchar (100)
Declare @Orphanname varchar (100)
Declare @DBSysSchema varchar (100)
Declare @From int
Declare @To int
Select @From = 0, @To = @@ROWCOUNT 
from #Orphans
--Print @From
--Print @To
While @From <= @To
 Begin
 Set @From = @From + 1
 
 Select @DDBName = TDBName, @Orphanname = UserName from #Orphans
 Where RowID = @From
 
 Set @DBSysSchema = '[' + @DDBName + ']' + '.[sys].[schemas]'
 print @DBsysSchema
 Print @DDBname
 Print @Orphanname
 set @SQL = 'If Exists (Select * from ' + @DBSysSchema 
 + ' where name = ''' + @Orphanname + ''')
 Begin
 Use ' + @DDBName 
 + ' Drop Schema [' + @Orphanname + ']
 End'
 print @SQL
 Exec (@SQL)
 
 Begin Try
 Set @SQL = 'Use ' + @DDBName 
 + ' Drop User [' + @Orphanname + ']'
 Exec (@SQL)
 End Try
 Begin Catch
 End Catch
 
 End
 
Drop table #Orphans

脚本中需要注意的事项

首先如果有些数据库的孤立用户不想处理那么在插入临时表时可以提前通过NOT IN语句排除数据库。在删除的孤立用户同时,也会删除孤立用户拥有的架构。需要引起注意。这个脚本将不会检查其他可能被用户拥有的对象。我已经在sql server 2005/2008/2014上进行了测试,请大家知悉。

总结

在上云或者数据库迁移的时候一旦发现这类错误往往会出现一些难以预料的问题,我建议。可以先用查询的语句进行查询看看具体哪些用户是孤立用户,哪些需要区别对待,在进行其他处理。脚本是大大减少了自己挨个查询的时间,但是也提高了风险,请大家酌情使用。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server中收缩、清空和删除数据库文件及日志文件
一、简单方法 –SQL Server收缩方法 1、右键数据库→属性→选项→故障还原模型→设为简单→确定; 2、右键数据库→所有任务→收缩数据库→确定; 3、右键数据库→属性→选项→故障还原模型→设为大容量日志记录→确定。
4532 0
Tengine 如何查找 server 块
本文的目标读者是 Tengine/Nginx 研发或者运维同学,如果自己对这块逻辑非常清楚,那可以略过,如果在配置或者开发 Tengine/Nginx 过程中,有遇到如【请求到达匹配的是哪个 server 块?】等等问题,本文或许能解答你多年的疑惑。
848 0
SQL Server 查找全部重复记录
Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)&gt;1)
770 0
SQL Server 2000删除表中的重复记录
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/chinahuyong/article/details/3551849  在SQL Server中除了对拥有十几条记录的表进行人工删除外,实现删除重复记录一般都是写一段代码,用游标的方法一行一行检查,删除重复的记录。
644 0
SQLServer删除登录记录用户名和密码
介绍: 作为一名开发人员都会知道我们做的项目都要用到数据库,数据库都需要账号和密码,然而问题来了,做的东西多了那些没用的账号和密码还在哪里纠缠着我们。所有我们不能忍了删除掉他。 网上很多都是2008的是删除方案,知道我看到了这篇:http://stackoverflow.
1650 0
+关注
58
文章
24
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载