loosing permissions on stored procedures

Question

loosing permissions on stored procedures

lfarruggia

SSC Enthusiast

Points: 172
More actions
July 19, 2008 at 11:17 am

#195519

Can anyone help?
I am using MS SQL 2000. I have tables set, views, relationships etc.
I am also using a program called Iron speed which writes code for based on my tables. Each time I do a build it wipes out the permissions on all my stored procedures.
The vendor says that I have to make a setting in the main database of "0" or something. Be darned if I know where to look.
Where do I set permissions on base DB that will be inherited to all tables and procedures below?
Thank you so much!!
It's a nightmare to make one change, re-build, only to re-apply all exec permissions on stored procedures just to see if my change worked.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

IThere is no database setting that will prevent loss of permissions if a stored proc is dropped and re-created. maybe its some setting in their app that changes the drop\re-create into an alter procedure statement which would then preserve the permission settings.

alternative is to reverse engineer out the permissions in the databse before the rebuild and then apply the script. There are plenty of examples of code to do this on this site

---------------------------------------------------------------------

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 2

The only way I can think of would be to create roles with the appropriate schema level permissions. Once you have a role defined, and assigned the users/groups to the role - then:

GRANT EXECUTE ON SCHEMA::schema TO role;

With the above, a drop/recreate of a stored procedure in the defined schema will not be affected and the role will still have the necessary rights to execute the procedures.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

lfarruggia SSC Enthusiast Points: 172 More actions · Answer 3

THank you I will give this a try. It sounds like the roles should work!!:P

george sibbald SSC Guru Points: 104210 More actions · Answer 4

this is in the 2005 forum but you stated you are using sql 2000, in that cases you cannot use schemas

---------------------------------------------------------------------

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

george sibbald (7/20/2008)
this is in the 2005 forum but you stated you are using sql 2000, in that cases you cannot use schemas

George - I must of skipped right over that part of the message and just noticed the forum. You are correct and schemas cannot be used on 2000.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

lfarruggia SSC Enthusiast Points: 172 More actions · Answer 6

Thanks All, so far no luck, if anyone has any ideas on what I can do please let me know.

I am wondering if there is a procedure that can be written to apply permissions automatically anytime procedures are added or changed?

Any experts out there?

mforbes Say Hey Kid Points: 695 More actions · Answer 7

There are ways to get the same effect as schema security in 2000 but Since you are using Iron Speed I would say your options are limited. One way to handle this is after your build use a stored procedure that grants execute to the user you want for all the stored procedures in that database. This is not automatic but only takes a second to run. If you are interested in this approach I can post the code for this procedure or send it to you.