none
Unable to attach a database: Security

    Question

  • Hello:

    I just installed SQL Server 2012. I am signed as a user who is part of the Administrators' group.

    When I try to attach a database, when I click on Add, it says I don't have access privileges to the Data folder.

    But I am able to create a new database that creates the data and the log file in the same folder.

    I am able to browse the Data folder in Windows Explorer.

    Can someone help me understand the issue and more importantly how I can resolve it?

    Venki

    Saturday, February 09, 2013 2:24 AM

Answers

  • Rama:

    I changed the Account used to start the service from NT Service\MSSSQL$Venkat to my account and everything works as expected.

    Venki

    • Marked as answer by TheVenkster Saturday, February 09, 2013 11:50 PM
    Saturday, February 09, 2013 11:50 PM
  • Alberto:

    I changed the Account used to start the service from NT Service\MSSSQL$Venkat to my account and everything works as expected.

    Venki

    • Marked as answer by TheVenkster Saturday, February 09, 2013 11:50 PM
    Saturday, February 09, 2013 11:50 PM

All replies

  • When you are detaching or attaching a database, the Database Engine tries to impersonate the Windows account of the connection performing the operation to guarantee that the account has permission to access the database and log files.

    When you detach, only the account performing the operation will be granted permission on those files. You would have to grant permissions to additional accounts, if they are needed after the database is detached.

    http://msdn.microsoft.com/en-us/library/ms189128(v=sql.105).aspx

    Saturday, February 09, 2013 2:45 AM
  • Hello,

    Please right click on the folder that contains the database(s) you would like to attach, select Properties, click on the Security tab and provide Full Control permissions to the Windows account that is starting SQL Server service. Use SQL Server Configuration Manager to verify the name of the account.

    Make sure the SQL Server service account has access to the database files individually.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Saturday, February 09, 2013 2:55 AM
  • Keerthi:

    I checked the security. At the folder level (data) and at the individual level Administrators (Of which I am a member of), all the privileges checked.

    What do I need to do?

    venki

    Saturday, February 09, 2013 2:59 AM
  • If you have the accesses then it should work, in-case if you wana try try like this-

    1.Right click on SSMS->Run as administrator then ensure the files of the path called is right one and attach it.

    2.If you need T-SQL please try with sp_attach_dboption.

    3.If you need move database files to where the MSSQL folder that working for the SQL server DB engine(i,e for the service account) then attach it.

    you can try with the service account as well.

    if possible can you please paste the complete error but try with the above options-


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Saturday, February 09, 2013 3:12 AM
  • Alberto:

    The folder where the data files are stored is C:\venki\MS SQL Server Data\MSSQL11.Venkat\MSSQL\Data

    I checked the Services and the account, NT Service\MSSQL$Venkat is being used to start the service.

    I checked the security of the Data folder, C:\venki\MS SQL Server Data\MSSQL11.Venkat\MSSQL\Data and it lists MSSQL$Venkat as having all privileges.

    But when I check the security of data and log file of the database that I am trying to attach, it does not list MSSQL$Venkat. I tried to add that account but it does not show up in the list of accounts.

    venki

    Saturday, February 09, 2013 3:13 AM
  • Rama:

    I tried that too. Launching SQL Server Management Studio as an Administrator but I run into the same issue.


    Venki

    Saturday, February 09, 2013 3:27 AM
  • But when I check the security of data and log file of the database that I am trying to attach, it does not list MSSQL$Venkat. I tried to add that account but it does not show up in the list of accounts.

    >>are you the OS admin , if not ask the OS team to check on that.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Saturday, February 09, 2013 3:37 AM
  • Hello,

    If you don’t see the account, you need to click on the Edit button, and then  click on the Add button.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Saturday, February 09, 2013 3:44 AM
  • Alberto:

    The account MSSQQL$VENKAT does not show up as an option!

    venki

    Saturday, February 09, 2013 3:55 AM
  • Rama:

    I am part of the Administrators group.

    I am able to navigate to the folder where the data and the log files are!

    Venki

    Saturday, February 09, 2013 3:57 AM
  • Rama:

    I am part of the Administrators group.

    I am able to navigate to the folder where the data and the log files are!

    Venki

    I agree you can navigate & part of admin, but dont mind can you ask OS/AD team to check -properly permission exists or not for which account you are trying or did you tried with the service account of SQL too....

    The reason why I said , in few cases we can add it to folder level permissions or windows administrators but few moments ago they will loss hence informed to check with them.

    check the errorlogs/eventlogs as well did any other thing its logged.

    try with create database also


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.



    • Edited by Rama Udaya Saturday, February 09, 2013 5:36 AM
    Saturday, February 09, 2013 4:10 AM
  • Rama:

    I changed the Account used to start the service from NT Service\MSSSQL$Venkat to my account and everything works as expected.

    Venki

    • Marked as answer by TheVenkster Saturday, February 09, 2013 11:50 PM
    Saturday, February 09, 2013 11:50 PM
  • Alberto:

    I changed the Account used to start the service from NT Service\MSSSQL$Venkat to my account and everything works as expected.

    Venki

    • Marked as answer by TheVenkster Saturday, February 09, 2013 11:50 PM
    Saturday, February 09, 2013 11:50 PM