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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LowfatspreadCommented:
read-only is suspect... but a dml trigger could also disallow changes to procedures
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.