Sql Server中孤立的SQL用户查找和删除
问题
最近公司很多数据库在上云,也有一部分在下云。这期间出现了很多问题,其中一个比较恶心的问题就是“孤立用户”。当数据库备份还原以后用以前的用户发现不能登录。一开始以为是登录账号没有创建,然后重新创建登录账号,然后再授权给数据库,此时又出错,说用户已经存在。我这才引起注意,开始搜索这个资料,原来这就是因为臭名昭著的孤立用户引起的。
什么是孤立的SQL用户?
那么孤立用户又是什么东西?一个孤立用户就是一个数据库用户,同时没有SQL Server的登录权限。
在实际生产中有很多产生孤立用户的原因,最为主要的方式就是备份还原到不同的服务器实例时。还原数据库的时候回将数据库和用户一同还原到新的数据库上,但是服务器的登录账户却没有一同还原(也不需要这么做)。如果数据库相同服务器那么皆大欢喜,因为用户没有变。如果是不同服务器,此时登录账户中没有了数据库用的名称,即使你创建了相同的名称但是他们的ID也是不同的导致他们不能关联起来。此时就导致了数据库的用户被孤立,也不能访问。此时我们需要做的就是找出孤立用户修改或者删除重建。
下图中是外国网友列出可能的产生孤立用户的原因(很详细):
查找数据库中的孤立用户
我打算写一个脚本实现两个主要目的,一是找到一个实例内所有的孤立用户;第二是按需求删除这些用户。从网上找了不少脚本和博客发现都不能实现。所以我自己写了一个亲测可用。这个脚本的麻烦在于当删除用户时,这个用户拥有自己的对象,并且不能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上进行了测试,请大家知悉。
总结
在上云或者数据库迁移的时候一旦发现这类错误往往会出现一些难以预料的问题,我建议。可以先用查询的语句进行查询看看具体哪些用户是孤立用户,哪些需要区别对待,在进行其他处理。脚本是大大减少了自己挨个查询的时间,但是也提高了风险,请大家酌情使用。
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
今日头条在消息服务平台和容灾体系建设方面的实践与思考
本篇文章整理自今日头条的沈辉在 RocketMQ 开发者沙龙中的演讲,主要和大家分享一下,RocketMQ 在微服务架构下的实践和容灾体系建设。沈辉是今日头条的架构师,主要负责 RocketMQ 在头条的落地以及架构设计,参与消息系统的时间大概一年左右。 以下是本次分享的议题: 头条的业务背景 为什么选择 RocketMQ RocketMQ 在头条的落地实践 头条的容灾系统建设 业务背景 今日头条的服务大量使用微服务,容器数目巨大,业务线繁多, Topic 的数量也非常多。另外,使用的语言比较繁杂,包括 Python,Go, C++, Java, JS 等,对于基础组件的接入,维护 SDK 的成本很高。 引入 RocketMQ 之前采用的消息队列是 NSQ 和 kafka , NSQ 是纯内存的消息队列,缺少消息的持久性,不落盘直接写到 Go
- 下一篇
阿里云和微软共同开源的 OAM 对 Kubernetes 开发人员意味着什么?
上周,微软和阿里巴巴共同推出了开放应用模型( OAM ),用于定义部署在任何地方的应用模型的一种规范。 Rudr 是 Microsoft 基于 Kubernetes 环境的 OAM 标准实现。 我用了一个周末来了解 OAM 试图解决的问题,为此我还以 Rudr 为基础重构了一些我喜欢的基础微服务的应用程序。本文和以下教程将帮助普通的 Kubernetes 用户了解 OAM 背后的动机。 众所周知, Kubernetes 是一个复杂的平台,包含许多活动组件。在编排和部署简单的两层 Web 应用程序时,需要涉及到创建 Storage Classes , PVC , PV , Secret , ConfigMap , Service , Deployment 和 Ingress 。在实际生产部署中还需要健全的日志收集,监控告警,安全性,高可
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- Mario游戏-低调大师作品
- Docker安装Oracle12C,快速搭建Oracle学习环境
- Red5直播服务器,属于Java语言的直播服务器
- CentOS7,CentOS8安装Elasticsearch6.8.6
- CentOS8编译安装MySQL8.0.19
- SpringBoot2整合MyBatis,连接MySql数据库做增删改查操作
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- CentOS6,CentOS7官方镜像安装Oracle11G
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- SpringBoot2更换Tomcat为Jetty,小型站点的福音