none
Permissions to Attach DB when Running under User account - SQL Expr 2005

    Question

  • Hi,

    I have a .Net (C#) application using SQL Express 2005, with Windows Authentication, attaching 2 databases at start-up through ADO.Net code. This works just fine (and has for many years). However, users have always run the application with administrator accounts (we typically provide the computers and we set them up that way.) We now have a client who wants to restrict application users to user accounts only. (This particular user is on XP, but I have been testing and need a solution that works on Win7 as well as XP.) In my test environment this is not working, as follows:

    In trying to get this working, I'm running with UAC turned off (Win7) and launching my application with "Run as Administrator". The SQL Express service runs as "Local System". I've made sure that the user account has complete permissions to the locations of the databases. The error is:

    CREATE DATABSE permission denied in database 'master'. Cannot attach the file [dbpath] as [dbname].

    Also, the test platform is one where I've already successfully opened the databases numerous times using an administrator account, and can run fine with an administrator account.

    What additional permissions do I need to assign to the user account to get this to work? I lamely tried giving the user account rights to the 'master' file but this didn't do the trick.

    Thanks in advance for whatever wisdom you can share on this one!

     

     


    Elizabeth Connolly
    Friday, July 22, 2011 1:12 PM

Answers

  • Hello Elizabeth,

    From what it sounds like, this is what I gathered and I may be wrong so please double check my assumptions.

    SQL Server 2005 by default would give SYSADMIN to the group BUILTIN\Administrators for the instance, whether it was express or enterprise. Since you pointed out that all of the previous installations were on computers where the user was an admin, this seems the most likely cause of the permissions issue as now the non-admin user doesn't get picked up into the sysadmin group (but rather is allowed access probably as BUILTIN\Users). To fix this, you could do the following at install time (i.e. in the installer).

    1. Install SQL Server 2005 Express

    2. Connect to the instance with the SA account and password

    3. Run the following script replacing the values as needed (i.e. the actual domain or computer name and the actual user account name)

     

    USE [master]
    GO
    CREATE LOGIN [ComputerOrDomainName\UserAccountName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    EXEC master..sp_addsrvrolemember @loginame = N'ComputerOrDomainName\UserAccountName', @rolename = N'sysadmin'
    GO
    
    

    4. Test attaching the database.

     

     

    What the script does is create a windows login for the non-admin user in the sql server instance. It assigns the sysadmin server role (which means it can do anything it wants) to that login so that there should be no sql server permission issues with your test. If this works for you I would highly advise the checking of permissions to get down to the least amount needed, which for attaching a DB is CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE (source: http://msdn.microsoft.com/en-us/library/ms176061.aspx).

    Also I would like to point out that Sql Server 2005 is in extended support and you should start looking at updating to the latest version SQL Server 2008 R2 at the time of this writing. Also note that SQL Server 2008 DOES NOT automatically include the BUILTIN\Administrators group by default.

    Hope this helps,

    Sean

    • Proposed as answer by Peja Tao Monday, July 25, 2011 1:46 AM
    • Marked as answer by Peja Tao Thursday, July 28, 2011 8:06 AM
    Friday, July 22, 2011 1:48 PM

All replies

  • Hello Elizabeth,

    From what it sounds like, this is what I gathered and I may be wrong so please double check my assumptions.

    SQL Server 2005 by default would give SYSADMIN to the group BUILTIN\Administrators for the instance, whether it was express or enterprise. Since you pointed out that all of the previous installations were on computers where the user was an admin, this seems the most likely cause of the permissions issue as now the non-admin user doesn't get picked up into the sysadmin group (but rather is allowed access probably as BUILTIN\Users). To fix this, you could do the following at install time (i.e. in the installer).

    1. Install SQL Server 2005 Express

    2. Connect to the instance with the SA account and password

    3. Run the following script replacing the values as needed (i.e. the actual domain or computer name and the actual user account name)

     

    USE [master]
    GO
    CREATE LOGIN [ComputerOrDomainName\UserAccountName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    EXEC master..sp_addsrvrolemember @loginame = N'ComputerOrDomainName\UserAccountName', @rolename = N'sysadmin'
    GO
    
    

    4. Test attaching the database.

     

     

    What the script does is create a windows login for the non-admin user in the sql server instance. It assigns the sysadmin server role (which means it can do anything it wants) to that login so that there should be no sql server permission issues with your test. If this works for you I would highly advise the checking of permissions to get down to the least amount needed, which for attaching a DB is CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE (source: http://msdn.microsoft.com/en-us/library/ms176061.aspx).

    Also I would like to point out that Sql Server 2005 is in extended support and you should start looking at updating to the latest version SQL Server 2008 R2 at the time of this writing. Also note that SQL Server 2008 DOES NOT automatically include the BUILTIN\Administrators group by default.

    Hope this helps,

    Sean

    • Proposed as answer by Peja Tao Monday, July 25, 2011 1:46 AM
    • Marked as answer by Peja Tao Thursday, July 28, 2011 8:06 AM
    Friday, July 22, 2011 1:48 PM
  • Hi,

    Please see http://msdn.microsoft.com/en-us/library/ms176061.aspx for details of the permissions required to attach databases.  For example, you could run (assuming the user exists in master database):

    USE MASTER ;

    GRANT CREATE DATABASE TO [test];

    Thanks, Andrew

     


     
    Friday, July 22, 2011 1:49 PM
  • Thanks!!! This looks like exactly what I need and I will try this and ask again if it doesn't work.
    Elizabeth Connolly
    Friday, July 22, 2011 1:58 PM
  • Thank you very much! This plus the other post I think gave me what I need to address this. I'll ask again if it doesn't work.
    Elizabeth Connolly
    Friday, July 22, 2011 1:59 PM