March 29, 2006 at 4:20 pm
Hello!
I recently set up a new SQL Standard login on a SQL 2005 database deployment and granted the user the dbcreator role. However, when the user attempts to restore a database via the GUI interface (i.e. selects the "Restore Database..." option, hits the ellipsis next to "From device" and attempts to browse for a backup file with the "Add" button), he receives the following error:
"[default backup path] Cannot access the specified path or file on the server. Verify that you have the necessary security priviliges 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 dialogue box."
Manually specifying the full path and file as indicated also produces a security-related error about not being able to resolve the specified file location.
Running a Profiler trace in the background, it appears that this function calls xp_fixeddrives and xp_get_tape_devices, among other things. Until I grant his SQL login the sysadmin role, both of these extended stored procedures run, but produce no output. When the sysadmin role is granted, all of the drives and their available space appear as expected. Adding the login as a dbo to master (to make sure there were no additional stored procedures it did not have access to) and adding back the BUILTIN\ADMINISTRATORS group as a sysadmin (taking out in our build process) had no effect.
I further tested a number of other scenarios using the sa account and my own Windows authenticated account. Sa could produce the output without any problem, as could my Windows account - whether I had granted it the sysadmin role, or simply just the dbcreator role. Odd. Actually, I could produce the file list without ANY roles granted to me (just couldn't actually run the RESTORE part of the process).
The account running the SQL services is a domain user added to the local administrators group. When logged on locally with this account, it can see and control all disks on the machine. I assumed that this account was the permissions driver for results of xp_fixeddrives and xp_get_tape_devices, but apparently I'm mistaken?
Any been-there-done-that or any ideas of what I'm doing wrong?
Thanks!
Allan Harris
March 30, 2006 at 3:08 am
--Try:
sp_addrolemember 'db_backupoperators', 'youruserhere'
March 30, 2006 at 4:42 am
Would db_owner or owning the database in question cover this role? My user is in this scenario now for 2 separate database; still no luck...
Thanks!
Allan
March 30, 2006 at 5:25 am
What is this user account to be used for/by?
Is it specifically creating a new database from a tape backup?
March 30, 2006 at 5:51 am
Right, I think I see the issue now.
The 'sa' account and your windows account have access to the operating system. Sysadmins use the sql services to access the path you're having problems with. Non-sysadmin windows accounts require permissions on NTFS implicitly or explicitly.
The non-sysadmin SQL login will need proxy access set up.
March 30, 2006 at 5:55 am
Although it's an interesting thought experiment, I don't agree with using SQL logins, and I don't agree with anyone but the DBAs restoring databases.
Is there a good reason you want to do it this way?
March 30, 2006 at 10:01 am
A good reason? Not really. In our case, we're moving a legacy application up to SQL 2005, and we don't want to make any wholesale authentication changes until we stabilize on the new platform. Here, I'm allowing this fellow DBA-level access on this box to work the look-and-feel as he develops, and as such, don't want to have to have him chase me around to do restores.
Back to this proxy account: where would this be set up?
July 13, 2006 at 1:59 am
hi allan
quite an old topic but.....got exactly the same problem....did you find a solution to this?
greets
July 13, 2006 at 5:55 am
I didn't, unfortunately. At one point, I had grand ideas of taking this to our MS TAM and letting him open an SRX for me, but ended up taking care of this customer's restores myself, and the problem hasn't come up again (that customer is still our only 2K5 pilot). I definitely still need to pursue this, though - the ramifications in our shop are large.
Let me know if you turn up any decent solutions anywhere...
Regards,
Allan
July 14, 2006 at 5:52 am
i've tried the thing with "proxy enabled" under "Properities" of the instance in "Security" which was written in another posting to try. I've filled in my windows admin user an password as the proxy user (Attention: this has to be the same user as the sql services run under...otherwise sql wont start anymore...aarrgh)...
did not work...
April 24, 2007 at 9:56 am
Same issue - need non sysadmin to have the ability to restore backup files to any database
April 24, 2007 at 9:56 am
Same issue - need non sysadmin to have the ability to restore backup files to any database
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply