How to lock down objects like stored procedures without using object level permissions (SQL 2008)

There is a way to lock down stored procedures so that it is impossible to modify them even if you have system administrator priviledges.

As a new person in a particular company I was given the sa password but later found out I could not modify the stored procedures because all stored procedures had that option dimmed out.  If you right clicked on the sp, the pop up menu displayed the 'Modify' option but it was dimmed out.

Thus, how could it be possible to have such an option dimmed out to a system administrator?

Thanks
John500Asked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
read-only is suspect... but a dml trigger could also disallow changes to procedures
0
 
Chris MConsulting - Technology ServicesCommented:
Maybe the database was read-only mode.
The SA has all permissions to modify any object of the database.

In SQL 2005, you can rename the SA account. I hope the SA account was not just renamed and an underprivileged account named "SA" created for you (lol).

Anyway, I suspect that you check the database properties and see if it's not set to read-only.
0
 
John500Author Commented:
>>  I hope the SA account was not just renamed and an underprivileged account named "SA" created for you (lol).
That is pretty funny ! lol

However, I asked the previous SA and the answer was, "I locked them and will unlock them".   Since there was no additional provided, I didn't beg for it.

>>  I suspect that you check the database properties and see if it's not set to read-only.

How would I set and check the read-only property for a stored procedure
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
John500Author Commented:
Also, it would be very tedious to do this one by one... there are way too many.  This was done in some bulk fashion
0
 
LowfatspreadCommented:
sorry ddl trigger
0
 
John500Author Commented:
Sorry so slow on this.  Can you give a brief example of such a trigger?

Thanks!

0
 
John500Author Commented:
Couldn't get to the bottom of it but thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.