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
Begin
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
END
drop table tempdb..OrphUsers '
Select * from tempdb..OrphUsers_Histroy
4 comments:
Can we use this script to resolve logins as well?
Hi Amit,
you can use the below command to resolve
Exec sp_change_users_login 'Update_one','UserName','UserName'
Its Really nice post thanks
Mua vé máy bay tại Aivivu, tham khảo
vé máy bay đi Mỹ khứ hồi
vé máy bay từ houston về việt nam
bay từ nhật bản về việt nam
lich bay tu duc ve viet nam
giá vé máy bay từ canada về Việt Nam
khi nào mở lại đường bay hàn quốc
khách sạn cách ly
ve may bay chuyen gia sang Viet Nam
Post a Comment