[dba-SQLServer] SQL Server 2008 - database ownership issues

jwcolby jwcolby at colbyconsulting.com
Fri Jun 25 12:32:38 CDT 2010


I am writing code that uses the SMO object to do things like attach and detach, backup and restore 
databases from C#.  Basically in my business I end up with a ton of order and count databases and 
detach them once I am finished processing that count or order.  I now want to attach these and back 
them up, with compression, then delete the original database file which is typically much much larger.

The concept works, however I am running into ownership issues on the restore and the attach side of 
things.  Oddly the code can detach and backup databases without owning them, but it appears that the 
code cannot restore or attach them without owning them (the database).  I suppose this makes sense 
from a security perspective, but it is causing me headaches in writing the code since my programmer 
is doing this stuff under his own username.  The exceptions from SMO / SQL Server give generic 
"Access denied" error messages when Paul tries to run the code but it works when I try to run the 
code - the databases were created by my user.

We determined that is an ownership issue because if Paul creates a test database, he can perform all 
of the operations on that database, but not on the ones that I created.  I can go in and perform 
those operations on the ones I created, though I did not attempt to do so on the objects he created.

So this (ownership) is my take on what is going on.  It may be something else entirely, I am 
certainly no SQL Server guru, but it appears that this is the cause.

Has anyone ever seen this and know how to allow another user to do an attach or restore?

I am using windows authentication for everything SQL Server related ATM.

-- 
John W. Colby
www.ColbyConsulting.com



More information about the dba-SQLServer mailing list