Urgent: Stored Procedure permissions question 1

[katgirl]

Verion: SQL Server 2005

I have a database user that has db_datareader and db_datawriter permissions in the database.

However, when the user attempts to execute a stored procedure in the database (via an application), it is denied execute permission.

I fixed the problem by adding granting the user execute permission on the stored procedure.

Which raises the question - why doesn't db_datareader/db_datawriter role allow execute permission on stored procedures?

Am I going to have to add users to every single stored procedure in the database? Or is there a better way to handle this?

Need to know!!

THANKS much

 

[mrdenny]

db_datareader and db_+datawriter don't grant rights to stored procedures. It only grants rights to read and write to the tables and views.

I would recommend creating a new database role, and granting it the ability to execute the stored procedures. After that add the users into that role and they will be able to execute the procedures.

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