Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Removing Orphan Users from All Databases on Server

By Gregory Larsen,

How many

Removing Orphan Users from All databases on 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 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.

 

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.  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.

 

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')

 

Removing Orphan Users

 

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.

 

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. 

 

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.

 

Conclusion

 

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.

 

Code for usp_remove_orphan_users

create proc usp_remove_orphan_users as

 

-- Written by: Gregory A. Larsen 

-- Script to modify database owner, and remove all users that

-- are not mapped to logins.

 

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 (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' and u.name <> ''system_function_schema'')

having count(*) > 0'

 

-- 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)

 

-- Section 5: Process through each database and remove orphan users

select @cnt=count(*) from ##DBNAMES

While @CNT > 0

begin

 

-- 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 u.name = ''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

  begin

    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 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)

 

 

-- Are there orphans

  select @cnt = count(*) from ##orphans

 

  WHILE @cnt > 0

  BEGIN

 

-- 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(*) 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

 

 

 

 

Total article views: 15066 | Views in the last 30 days: 13
 
Related Articles
BLOG

Finding orphaned database users

Database users can become orphaned for a few reasons. For example, a database restore to another ins...

BLOG

Finding orphaned database users

Database users can become orphaned for a few reasons. For example, a database restore to another ins...

FORUM

Alter Database DBNAME Set Recovery Full. Gives error.

Alter Database DBNAME Set Recovery Full. Gives error.

FORUM

query(dbname,size,recoverymodel)

query(dbname,size,recoverymodel)

SCRIPT

List orphaned users from all databases

The script returns all orphaned database users across a SQL Server.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones