Sunday, February 19, 2012

adding dbo to db_owner

Im duplicating a database by running the script below. This works fine. My only problem is that the dbo user does not by default have any role memberships in the new database hence no access. I have tried using sp_addrolemember but dbo is not a valid user for this procedure. Adding dbo to the db_owner role through the sql2005 MS works fine, but I would very much like to script this. Any suggestions?


--copy database
use master;
alter database polaris_regular set single_user with rollback immediate;
DROP DATABASE polaris_regular;
backup database polaris to disk = 'c:\tmp\polarisbak.bak' with INIT,format;
restore filelistonly from disk = 'c:\tmp\polarisbak.bak';
restore database polaris_regular from disk = 'c:\tmp\polarisbak.bak'
with move 'polaris' to 'C:\Data\polaris_regular.mdf',
move 'polarisLog' to 'C:\Data\polaris_regularLog.mdf';dbo is always a member of db_owner. You should never have to add it explicitly.|||you may want prefix your objects with [dbo], like [dbo].[polaris_regular]|||Thanks guys. Somehow I can't reproduce the situation, so the problem might have been something else.

No comments:

Post a Comment