January 20, 2015 at 8:39 am
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
January 20, 2015 at 9:26 am
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
January 20, 2015 at 11:49 am
Thanks for this, I'll give it a try in the morning and let you know how I get on.
Nic
January 21, 2015 at 3:20 am
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
January 21, 2015 at 3:48 am
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?
January 21, 2015 at 4:07 am
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
January 21, 2015 at 4:15 am
Ok, no worries. Is the DB being added via GUI or T-SQL script or other?
January 21, 2015 at 4:17 am
By the UI, I'll try doing it via T-SQL now.
January 21, 2015 at 4:20 am
Interesting (but more confusing).
It works when done via T-SQL but not via SSMS UI.
Really confused now.
January 21, 2015 at 4:37 am
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.
January 21, 2015 at 4:54 am
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
January 21, 2015 at 5:11 am
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.
January 21, 2015 at 5:33 am
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
January 21, 2015 at 5:45 am
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