Revoke access for all Orphaned users in a server | SansSQL

Saturday, April 24, 2010

Revoke access for all Orphaned users in a server

When a database is moved or restored from one server to another server the login Id's stored in the master database do not align with the login Id's stored in the user database, which account for the orphaned users in the databases.

To get the list of Orphaned users in a database, execute the below query on the particular database.
Exec sp_change_users_login 'Report'

To cycle through all the databases in a server to find the orphaned users, execute the below query

Exec sp_MSforeachdb 'use ?;
Select db_name() as DatabaseName
Exec sp_change_users_login ''Report'''

To Revoke access for all the Orphaned Users in a server, Run the below script

Create a History table in tempdb or in any other DB as required or if required.

Create Table tempdb..OrphUsers_Histroy
(Username nvarchar(250),
userSID varbinary (85),
SQLText nvarchar(500),
Fixedon datetime)

Select * from tempdb..OrphUsers_Histroy

Exec sp_MSforeachdb 'use ?;

Create Table tempdb..OrphUsers 
(Username nvarchar(250),
userSID varbinary (85),
SQLText nvarchar(500))

insert into tempdb..OrphUsers (Username,userSID) 
Exec sp_change_users_login ''Report''

update tempdb..OrphUsers set SQLTEXT=''Exec sp_revokedbaccess ''+username+''''

while (select COUNT(*) from tempdb..OrphUsers )<>0
declare @SQLTEXT nvarchar(500)
select @SQLTEXT=SQLText from tempdb..OrphUsers
exec sp_executesql @SQLTEXT

/* Remove this insert statement if you do not want to log history */ 
insert into tempdb..OrphUsers_Histroy
select Username,userSID,SQLText,getdate() from tempdb..OrphUsers where SQLTEXT=@SQLTEXT

delete from tempdb..OrphUsers where SQLTEXT=@SQLTEXT

drop table tempdb..OrphUsers '

Select * from tempdb..OrphUsers_Histroy


amit said...

Can we use this script to resolve logins as well?

Sandesh Segu said...

Hi Amit,

you can use the below command to resolve
Exec sp_change_users_login 'Update_one','UserName','UserName'

ashish malwal said...

Its Really nice post thanks

Post a Comment