SQL Server Performance

Log in or Sign up

Granting permissions to view stored procedures

Discussion in 'SQL Server 2005 General DBA Questions' started by silktaiji1, Sep 7, 2007.

silktaiji1 New Member

I need to grant permissions to a login, and the login only needs to view stored procedures (contents not just names of SP's). I don't not want to grant him db_owner nor ddladmin roles. I granted him execution privilege, but he can only sees names of SP's not scripts.
Any input will be helpful. Thanks.

silktaiji1, Sep 7, 2007

#2
ghemant Moderator

Hi,
You may "grant execute" permission on sp(s) you want login to execute, also ensure that user have permission to read underlying table(s).
USE AdventureWorks; GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo TO Recruiting11;GO
Or you may grant him db_datareader and refer http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritythedb_executorrole.asp

ghemant, Sep 10, 2007

#3
satya Moderator

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

satya, Sep 10, 2007

#4

(You must log in or sign up to reply here.)

Share This Page