DBA Sensation

November 7, 2008

Fix orphaned users in SQL Server

Filed under: 3. MS SQL Server — zhefeng @ 11:05 am

Every time I restore a database, I forget how to fix the orphaned users.  So I google, trying to find the magic words that will bring me to that stored procedure that I know exists, but I just can’t remember the name of.  For those of you who don’t know, sp_change_users_login has an ‘Auto_Fix’ action that will map a user in a database to a login of the same name. Since I’m going to forget all that again, I’m posting this little snippet of Books Online.  Maybe Google will help me find it next time!

sp_change_users_login

Changes the relationship between a Microsoft® SQL Server™ login and a SQL Server user in the current database.

Syntax

sp_change_users_login [ @Action = ] action
[ , [ @UserNamePattern = ] user ]
[ , [ @LoginName = ] login ]

Arguments

[@Action =] action

Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values.

Value

Description

Auto_Fix

Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.

user must be a valid user in the current database, and login must be NULL, a zero-length string (”), or not specified.

Report

Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.

user and login must be NULL, a zero-length string (”), or not specified.

Update_One

Links the specified user in the current database to login. login must already exist. user and login must be specified.


[@UserNamePattern =] user

Is the name of a SQL Server user in the current database. user is sysname, with a default of NULL. sp_change_users_login can be used only with the security accounts of SQL Server logins and users; it cannot be used with Microsoft Windows NT® users.

[@LoginName =] login

Is the name of a SQL Server login. login is sysname, with a default of NULL.

Sample Usage:

–Check if there is any orphaned user in the database
use [databasename];
exec sp_change_users_login @action=’report’;

–use auto fix to fix the orphaned user
use [databasename];
exec sp_change_users_login ‘auto_fix’,’username’;

–link the orphaned user to existing login
use [databasename];
exec sp_change_users_login ‘update_one’, ‘username’,’userlogin’;

or

sp_change_users_login @Action=’update_one’, @UserNamePattern='<database_user>’, @LoginName='<login_name>’;

Advertisements

1 Comment »

  1. books online are great, wether they are e-books or conventional hardbound and paperback books :*’

    Comment by Nursery Decoration — November 24, 2010 @ 1:27 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: