Removing Orphan Users from All databases on Server
<![if !supportEmptyParas]> <![endif]>
Have you heard about orphan
SQL Server users? If not, an orphan
user is a user in a SQL Server database that is not associated with a SQL
Server login. Orphan users are created
when a database backup from one server is restored on another server. There are a number of articles that have
been written, and lots of topics on discussion boards about reconnecting orphan
users with logins, but few, if any, regarding removing orphan users. Therefore this article deals with how to
identify which databases have orphan users, and how to remove the identified
orphan users.
<![if !supportEmptyParas]> <![endif]>
Just because you are moving
a database from one server to another does not mean you also want to move all
the users associated with the database.
In some cases you may not want to move any, and in other cases you might
want to only move a few. For this
discussion lets say you have already identified and reconnected all the orphan
users you plan to keep. For all the other orphan users you did not reconnect, I
will show you how to identify and remove.
<![if !supportEmptyParas]> <![endif]>
Identifying Orphan Users
<![if !supportEmptyParas]> <![endif]>
SQL Server provides a SP to
help you identify orphan users that were originally associated with SQL Server
Authenticated logins. The SP is called
sp_change_users_login. But SQL Server
does not provide a mechanism to identify orphan users that where originally
associated with Windows authenticated users or groups. The key to removing users is being able to
identify them. The following code can
be run against any database to identify all the orphan users regardless of
whether they are associated with a SQL Server or Windows authenticated user and/or a Windows group.
<![if !supportEmptyParas]> <![endif]>
select u.name from master..syslogins l right join sysusers u on l.sid = u.sid where l.sid is null and issqlrole <> 1 and isapprole <> 1 and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and u.name <> 'system_function_schema') |
<![if !supportEmptyParas]> <![endif]>
Removing Orphan Users
<![if !supportEmptyParas]> <![endif]>
Once you have identified
orphan users it is extremely simple to remove them. You remove them by using
the sp_revokeuser SP. Here is an
example that removes the database users ‘USERX’, from the current database in
use.
<![if !supportEmptyParas]> <![endif]>
exec sp_revokelogin ‘USERX’ |
It seems fairly simple to do
this for a few users and databases. But
if you have a large number of orphan users and databases, I’m sure you would
not like to do this by hand. Or at
least I didn’t want to do this manually.
Because I like to automate repetitive manual tasks, I developed a stored
procedure (SP) named “usp_remove_orphan_users” to accomplish identifying and
removing orphan users. The code for this SP can be found at the end of this
article.
<![if !supportEmptyParas]> <![endif]>
This SP first determines
which databases have orphan users. For
each database that has orphans it removes them one at a time. If an orphan user
is the database owner then the “sp_changedbowner” SP is used change the owner
to “SA” before the orphan user is removed.
The SP does not really remove the users, or change the database owners,
but instead it just generates the code to remove the users. This allows you to review the code and
determine if you want remove all users, or only a select set of orphan users.
<![if !supportEmptyParas]> <![endif]>
Conclusion
<![if !supportEmptyParas]> <![endif]>
You can leave the orphan
database users in a database if you want.
Although they are excess baggage, that comes along with a database
restore. Also they provide a small
security risk if some newly defined login is unintentionally associated with an
orphan user allowing the new login to gain unauthorized database access. It is best to remove orphan users not needed
to provide a clean, uncluttered database environment. This script allows for an easy method to identify and remove
unneeded orphan users. Therefore, this
SP can be a valuable tool, to be used as part of your database restore process,
should you desire to remove orphan users.
<![if !supportEmptyParas]> <![endif]>
Codefor usp_remove_orphan_users
create proc usp_remove_orphan_users as <![if !supportEmptyParas]> <![endif]> -- Written by: Gregory A. Larsen -- Script to modify database owner, and remove all users that -- are not mapped to logins. <![if !supportEmptyParas]> <![endif]> set nocount on <![if !supportEmptyParas]> <![endif]> -- Section 1: Create temporary table to hold databases to process <![if !supportEmptyParas]> <![endif]> -- drop table if it already exists if (select object_id('tempdb..##dbnames')) is not null drop table ##dbnames <![if !supportEmptyParas]> <![endif]> -- Create table to hold databases to process create table ##dbnames (dbname varchar(128)) <![if !supportEmptyParas]> <![endif]> -- Section 2: Determine what databases have orphan users exec master.dbo.sp_MSforeachdb 'insert into ##dbnames select ''?'' from master..syslogins l right join ?..sysusers u on l.sid = u.sid where l.sid is null and issqlrole <> 1 and isapprole <> 1 and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' and u.name <> ''system_function_schema'') having count(*) > 0' <![if !supportEmptyParas]> <![endif]> -- Section 3: Create local variables needed declare @CNT int declare @name char(128) declare @sid varbinary(85) declare @cmd nchar(4000) declare @c int declare @hexnum char(100) declare @db varchar(100) <![if !supportEmptyParas]> <![endif]> -- Section 5: Process through each database and remove orphan users select @cnt=count(*) from ##DBNAMES While @CNT > 0 begin <![if !supportEmptyParas]> <![endif]> -- get the name of the top database select top 1 @db=dbname from ##DBNAMES <![if !supportEmptyParas]> <![endif]> -- delete top database delete from ##DBNAMES where dbname = @db <![if !supportEmptyParas]> <![endif]> -- Build and execute command to determine if DBO is not mapped to login set @cmd = 'select @cnt = count(*) from master..syslogins l right join ' + rtrim(@db) + '..sysusers u on l.sid = u.sid' + ' where l.sid is null and u.name = ''DBO''' exec sp_executesql @cmd,N'@cnt int out',@cnt out <![if !supportEmptyParas]> <![endif]> -- if DB is not mapped to login that exists map DBO to SA if @cnt = 1 begin print 'exec ' + @db + '..sp_changedbowner ''SA''' -- exec sp_changedbowner 'SA' end -- if @cnt = 1 <![if !supportEmptyParas]> <![endif]> <![if !supportEmptyParas]> <![endif]> -- drop table if it already exists if (select object_id('tempdb..##orphans')) is not null drop table ##orphans <![if !supportEmptyParas]> <![endif]> -- Create table to hold orphan users create table ##orphans (orphan varchar(128)) <![if !supportEmptyParas]> <![endif]> -- Build and execute command to get list of all orphan users (Windows and SQL Server) -- for current database being processed set @cmd = 'insert into ##orphans select u.name from master..syslogins l right join ' + rtrim(@db) + '..sysusers u on l.sid = u.sid ' + 'where l.sid is null and issqlrole <> 1 and isapprole <> 1 ' + 'and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' ' + 'and u.name <> ''system_function_schema'')' exec (@cmd) <![if !supportEmptyParas]> <![endif]> <![if !supportEmptyParas]> <![endif]> -- Are there orphans select @cnt = count(*) from ##orphans
WHILE @cnt > 0 BEGIN
-- get top orphan select top 1 @name= orphan from ##orphans <![if !supportEmptyParas]> <![endif]> -- delete top orphan delete from ##orphans where orphan = @name <![if !supportEmptyParas]> <![endif]> -- Build command to drop user from database. set @cmd = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + '''' print @cmd --exec (@cmd) <![if !supportEmptyParas]> <![endif]>
-- are there orphans left select @cnt = count(*) from ##orphans end -- WHILE @cnt > 0 <![if !supportEmptyParas]> <![endif]> <![if !supportEmptyParas]> <![endif]> -- are the still databases to process select @cnt=count(*) from ##dbnames <![if !supportEmptyParas]> <![endif]> end -- while @cnt > 0 <![if !supportEmptyParas]> <![endif]> -- Remove temporary tables drop table ##dbnames, ##orphans <![if !supportEmptyParas]> <![endif]> |