Thursday, April 12, 2012

Find and Fix Orphan Users

Sp_change_users_login – This SP helps us to maps the existing database user to SQL login and creates a login if required


Below are the parameters of SP

1. Report -- Reports the orphan users
2. Auto_Fix -- Creates SQL Login for the corresponding database user
3. Update_one -- Maps the database user with corresponding SQL login if SIDS are different.

How To Find Orphan Users in the database?

Use Kalyandb
exec sp_change_users_login 'Report'
                           (OR)
select name from sysusers where issqluser = 1 and uid >4
and name not in (select name from master..syslogins)

Auto_Fix option helps us to fix the orphan user by creating SQL login with the given password. It takes three parameters a) database_user b) SQL Login (we can pass Null or SQL Login name) c) password

USE Kalyandb
EXEC sp_change_users_login 'Auto_Fix', 'test', null, 'password@123'

Output:
Barring a conflict, the row for user 'test' will be fixed by updating its link to a new login.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 1.

Update_One – It maps the Database user with appropriate SQL Login. Sometimes we face login issues eventhough database user and SQL Login exists on the server. It might be because of their SIDs are different. The sp_change_users_login ‘report’ helps to identify that one also.

Sp_change_user_login ‘Report’
                                (OR)
select uid, sl.sid, su.sid name from sysusers su, master..syslogins sl
where su.name = sl.name and issqluser = 1 and uid > 4 and su.sid <> sl.sid

Update_one helps to correct the SIDs between dbuser and syslogin

EXEC sp_change_users_login 'update_one','test','test'

Sample select statement which will generate Update_One commands for all database users which has SID mismatches with SQL Logins

select 'EXEC sp_change_users_login ' + '''' + 'update_one' + '''' + ',' + '''' + name + '''' + ',' + '''' + name + '''' + char(13) from sysusers where issqluser = 1 and uid >4

No comments: