execute permissions

[regulardude]

Hi,

I have a couple of questions regarding permissions and security.

First, I have read many times, that a wise practice is to give your permissions through stored procedures, in other words, give execute permissions on stored procedures, and limit or do not allow direct access to tables and other objects.

How would I give a user or a role(and then add the user to the role) permission to execute all stored procedures, preferably a blanket statement(GRANT EXECUTE ANY... not a script I have to run everytime I create a new stored procedure)?

Second,
And here is the rub, shouldn't that user/role be able to drop a table/delete records/ perform any functionality if it is within the stored procedure they have execute rights on? With the exception of dynamic sql, which I understand.

Thanks for anyone's input in advance.

 

[mrdenny]

1. If you have SQL 2005 then you can grant execute rights to the schema. This will grant the user or role rights to execute all objects within the schema without having to be given rights to the object directly. However this will give them rights to all procedures, functions, etc; not just the stored procedures.

If you have SQL 2000 no, you'll need to grant the rights to each procedure manually.

2. Yes, to an extent. By having rights to the stored procedure the user will automatically be able to do anything within the database that the procedure is in, provided that it's on DML (SELECT, INSERT, UPDATE, DELETE). Rights to DDL (CREATE TABLE, DROP TABLE, etc) are not handled this way. In order to execute a stored procedure with a CREATE TABLE script in it you still have to have the CREATE TABLE right.


Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[regulardude]

mrdenny,

Thank you. Your response is what I have experienced thus far.

So, since a lot of our stored procedures create tables and drop them, if you don't mind, can you share your method for giving just the right amount of permissions, in this circumstance?

Thanks again.

 

[mrdenny]

In SQL 2000 you have to grant the user the CREATE TABLE and DROP TABLE rights. In SQL 2005 you can either do this, or have the part of the code which does the CREATE TABLE and DROP TABLE operation executed by another user such as dbo by using the EXECUTE AS command within your stored procedure.

Keep in mind that this isn't needed for temp tables or global temp tables.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[regulardude]

Thanks MrDenny, I will work with this information.


What is the difference between:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

And

GRANT SELECT ON SCHEMA::SchemaName TO UserName

Thanks again.

 

[mrdenny]

The first piece of code grants selective access to the single procedure.

The second piece of code grants select rights to all objects in the schema to the user.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog