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