locked
DBCREATOR granted, but SQL Server Login cannot attach database (granting sysadmin allows) RRS feed

  • Question

  • I have created a sql server login, granted dbcreator, have the sql services (engine and agent) setup to use a domain account that has admin privileges on the server and explicitly given that account full control over the files (and directories for those files), but this login cannot locate any database files.  Error is:
    Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

    If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box

    If I type the path to the mdf, I get error:   
    user 'mark' does not have permission to run DBCC checkprimaryfile. (Microsoft SQL Server , Error: 2571)

    As soon as I grant the user "mark' sysadmin fixed server role, all is fine and I can locate the database files and attach.

     

    I have tried this exact same thing on a Windows server2003 server, Windows 7 box, Windows Server 2008 server all with the same results for SQL Server 2005 (both 32 and 64 bit editions).  What am I missing here? 


    Tuesday, April 27, 2010 5:57 PM

All replies

  • Did you check the permissions of the actual files? Detaching a DB will put specific ACLs on the file to allow either the person who did the detaching or the service account (if it cannot impersonate). See http://msdn.microsoft.com/en-us/library/ms189128.aspx for details.
    No great genius has ever existed without some touch of madness. - Aristotle
    Tuesday, April 27, 2010 8:41 PM
  • Thanks for the reply.  yes I have explicitly granted permissions to the files as well.  That was my first thought, but unfortunately wasn't the case.  As soon as I click the ADD button to locate the database file (locate files dialog), I get the error and then when I click ok, I don't get any files in the dialog.  As soon as I grant the user sysadmin fixed role, this all goes away, but this is not something I want to do.  

     

    It is probably one of those things I have done a million times before and for some reason just forgot a step...

    Wednesday, April 28, 2010 11:02 AM