HiveBrain v1.2.0
Get Started
← Back to all entries
patternMajor

SQL-Server - Grant user DBO permission on database

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqluserpermissiongrantdatabasedboserver

Problem

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?

Solution

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

Code Snippets

use Restored_Prod
GO

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

Context

StackExchange Database Administrators Q#54389, answer score: 38

Revisions (0)

No revisions yet.