12

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?

32

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

| improve this answer | |
  • 1
    That makes sense - I didn't know there was a distinction between login and user. Thanks – OrangeGrover Dec 3 '13 at 23:45
  • 2
    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

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.