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