Attach a database

  • Hi,

    I have a user who is part of an AD group, the group is a login on the server with the following server permissions;

    The issue comes when they try to attach a database, they get the error message;

    ADMINISTER BULK OPERATIONS

    ALTER ANY DATABASE

    CONNECT SQL

    CREATE ANY DATABASE

    VIEW ANY DATABASE

    "User 'guest' does not have permission to run DBCC checkprimaryfile. (Microsoft SQL Server, Error: 2571)"

    If add them into the sysadmin role then they are able to perform the attach.

    Does anyone know how to resolve this? Am I missing something on this?

    They have also tried running SSMS as an admin (seems a popular work around online) but this didn't resolve the issue.

    Any help would be appreciated.

    Thanks,

    Nic

  • you could try granting permission on the file restore location to the AD group. It might be that the AD group doesn't have permission to create a file in windows.

    check this link http://msdn.microsoft.com/en-us/library/ms189128.aspx

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Thanks for this, I'll give it a try in the morning and let you know how I get on.

    Nic

  • Hi Bob,

    No luck.

    So what I did was to grant permissions on the folder to the following;

    1. The service account of SQL Server.

    2. The service account for SQL Server agent (it's different to the above, due to be made the same shortly).

    3. The AD group of the user affected.

    4. The user directly just to test.

    None of them resolved the issue. What I'm struggling with is;

    1. Where the 'guest' user mentioned factors into it?

    2. When I make the user a sysadmin it works, it seems this is because it can then impersonate the service account, when it's not sysadmin it doesn't seem to be able to impersonate the account, but then I would expect it to use it's own credentials, but this doesn't work. Is my assumption correct here? I had thought that the impersonation of the service account would only occur for SQL logins, not Windows logins.

    Thanks,

    Nic

  • Can you try creating the user's AD account as a login then granting those SQL rights directly to that login rather than through the group?

  • Hi,

    No that didn't work, just to confirm what I did then;

    I added the account as a login on the instance, removed it from the AD group. Then granted the account the permissions above. I then retried and it gave the same error message.

    "User 'guest' does not have permission to run DBCC checkprimaryfile."

    In addition to the above I then tried adding the account to the administrator group on the Windows machine but that didn't make any difference.

    I'm at a loss to explain this now.

    Thanks for the suggestion though.

    Nic

  • Ok, no worries. Is the DB being added via GUI or T-SQL script or other?

  • By the UI, I'll try doing it via T-SQL now.

  • Interesting (but more confusing).

    It works when done via T-SQL but not via SSMS UI.

    Really confused now.

  • Cool. I suspect that the GUI is trying to execute DBCC CheckPrimaryFile against the db master file pre-attach to get file information, which is failing as the user isn't defined as a user in the database (hence 'guest').

    Presuming you're using CREATE ... FOR ATTACH or sp_attachdb, it must not run that step or somehow elevates permissions during execution.

  • Hi,

    I think you've got something there.

    If I add the user to the master database, but not a member of any role then I get the same error back but instead of 'guest' it states the users name. So as you say it must be running against the master database, and doing so as the guest.

    Now I just need to figure out how to get it to be able to execute 'checkprimaryfile'.

    Any thoughts on that?

    Thanks for your help with this, its appreciated.

    Nic

  • No problem, unfortunately I think it'd be down to trial & error - DBCC CheckPrimaryFile is an undocumented command and I haven't seen any info on what permissions would be required to run it other than sysadmin.

  • I think I'm going to just get the user to run it via the T-SQL script, saves me either giving them admin rights or going through a painful trial and error process (which may not even sort this).

    Thanks again, I learnt a few things there.

    Nic

  • Thanks, me too.

    One of the reasons I tend to favour T-SQL scripts over the GUI is sometimes the GUI does things you don't expect or can't see 🙂

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply