Granting VIEW permissions 1

[katgirl]

Hi,

I would like to grant VIEW stored procedure permissions to a user in the database. I would like the user to see ALL the stored procedures in the database, just not change them in any way.

So far I have determined I can use this stmt to accomplish this task:

GRANT VIEW DEFINITION ON OBJECT:atabaseNAME.usp_StoredProc
TO User;
GO

But I am wondering - do I have to run this statement for each and every stored procedure I wish to grant user permissions on? Or is there a single stmt I can use? Like using "ALL" or something? Just want to clarify before I proceed.

Thanks

 

[mrdenny]

You would need to use that statement once for each procedure.

You can however grant VIEW DEFINITION rights to the schema that the objects are in. This will allow the user to see the definition of all objects in the schema.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katgirl]

Hi mrdenny,

So you're saying - if all the stored procedures are owned by dbo, for example, I need only grant permissions to view the dbo schema?

Do you have an example of how to do this? I don't fully understand schemas, but they give me a lot of grief.

Thanks much

 

[mrdenny]

Yep.

GRANT VIEW DEFINITION ON SCHEMA::[{SchemaName}] TO [{UserName}]

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com