Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 4, 2003

Removing Orphan Users from All databases on SQL Server

By Gregory A. Larsen


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.

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_revokedbaccess 'USERX'

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

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

See all articles by columnist Gregory A. Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date