Stored Procedure & Security

[darude]

Good Afternoon All,
I have sort of a dumb question. I have created about 6 stored procedures to run a new piece of our Access app. Most of the stored procedures run fine, there is 1 that won't execute at all. I have been rewriting the proc and the Access call. I finally I granted permissions to the user on the stored proc and it worked.

Is this something I should do for every stored proc?

Thank you in advance.

 

[RiverGuy]

Is this something I should do for every stored proc?

Not specifically. You should come up with a security policy/schema for your database in general.

One example of part of that policy could be to set up roles in your database. You can grant permissions to roles, and add users to roles. If your current user is replaced in the future, you don't have to then go through every stored procedure granting access to his or her replacement. You can simply add the replacement to the desired role.

 

[gmmastros]

You should come up with a security policy/schema for your database in general.

I agree.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[darude]

Thanks for responding. So, for example I have a security group called CareerSpecialists which contains a list of users. To make the stored proc work I opened the permissions page in the stored procs properties and added this group and selected grant and execute. I now only have to remove a user from this group and not much else. Is this correct or is there another way?

 

[RiverGuy]

Yes, although keep in mind that removing a user's access to a role or to database(s) is not a substitute for disabling or removing their SQL login or AD account if the employee is terminated.

 

[darude]

Got it. Thank you so much.