Wednesday, February 8, 2012

Delete Orphan users from databases on a server

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.

Just because you are moving a database from one server to another does not mean you also want to move all of the users associated with the database. In some cases, you may not want to move any, and in other cases, you might want to move only a few. For this discussion, let's say you have already identified and reconnected all of the orphan users you plan to keep. For all of the other orphan users you did not reconnect, I will show you how to identify and remove them.

Identifying Orphan Users
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. However, 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 of the orphan users regardless of whether they are associated with a SQL Server or Windows authenticated user and/or a Windows group.

  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 not in ('INFORMATION_SCHEMA''guest', 'system_function_schema', 'sys', 'dbo''SA')

Removing Orphan Users

Once you have identified orphan users it is extremely simple to remove them. You remove them by using my script. Here is an example that removes the database users 'Orphan', from the current database in use.

exec sp_revokedbaccess 'Orphan'. It seems fairly simple to do this for a few users and databases. However, if you have a large number of orphan users and databases, I'm sure you would not like to do this by hand. At least, I didn't want to do this manually. Because I like to automate repetitive manual tasks, I developed a script to accomplish identifying and removing orphan users.

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 to 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 to remove all users, or only a select set of orphan users.


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. In addition, 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 un-necessary orphan users to provide a clean, uncluttered database environment. This script provides an easy method to identify and remove unneeded orphan users. Therefore, this script can be a valuable tool, to be used as part of your database restore process, should you desire to remove orphan users. Download my script here.

set nocount on

-- Section 1: Create temporary table to hold databases to process 

-- drop table if it already exists
if (select object_id('tempdb..##dbnames')) is not null
  drop table ##dbnames

-- Create table to hold databases to process
create table ##dbnames (dbname varchar(128))

-- 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 not in (''INFORMATION_SCHEMA'', ''guest'', ''system_function_schema'', ''sys'', ''dbo'', ''SA'') 
having count(1) > 0'

-- Section 3: Create local variables needed
declare @CNT int
          , @name char(128)
          , @sid  varbinary(85)
          , @cmd nchar(4000)
          , @c int
          , @hexnum char(100)
          , @db varchar(100) 

-- Section 5: Process through each database and remove orphan users
select @cnt=count(*) from ##DBNAMES
While @CNT > 0

-- get the name of the top database
  select top 1 @db=dbname from ##DBNAMES

-- delete top database 
  delete from ##DBNAMES where dbname = @db

-- 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 = ''DBO'''
  exec sp_executesql @cmd,N'@cnt int out',@cnt out

-- if DB is not mapped to login that exists map DBO to SA
  if @cnt = 1
    print 'exec ' + @db + '..sp_changedbowner ''SA'''  
    -- exec sp_changedbowner 'SA'
  end -- if @cnt = 1

-- drop table if it already exists
if (select object_id('tempdb..##orphans')) is not null
  drop table ##orphans

-- Create table to hold orphan users
create table ##orphans (orphan varchar(128))

-- 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 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 ( not in (''INFORMATION_SCHEMA'', ''guest'', ''system_function_schema'', ''sys'',  ''dbo'', ''SA'')'
   exec (@cmd)

-- Are there orphans
  select @cnt = count(1) from ##orphans
  WHILE @cnt > 0 
-- get top orphan
  select top 1 @name= orphan from ##orphans

-- delete top orphan
  delete from ##orphans where orphan = @name

-- Build command to drop user from database.
    set @cmd = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + ''''
    print @cmd
    --exec (@cmd)

-- are there orphans left
    select @cnt = count(1) from ##orphans  
  end --  WHILE @cnt > 0

-- are the still databases to process
select @cnt=count(*) from ##dbnames

end -- while @cnt > 0

-- Remove temporary tables
drop table ##dbnames, ##orphans

No comments:

Post a Comment