I have a stored procedure to restore the latest backup of a prod database to an acceptance database. Once it's restored I need to grant a user read access to the database.

Use statements are not allowed in stored procedures, and both ALTER ROLE (it's a SQL Server 2012 box) and sp_addrolemember don't take a target database parameter.


EXEC('USE mydb;ALTER ROLE myRole ADD MEMBER myMember;');

But I am not quite sure what you are trying to achieve. After restoring a database to a different server, you need to fix the link between users and logins. For that you can execute this in your database:


Or use the same EXEC('USE...') method shown above with that ALTER USER statement. That should get you where you need to be. All the database related permissions are stored in the database itself, so they get restored with the database.

| improve this answer | |
  • I need to grant access to the restored database for a user that does not have access to the original database. – StormRider01 Mar 21 '14 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.