Adding users to a DB from a Stored Procedure in Master DB

Question

Adding users to a DB from a Stored Procedure in Master DB

karen.rogers

SSC Enthusiast

Points: 180
More actions
July 16, 2008 at 10:21 am

#184885

I am restoring a database on server2 with a .bak file from a database on server1. The database on server2 has different users to the database on server1. When the database is restored the users of the database on server2 no longer have access. Can I create a Stored Procedure in the Master database which will create the users and put them into the correct roles once the database has been restored? The users logins still exist.

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Those are called ophaned users because they're not connected to any logins. You can use sp_change_users_login to report on ophaned users and connect them with the proper logins.

Greg

karen.rogers SSC Enthusiast Points: 180 More actions · Answer 2

The sp_change_users_login is used to correct users when there is no login. In this case I have a login but no user. Is there a similar procedure I can use to fix this?

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 3

There is a script to migrate the logins from the old server to the new server.

http://support.microsoft.com/default.aspx?scid=kb;en-us;246133

You need to bring over the logins info including SID's which are part of the login record.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

karen.rogers (7/17/2008)
The sp_change_users_login is used to correct users when there is no login. In this case I have a login but no user. Is there a similar procedure I can use to fix this?

So, you want to replace the users in the restored database with different users linked to existing logins? I think you'll just have to drop the old users and create the new ones. There's no system stored procedure to do that.

Greg

karen.rogers SSC Enthusiast Points: 180 More actions · Answer 5

I have got around my original problem by adding the user to the database that I am restoring from. Thanks to all the replies.

I now have another problem where the authority for one of my database users is being removed from the Login authorities following the restore. The login still exists. Is there a procedure that I can use, similar to the sp_change_users_login, that I can store and run from the master database, passing in the name of the database for which I want to add the authority?