Link to home
Start Free TrialLog in
Avatar of John500
John500Flag for United States of America

asked on

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
Avatar of Chris M
Chris M
Flag of Uganda image

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.
Avatar of John500

ASKER

>>  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
Avatar of John500

ASKER

Also, it would be very tedious to do this one by one... there are way too many.  This was done in some bulk fashion
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sorry ddl trigger
Avatar of John500

ASKER

Sorry so slow on this.  Can you give a brief example of such a trigger?

Thanks!

Avatar of John500

ASKER

Couldn't get to the bottom of it but thanks