Execute permissions on SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Execute permissions on SP

Author

Topic

beachtime1999
Starting Member

25 Posts

Posted - 2008-03-12 : 10:55:07

In this example ….. I have a sql user id called 'toronto' with the permissions it acquired by being added to the db_datareader (READ) & db_datawriter (ADD, CHANGE, DELETE) database roles in the 'getranet' database.
However, the 'toronto' account is unable to execute or run any of the 240 stored procedures in the 'getranet' database with only these permissions, at least that's the results I'm getting.

Solution #1: If I add the toronto id to the db_dbowners role (PERFORM ANY ACTIVITY) in the 'getranet' database, the problem is resolved. I would rather not use this approach because with these permissions the id can delete the db, or tables etc… and I'm not the only one with the id and password.

Solution #2: I created a new database role (SP_EXECUTE) in the 'getranet' database, and granted the role 'execute' permissions to all 240 stored procedures in the database, added the 'toronto' id to the SP_EXECUTE role, the problem is resolved.
This solution works fine but it will require more maintenance, anytime a stored procedure is added or modified I will have to remember to update the SP_EXECUTE database role in the getranet database. Hey, I will forget once in a while, apply thumb screws here.

So my question is …. Am I approaching this all wrong? Is there a 3rd, 4th or a better solution (White Elephant) that I just can't see?
I just thought I would ask…..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-12 : 11:03:15

Apply execute permission on all 240 SP so that you don't have to create roles.

beachtime1999
Starting Member

25 Posts

Posted - 2008-03-12 : 11:26:43

If I create the Role the next time I get another account that need execute I just add them to the role.......

Public can't be manipulated to have execute all procedures can it?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-12 : 12:07:24

That's Right but when new SP are added, you have to update that role.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-12 : 22:56:38

>> Public can't be manipulated to have execute all procedures can it?

Yes you can, but that will apply to all db users.

beachtime1999
Starting Member

25 Posts

Posted - 2008-03-13 : 12:23:02

IS THERE AN EASY WAY TO GRANT EXECUTE TO PUBLIC? HOW?

WILL IT BE ABLE TO EXECUTE ANY NEW PROCS ADDED LATER?