January 9, 2009 at 3:13 pm
I'm attempting to provide access to a developer to create new databases and restore databases from backup files. Thus far, I've made the developer a member of the dbcreator fixed server role.
Unfortunately, the developer is attempting to perform these operations from Management Studio and it appears that a higher level of permissions than db_creator is required. When attempting to create a new databases, the developer is receiving an error that he doesn't have access to the model database. When attempting to select a backup file to restore, the error is on execute access to xp_availablemedia in the mssqlsystemresource database.
When I've attempted to research this through Books Online, I'm having a difficult time determining exactly what permissions are granted to each server role. For dbcreator, only "create database" is listed. Since I can't assign permissions directly to stored procedures within the mssqlsystemresource database or view permissions on the extended sps there to determine who has permissions to execute them, I'm a bit stuck.
Any experiences with providing this type of limited access would be greatly appreciated.
Thanks
September 25, 2009 at 12:55 pm
Hi dcmccue,
Any luck with your question, I am coming up with the same issue. So far, I have an SP with execute rights to a user create backup files and restore.
However I'm coming up with an issue for the user to access the database after the restore. I think this would work if the user account/password info was the same on both Prod. and Dev. which is something I want to avoid.
Do you know of a way to resync logins from a non-priviledge account?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply