jwcolby
jwcolby at colbyconsulting.com
Fri Jun 25 14:49:44 CDT 2010
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. > ********************************************************************** >