Wednesday, March 14, 2012

Troubleshooting orphaned users

Reminder

Every couple of months I get to migrate a database backup to some other server and most often the users need to be recreated. As a reminder for myself, I’ve written this post.

The detailed explantaion can be found in this KB article on technet

Step to resolve orphaned users

Use SSMS and connect to the server.

Open a new query window

To detect orphaned users in a database:

Use <MyDatabase>

go

sp_change_users_login 'report'

go

Query result

UserName    UserSID
someuser    0xCEC65E3AE7C0B64CA05435BE2B580EE9

Now I know the SQL user account, I can create the login with the necessary credentials in SSMS under Security – Logins. I only create the account and grant it server role ‘public’, no specific user mapping, give a temporary password, till the responsible can give me the correct one, the default db is master.

Use <MyDatabase>

go

sp_change_users_login 'update_one', 'someuser', 'someuser'

go

Now the user properties can be modified to set default database to this database name, alter password and grant other roles to the user on the database.

2 comments:

Anonymous said...

louis vuitton outlet, sac longchamp, ralph lauren pas cher, replica watches, louboutin outlet, louboutin shoes, christian louboutin outlet, prada outlet, ugg boots, longchamp pas cher, tiffany and co, louis vuitton, louboutin, air jordan pas cher, tory burch outlet, cheap oakley sunglasses, nike outlet, polo ralph lauren outlet, ray ban sunglasses, polo ralph lauren outlet, louis vuitton outlet, nike air max, replica watches, louis vuitton, air max, longchamp outlet, michael kors, oakley sunglasses, chanel handbags, nike free, nike roshe run, oakley sunglasses, burberry, tiffany jewelry, ray ban sunglasses, kate spade outlet, prada handbags, oakley sunglasses, nike air max, louis vuitton, louboutin pas cher, uggs on sale, ray ban sunglasses, oakley sunglasses, longchamp, jordan shoes, gucci outlet, ugg boots, nike free, longchamp outlet

Anonymous said...

vanessa bruno, coach outlet, nike free run uk, michael kors, michael kors outlet, coach outlet, replica handbags, new balance pas cher, michael kors outlet, hollister pas cher, michael kors, converse pas cher, nike air max, tn pas cher, ray ban pas cher, michael kors, abercrombie and fitch, lululemon, ralph lauren uk, vans pas cher, michael kors, michael kors outlet, mulberry, nike air max, kate spade handbags, nike air max, hermes, ugg boots, hollister, nike roshe, lacoste pas cher, oakley pas cher, nike blazer, ugg boots, true religion jeans, michael kors outlet, sac guess, air force, true religion outlet, hogan, ray ban uk, coach purses, timberland, north face, burberry, michael kors outlet, true religion jeans, burberry outlet online, north face, true religion jeans