December 10, 2016 at 7:58 pm
To comply with security, we recently had to execute the following commands on SQL Server 2014
Revoke execute on xp_regread to public
Revoke execute on xp_instance_regread to public
Now our developers are not able to access tables in the production databases and he gets an error like
The EXECUTE permission was denied on the object ‘xp_regread’, database ‘mssqlsystemresource’, schema ‘sys’.
The EXECUTE permission was denied on the object ‘xp_instance_regread’, database ‘mssqlsystemresource’, schema ‘sys’.
So I thought to create a server role called xp_regread_SVR_Role add the developer group as a member and grant it execute to the xp_instance_regread and xp_regread, but I could not do this
I would run
Grant execute on xp_regread to xp_regread_SVR_Role –-(the svr role I created)
Grand execute on xp_instance_regread to xp_regread_SVR_Role
I receive a messages “… either you do not have the permission or xp_regread_svr_role does not exist”
I ran these
Grant execute on xp_regread to public
Grand execute on xp_instance_regread to public
And they completed successfully and public is a server role
I have not had to deal with privileges at such a granular level so I am a newby at this part, but
Can I grant execute to a database object to a server role?
What can I do to work around the revoke of these two extended stored procedures
So I was able to complete the following
Grant execute on xp_regread to domain/username
Grand execute on xp_instance_regreat to domain/user
Completed successfully
But the developer was still not able to access objects in the instance.
Any help is appreciated
Jeff
December 13, 2016 at 7:20 pm
I actually figured out the solution to this
First: The revoke was actually a deny xp_regread and xp_instance_regread to public rather than revoke and deny cannot be over ridden by a grant when we do the remaining steps
We ended up doing a grant ... to public and then we did a revoke ... to public
We then created a role in the master database called xp_regread_db and granted it execute on the xp_regread and xp_instance_regread and then added the logins to that role and it worked out
Jeff
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply