patternMajor
SQL-Server - Grant user DBO permission on database
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
I thought this would work:
But I get the following error, since that user doesn't exist in the Restored_Prod database:
How do I add the user 'chris' to the database to be able to run the
Is there a way to add the user permissions using
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'
goBut 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.
http://technet.microsoft.com/en-us/library/ms173463.aspx
use Restored_Prod
GO
CREATE USER [chris] FROM LOGIN [chris];
exec sp_addrolemember 'db_owner', 'chris';
GOhttp://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';
GOContext
StackExchange Database Administrators Q#54389, answer score: 38
Revisions (0)
No revisions yet.