Monday, April 12, 2010

Script for fixing orphan users

As a follow up to my previous post for fixing orphan users,Here is the script that will allow to fix orphan users in a database

Declare @sql varchar(5000)

select @sql = coalesce (@sql ,'') + 'ALTER USER '+ name + ' WITH LOGIN = '+ name + ';'
from sysusers
where issqluser = 1
and (sid is not null and sid <> 0x0)
and (len(sid) <= 16) and suser_sname(sid) is null order by name Print @sql

Exec @sql

Friday, April 9, 2010

Fixing Orphan Users

You all might already known that if we have orphan users in the Database then using the stored Proc sp_change_users_login we can map the user to a Sql Login.
But According to Books Online, this feature will depreciated in the future.

So you guys must be wondering ,then how are we going to fix Orphan user Problem.
Well Here is the Solution.

Alter User UserName
With Login = LoginName


Replace the userName and LoginName accordingly with the orphaned user
and the existing login.

The advantage of using this method over sp_change_users_login is that
ALTER USER WITH LOGIN supports both SQL Server and Windows
logins where as sp_change_users_login works only for SQL Server
logins


I have picked up this tip from Laurentis Blog

http://blogs.msdn.com/lcris/

His Blog is an excellent resource for topics related to Sql Server Security