June 10, 2009 at 7:20 am
Hi,
I'm new to this...Last day i got a dB backup.The backup was taken before deleting the server.I restored it in our server..
The question is "is there any way to find the users and there roles who had accessing the database before while it was in the server i deleted"
June 10, 2009 at 7:29 am
I hope this should work: Restore the database and under security check each user and their corresponfing Roles, should e highlighted. infact they might be orphaned but the roles mappings are retained in the database --> security--> users..
I am not sure about a Script, but there might be other experts responding to this with their scripts.. but again i doubt the scripts might work becasue of the users being orphaned.
but yes as far as i know, you probably should be able to see the roles associated with the users..
wanted to add another note:
check for orphaned users:
use Database
exec sp_change_users_login 'report'
Also there are scripts at microsoft site to copy logins from server A to B.. but i guess you have already deleted the server as you mentioned... best luck
Maninder
www.dbanation.com
June 10, 2009 at 8:49 am
Mani Singh (6/10/2009)
I hope this should work: Restore the database and under security check each user and their corresponfing Roles, should e highlighted. infact they might be orphaned but the roles mappings are retained in the database --> security--> users..I am not sure about a Script, but there might be other experts responding to this with their scripts.. but again i doubt the scripts might work becasue of the users being orphaned.
but yes as far as i know, you probably should be able to see the roles associated with the users..
Once you have re-created the 'logins' from your old server, you will need to pair the 'login' accounts back up with the 'user' records in the restored database. To do this run:
select 'alter user [' + b.name + '] with login = [' + b.name + ']'
from .dbo.sysmembers a
join .dbo.sysusers b on a.memberuid = b.uid
where b.islogin=1 and b.name 'dbo'
group by b.name
order by b.name
Then copy the output (output must be to text) and run as a seperate query.
Database users will then be reconnected to the server login record.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply