orphaned users in sql server

Question

orphaned users in sql server

reddisqldba

SSC Enthusiast

Points: 125
More actions
July 24, 2013 at 2:21 am

#277792

hi this is reddi Krishna i created a new login at server level and mapped to user level permissions after i have executed the below command
select *from sys.sysusers and select *from sysusers
by executing both these commands i got the same sid no. after i deleted the login which i had created server level login. after i execute the following command
sp_change_users_login 'report'
by executing this command i got below created user name and sid.
nikil0x191792319FBF2A4788077A5F20567D7D
so,from this what can i do for getting identification of orphaned users
please reply as early as possible

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

Start by reading about the "sp_change_users_login" at http://msdn.microsoft.com/en-us/library/ms174378.aspx

The option 'report' has resulted in a list of users within the current database that do not have a link to a login. By using the 'auto_fix' or the 'update_one' option, you can (re-)establish this link.

exec sp_change_users_login 'auto_fix', 'nikil'

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

colin.frame Default port Points: 1483 More actions · Answer 2

Alternatively, you could transfer the logins and passwords from the original server to the target server. This can have the advantage of not having to deal with orphaned users after a database has been restored to a different server. It involves creating a couple of stored procedures in the original server's master database - see article http://support.microsoft.com/kb/918992 for details.

Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.

SQLAli Ten Centuries Points: 1079 More actions · Answer 3

After executing the below 1st command, we got the list of orphan logins, it listed out and i selected one of the users to auto_fix. But unfortunately it throws below error. Hope we also need to give the password to fix this. If any one has complete knowledge could you share?

sp_change_users_login 'report'

sp_change_users_login 'auto_fix', 'kumar'

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214

An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.

Thanks,
I’m nobody but still I’m somebody to someone………….

christopher.gray Old Hand Points: 372 More actions · Answer 4

The most likely reason is no login named "Kumar" exists, so the [sp_change_users_login] proc can't successfully un-orphan the user.

See if you have a login named 'kumar'. If not, create one and try the auto_fix again.