belle’s sql musings

Follow up on Issue: Server principal is not able to access the database under the current security context

Posted in Issues/Troubleshooting, Security by belle on June 13, 2008

SQL Server operates on the concepts of Logins and DB Users.

A login (server level) can map to a single database user. The name of the db user can be the same, or can be different. A login is mapped to a user via an SID (security ID)

Often, when databases are moved around, this login-user mapping gets lost because a different login in a different server or instance will have a different SID. And this causes grief not just to users but also to DBAs.

Couple ways to fix this:

 

1. sp_change_users_login

This is marked for deprecation, but you can still use this.

3 options for sp_change_users_login:

Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present

Report, it lists the orphaned users and their security identifiers (SID)

Update_One, it links the specified database user to an existing SQL Server login

EXEC sp_change_users_login @Action=’Report’

EXEC sp_change_users_login  @Action =  ‘Update_One’ 
   UserNamePattern = ‘dbusername’  
    @LoginName = ‘loginname’ 

When there are a lot of users, can use a cursor.

 

2. ALTER LOGIN – better way or recommended way

ALTER USER dbusername WITH LOGIN ‘loginname’

Advertisements

4 Responses

Subscribe to comments with RSS.

  1. […] Up Post: Follow up on Issue: SQL Server Principal Is Not Able to Access the Database Under the Current Securi… « Executing Batches Multiple Times (SQL Server 2005 only) Follow up on Issue: […]

  2. […] Issue: Server principal is not able to access the database under the current security context […]

  3. […] Issue: Server principal is not able to access the database under the current security context […]

  4. […] http://www.sqlservercentral.com/Forums/Topic362018-359-1.aspx Follow Up Post: Follow up on Issue: SQL Server Principal Is Not Able to Access the Database Under the Current Securi… […]


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

%d bloggers like this: