Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a reporting database that I restore every night from a backup of the production database. When I restore, I need to add a user to the DBO role for that database.

I thought this would work:

use Restored_Prod
go
exec sp_addrolemember 'db_owner', 'chris'
go

But I get the following error, since that user doesn't exist in the Restored_Prod database:

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'chris' does not exist in this database.

How do I add the user 'chris' to the database to be able to run the sp_addrolemember to make him a DBO of that database? Is there a way to add the user permissions using sp_addrolemember command from the master db where the user exists?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

User principals must exist in a database before you can grant them permissions.

use Restored_Prod
GO

CREATE USER [chris] FROM LOGIN [chris];
exec sp_addrolemember 'db_owner', 'chris';
GO

http://technet.microsoft.com/en-us/library/ms173463.aspx

share|improve this answer
    
That makes sense - I didn't know there was a distinction between login and user. Thanks –  OrangeGrover Dec 3 '13 at 23:45
1  
See this answer to a different question where I explain the login/user relationship in a little more detail. –  Mike Fal Dec 4 '13 at 0:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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