[AccessD] SQL Server 2008 - database ownership issues

Thanks for the reply.  Both Paul and I have SysAdmin roles, but neither of us have any of the other 
roles.

John W. Colby
www.ColbyConsulting.com


Rusty Hammond wrote:
> Just wanted to add, it wasn't just to allow the developers to do BULK
> INSERTS when we added them to the bulkadmin role.
> 
> -----Original Message-----
> From: Rusty Hammond 
> Sent: Friday, June 25, 2010 1:53 PM
> To: 'Access Developers discussion and problem solving'; Sqlserver-Dba;
> VBA
> Subject: RE: [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.
> **********************************************************************
>