[AccessD] SQL Server 2008 - database ownership issues

John,

I'm assuming your programmer has sysadmin rights to the server?  If so,
you might also try giving him bulkadmin rights.  I don't remember the
details but we've had to do that for some of our developers.

To give bulkadmin from SQL Server Management System (SSMS), right click
the user under the server security folder, go to Properties, choose
Server Roles, check bulkadmin, click OK.

HTH,

Rusty

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, June 25, 2010 12:33 PM
To: Access Developers discussion and problem solving; Sqlserver-Dba; VBA
Subject: [AccessD] SQL Server 2008 - database ownership issues

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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************