December 4, 2008 at 6:29 am
We are looking to only allow our developers certain access to our new SQL 2008 databases. We'd like them to have read only access to the tables, but also be able to view stored procedures, views and functions. They don't need execute on the SPs but if that would be our only option that would be ok as well.
Any insight? I can't find specific Read Only access to stored procedures...
Thanks in advance!
December 4, 2008 at 8:19 am
Permissions of Fixed Database Roles
http://msdn.microsoft.com/en-us/library/ms189612(SQL.90).aspx
http://sqlserver-qa.net/blogs/perftune/archive/2007/09/10/1960.aspx
As usual if that login needs the execute permission you have to "grant execute" permission on sp(s) you want login to execute, also ensure that user have permission to read underlying table(s), for instance:
USE AdventureWorks;
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo
TO Recruiting11;
GO
Whereas to view the text within the SQL 2005 you need to grant them VIEW DEFINITION on the procedures. To make it simpler you grant them this permission on the schema:
GRANT VIEW DEFINITION ON SCHENA::dbo TO someuser
EXECUTE all stored procedure can be easily Google'ed (db_executor role)
December 4, 2008 at 2:18 pm
Excellent! This worked great!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply