您当前的位置:首页 > 电脑百科 > 数据库 > SQL Server

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

时间:2019-11-04 10:37:17  来源:  作者:

问题

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

什么是孤立的SQL用户?

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

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

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

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

 

查找数据库中的孤立用户

我打算写一个脚本实现两个主要目的,一是找到一个实例内所有的孤立用户;第二是按需求删除这些用户。从网上找了不少脚本和博客发现都不能实现。所以我自己写了一个亲测可用。这个脚本的麻烦在于当删除用户时,这个用户拥有自己的对象,并且不能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上进行了测试,请大家知悉。

总结

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



Tags:Sql Server   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除,谢谢。
▌相关推荐
要想看懂存储过程的意思,就必须要多看存储过程和实例,多百度查询前人的积累的说明,只有在不断地实践才能形成自己的知识,首先,先看到一些基础的语法,慢慢就看懂存储过程说表达的意...【详细内容】
2021-05-14  Tags: Sql Server  点击:(58)  评论:(0)  加入收藏
在SQL server应用开发过程中,可能会遇到ntext、text 和 image 三种数据类型,这三种数据类型是做什么的,有什么区别呢?下面表格从存储类型、存储数据的最大长度和存储大小三个维...【详细内容】
2021-04-26  Tags: Sql Server  点击:(85)  评论:(0)  加入收藏
在数据库使用过程中,为了数据的安全,除了通过服务器防火墙设置访问权限外,还可以通过在数据库中通过创建触发器的方式来控制用户或特定IP的登录权限。USE master;创建数据库登...【详细内容】
2021-04-23  Tags: Sql Server  点击:(85)  评论:(0)  加入收藏
文章来源:https://mp.weixin.qq.com/s/pEXio0MNoi1k0w9XgYECNw作者:廖学强 1. sqlserver查看实例级别的信息,使用SERVERPROPERTY函数select SERVERPROPERTY (&#39;propertyname...【详细内容】
2021-01-12  Tags: Sql Server  点击:(108)  评论:(0)  加入收藏
因工作需要,长期用到Microsoft SQL Server,很多人还不知道怎么安装和配置这个Microsoft SQL Server,今天我就从头到尾来安装一遍,用截图软件截下图来打上文字或者画上箭头来标示...【详细内容】
2020-09-15  Tags: Sql Server  点击:(51)  评论:(0)  加入收藏
Legal informations for Sage, depending on each country. Address. 修改数据库环境的排序规则登录数据库后,查看当前安装数据库默认排序规则的两种方式方式一、使用SQL S...【详细内容】
2020-08-10  Tags: Sql Server  点击:(119)  评论:(0)  加入收藏
因为发展需求,公司使用了OSI 公司的 PI 系统对于PI系统没有深入的了解,只是根据供应商的资料学习了一些基本内容,因为这个pi系统是从底层DCS 收集数据,然后再在第三方客户端程序...【详细内容】
2020-08-04  Tags: Sql Server  点击:(46)  评论:(0)  加入收藏
本文描述了SQL Server中存储过程和用户定义函数之间的区别。存储过程(Stored Procedure)存储过程只不过是您事先保存好的SQL代码而已,您可以反复使用该代码。 如果您一遍又一...【详细内容】
2020-06-26  Tags: Sql Server  点击:(65)  评论:(0)  加入收藏
在本文中,我将解释一些通用的SQL查询。我认为每个开发人员都应该了解这些查询。这些查询与SQL的任何特定主题都无关。但是了解这些查询可以解决一些复杂的任务,并且可以在许多...【详细内容】
2020-06-19  Tags: Sql Server  点击:(36)  评论:(0)  加入收藏
一、应用场景1.控制A账号上班时间不允许从某些段的IP进行登录数据库。2.控制B账号登录数据库只能使用某些程序进行登录。3.记录所有账号的登录来源。(根据需要可以不用)注意...【详细内容】
2020-06-09  Tags: Sql Server  点击:(35)  评论:(0)  加入收藏
▌简易百科推荐
要想看懂存储过程的意思,就必须要多看存储过程和实例,多百度查询前人的积累的说明,只有在不断地实践才能形成自己的知识,首先,先看到一些基础的语法,慢慢就看懂存储过程说表达的意...【详细内容】
2021-05-14  ITLIFEMAN    Tags:sql server   点击:(58)  评论:(0)  加入收藏
在SQL server应用开发过程中,可能会遇到ntext、text 和 image 三种数据类型,这三种数据类型是做什么的,有什么区别呢?下面表格从存储类型、存储数据的最大长度和存储大小三个维...【详细内容】
2021-04-26  数据超酷  今日头条  Tags:SQL Server   点击:(85)  评论:(0)  加入收藏
在数据库使用过程中,为了数据的安全,除了通过服务器防火墙设置访问权限外,还可以通过在数据库中通过创建触发器的方式来控制用户或特定IP的登录权限。USE master;创建数据库登...【详细内容】
2021-04-23  数据超酷  今日头条  Tags:SQL Server   点击:(85)  评论:(0)  加入收藏
今天给大家梳理Apply关键字用法,希望对大家能有所帮助!1、概念介绍APPLy关键字是SQLServer版本中开始提供的一个系统关键字。APPLY的功能同联接很类似,APPLY运算分左右两个部...【详细内容】
2021-04-16    数据库技术分享社区  Tags:Apply关键字   点击:(82)  评论:(0)  加入收藏
文章来源:https://mp.weixin.qq.com/s/pEXio0MNoi1k0w9XgYECNw作者:廖学强 1. sqlserver查看实例级别的信息,使用SERVERPROPERTY函数select SERVERPROPERTY (&#39;propertyname...【详细内容】
2021-01-12      Tags:SQL Server   点击:(108)  评论:(0)  加入收藏
作为程序员来说,与数据库打交道是十分频繁的分页查询是一个开发者必须掌握的基本知识点,目前整理了下面三种SQLServer分页查询语句的写法,仅供参考。一、Top Not IN 方式(查询...【详细内容】
2020-12-29      Tags:分页查询   点击:(80)  评论:(0)  加入收藏
因工作需要,长期用到Microsoft SQL Server,很多人还不知道怎么安装和配置这个Microsoft SQL Server,今天我就从头到尾来安装一遍,用截图软件截下图来打上文字或者画上箭头来标示...【详细内容】
2020-09-15      Tags:SQL Server2000   点击:(51)  评论:(0)  加入收藏
Legal informations for Sage, depending on each country. Address. 修改数据库环境的排序规则登录数据库后,查看当前安装数据库默认排序规则的两种方式方式一、使用SQL S...【详细内容】
2020-08-10      Tags:SQL Server   点击:(119)  评论:(0)  加入收藏
写SQL语句的时候我们往往关注的是SQL的执行结果,但是是否真的关注了SQL的执行效率,是否注意了SQL的写法规范?以下的干货分享是在实际开发过程中总结的,希望对大家有所帮助!1....【详细内容】
2020-08-07      Tags:SQL   点击:(41)  评论:(0)  加入收藏
因为发展需求,公司使用了OSI 公司的 PI 系统对于PI系统没有深入的了解,只是根据供应商的资料学习了一些基本内容,因为这个pi系统是从底层DCS 收集数据,然后再在第三方客户端程序...【详细内容】
2020-08-04      Tags:SQL SERVER   点击:(46)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条