SQL Orphaned users – Solution

I would like to explain about Orphaned users in simple terms which covers majority of scenarios.

User permissions are managed in SQL Server with Logins (Authentication at Server Level) and Users (Authorization at Database level), these records are connected with sid. When there is a mismatch of sid values such users are considered as orphaned users.

Orphaned users will be commonly observed when a database is restored from one Server/instance to a different (Ex: Production to UAT or DEV).

Symptom: User will not be able to Access the Database using SQL Login, you see that the account has appropriate permissions on the Database.

How do you find all the Orphaned users in a given Database: Following TSQL Script can be used

-- use MYDATABASENAME
-- how to find all the impacted users
EXEC sp_change_users_login 'Report'

-- Actual Query
select UserName = name, UserSID = sid from sysusers
where issqluser = 1
and (sid is not null and sid 0x0)
and (len(sid) <= 16)
and suser_sname(sid) is null

How to address the problem: There are 2 types of Orphaned users
1. First category, SQL Login and SQL User Account exists but there is a mismatch in the sid. Following Script can be used to find such users/ Fix the problem on specific users/ Script to fix all impacted users

-- 1 . only SID Mismatch users
select l.name, * from master.dbo.syslogins l
join sysusers u (nolock) on u.name = l.name
where u.sid l.sid
-- OR
select u.name from sysusers u
left join master.dbo.syslogins l on u.name = l.name
where u.issqluser = 1
and (u.sid is not null and u.sid 0x0)
and (len(u.sid) <= 16)
and suser_sname(u.sid) is null
and l.name is not NULL
order by u.name

-- Fix for such users
sp_change_users_login 'Update_One' ,'username', 'username'

-- Fix with Script
-- This one handles the uesr accoutns which are available both at Server level and Database level but with Different SID
DECLARE @SQuery NVARCHAR(MAX)
DECLARE Query_cursor CURSOR FOR
select 'sp_change_users_login ''Update_One'' ,''' + u.name+''', ''' + u.name + '''' as Query
from sysusers u
left join master.dbo.syslogins l on u.name = l.name
where u.issqluser = 1
and (u.sid is not null and u.sid 0x0)
and (len(u.sid) <= 16)
and suser_sname(u.sid) is null
and l.name is not NULL
order by u.name
OPEN Query_cursor
FETCH NEXT FROM Query_cursor INTO @SQuery
WHILE @@FETCH_STATUS = 0
BEGIN
print @SQuery
EXEC(@SQuery)
FETCH NEXT FROM Query_cursor INTO @SQuery
END
CLOSE Query_cursor
DEALLOCATE Query_cursor

2. Second category, SQL Login does not exists on the Server but the user account exists on the Database with appropriate permissions.

-- 2. For user accounts which are in database but there is no login at Server level
select u.name from sysusers u
left join master.dbo.syslogins l on u.name = l.name
where u.issqluser = 1
and (u.sid is not null and u.sid 0x0)
and (len(u.sid) <= 16)
and suser_sname(u.sid) is null
and l.name is NULL
order by u.name

I recommend 2 different ways to address such users (Delete the User account on Database or Create a Login and map Login and User Account)
A. Delete user account
-- Option A to Delete such USers
drop user 'username'

-- Fix with Script
-- This Handles the user accoutns which are available only in Database but not on Server level
-- This will Just delete such users accounts
DECLARE @SQuery NVARCHAR(MAX)
DECLARE Query_cursor CURSOR FOR
select 'DROP USER [' + u.name+']' as Query
from sysusers u
left join master.dbo.syslogins l on u.name = l.name
where u.issqluser = 1
and (u.sid is not null and u.sid 0x0)
and (len(u.sid) <= 16)
and suser_sname(u.sid) is null
and l.name is NULL
order by u.name
OPEN Query_cursor
FETCH NEXT FROM Query_cursor INTO @SQuery
WHILE @@FETCH_STATUS = 0
BEGIN
print @SQuery
EXEC(@SQuery)
FETCH NEXT FROM Query_cursor INTO @SQuery
END
CLOSE Query_cursor
DEALLOCATE Query_cursor

B. Create a Login for the user and map with User Account
-- option B to create a Login
EXEC sp_change_users_login 'Auto_Fix', 'dbscan',NULL, 'Password1!';

-- Fix with Script
-- This Handles the user accoutns which are available only in Database but not on Server level
-- This will create Logins for such user accounts with defined password
DECLARE @SQuery NVARCHAR(MAX)
DECLARE Query_cursor CURSOR FOR
select 'EXEC sp_change_users_login ''Auto_Fix'', ''' + u.name + ''',NULL, ''Password1!''' as Query
from sysusers u
left join master.dbo.syslogins l on u.name = l.name
where u.issqluser = 1
and (u.sid is not null and u.sid 0x0)
and (len(u.sid) <= 16)
and suser_sname(u.sid) is null
and l.name is NULL
order by u.name
OPEN Query_cursor
FETCH NEXT FROM Query_cursor INTO @SQuery
WHILE @@FETCH_STATUS = 0
BEGIN
print @SQuery
EXEC(@SQuery)
FETCH NEXT FROM Query_cursor INTO @SQuery
END
CLOSE Query_cursor
DEALLOCATE Query_cursor

Advertisements
This entry was posted in SQL, SQL Query and tagged , , , , . Bookmark the permalink.

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