Can a user be granted Read Only privileges for stored procedures

Can a user be granted the ability to create stored procedures with read only capabilities? I wouldn't mind be able to UPDATE but simply need to read only.
ddaveI don't understand...

You GRANT EXECUT to sprocs...

Do you mean to the underlying tables?

Personally...

In our Prodction environment, we hand the scripts over to the prod dba...

the run the script and it gets created as dbo...

users are granted execute to the sprocs...|||Hi Brett,

In one of the companies I am working for I am requesting the ability to create stored procedures. The data is extremely sensitive however. It is for a financial company where access to this data would allow someone like myself to alter data, ie wire myself money. I don't want that to be a concern of course so I was wondering if I could run stored procedures with the ability to read but not alter the data. Is there such a thing? Thanks.

ddave|||Yes,

But it's at the table

either

GRANT SELECT ON dbTable TO yourID

Or have the dba put you in a role...

That's what I'd do...
set up a role...call readonly or whatever..

GRANT SELECT on all tables

SELECT 'GRANT SELECT ON ' + TABLE_NAME + 'TO readonly'
FROM INFORMATION_SCHEMA.TABLES

And just put your id in that role...

makes managing alot easier....|||One slight problem, though. If you have a table that you can only read, you can still execute procedures that update/insert/delete that table. The select permissions that you are granted only really extend to the actions that you yourself are running (outside stored procedures).

This of course only applies to objects with an unbroken chain of ownership. If you put yourself in a role that has been denied update, delete, insert on the specific tables, then you could create procedures in your own schema that do almost anything. But you yourself (and anyone running them) would be limited by their own permissions on the underlying tables. This is not a fun place to be for a livinig system that will have various coders running around tieing their applications, reports, and what-not into various bits of code that were left lying around.|||Well just before the database crashed...AGAIN...

I was going to say I agree...

Don't you have a dev environment?

playing in production is not a good thing...

oops

Now what did I do with that backup?|||Does the db_datareader (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_9nla.asp) role fit the bill?

-PatP|||No, I don't have a dev environment. How do you get one? The company I work for is huge but this is one small department granting me the ability to access this data. I know that there is a lot for them and myself to learn.

ddave|||Keep in mind that (at least for the most part) the permissions used by a stored procedure are the permissions of the user that creates the stored procedure. There are a few exceptions, but very, very few. The procedure's EXECUTE permission is the only permission that matters to the user that runs the stored procedure.

This allows a "super user" like dbo to create a procedure that can do almost anything, then give other "underprivledged" users permission to execute that procedure.

-PatP|||Do you have a dba?

If I were you, I'd make SURE that I didn't get blamed for ANYTHING...

and that means staying out of production, and be isolated in another environment...

What do you have to do?|||So Pat,

What you're telling me is that since my contact in this other department has the ability to EXECUTE stored procedures, it's either feast or famine. I will either be able to run sprocs AND perform DML commands or not based on whether he GRANTs me these permissions or not. Is this correct? Thanks.

ddave|||Sorry, but SQL Server security isn't quite that simple.

When a stored procedure is created, the database engine checks the permissions of the creator to perform all of the operations in the procedure. If the creator is lacking any of the permissions needed for any statement in the stored procedure, it won't compile successfully.

When a stored procedure is executed, the database engine only checks to see if the current user has permission to execute the stored procedure.

This allows a privileged user like dbo to create a stored procedure that can be executed by an "underprivileged" user that contains DML that the user could not execute directly. It allows the creator to "delegate" their privileges in a controlled way.

There are a few exceptions to this, but the only exception that matters in many cases is dynamic SQL. SQL Server executes dynamic SQL in a different context, almost like the current user created a new spid for the dynamic SQL. That is why @.variables can't be referenced in dynamic SQL, and it is also why the user needs to have permission to execute any DML statements that are executed dynamically.

-PatP|||Brett,

By the question "What do you have to do?" I take you mean what is my job here? It is simply creating reports for loan data. There are several steps required but basically updating data from a temporary table that has its data destroyed every fifteen minutes, deduping, grouping, and sorting and that sort of fun stuff.

I don't think there will be a place to hide. I will either be granted privileges to do this or not.

ddave|||If you are working for a large company there has to be a DBA or at least an IS contact who is responsible for this. You need to have them give you db_datareader access. You can then create procedures and have THEM place them on the production server after reviewing them.

And, tell them to get a development environment. If they are a large company, I'm sure they can afford it.