SQL-Server - Grant user DBO permission on database

Question

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?

Answer 1

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