Removing Orphan Users from All Databases on Server

,

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

Code

for 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]>

Rate

4.75 (4)

Share

Share

Rate

4.75 (4)