setting permissions programatically 2

[esmithbda]

Is there a way that I can query all of the stored procedures and functions for LIKE '%ABC%' where "ABC" in this case is a unique string that all of our key procedures and functions have in their name - and then modify their permissions?

I am currently having to go through manually when we have new hires, which then become new database users, and it is a huge pain in the neck and I am sure there has to be an easier way than this - so the scripting way is the obvious first place I would look.

Thanks

 

[gmmastros]

I suggest you create a Role in your database. Assign permissions to the role as you need. Then, assign users to the Role. Then, when a new employee is hired, fired, terminated, or re-assigned, all you need to do is to add/remove this user from the role.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[esmithbda]

Fair enough - and we technically do have that in place.

Part of the issue is that we have N stored procedures and functions, where N is large enough that it is hard to keep track of them in a manner that is easy.

We have new functionality being added regularly, and depending on how busy we are, it is still very easy to lose track of what permissions got set - if any - for any given object.

If I can build a script that goes through all of them with the keyword and sets that role's permissions, then that is a huge help.

I agree that a role is a better system than users, but I think the programmatic change of settings is still really what I want here, regardless of whether a role or a user is what is getting the permission changed for the object.

 

[gmmastros]

Take a look at the information schema views.

Select * From Information_Schema.Routines Where Routine_Name Like '%abc%'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[esmithbda]

Thank you George, it looks as if from there I could do something similar to in here:

http://www.sqldbatips.com/showarticle.asp?ID=8

Thanks for the help!