Don't Allow SP to Modify | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Don’t Allow SP to Modify

We can not modify the SP’s in Master or other system databases. The same way, Is there any way to create only readable/executable SP in user database.
Just control the permissions properly. Make sure no users have the ability to ALTER or DROP the procedure. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Additinaly, You can use encyption option to encrypt the sp
But before doing this you need to save sp script so that it can be used for future midifications
quote:Originally posted by surendrakalekar We can not modify the SP’s in Master or other system databases. The same way, Is there any way to create only readable/executable SP in user database.
Yuck!! [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] Encrypting of stored procedures in SQL Server is more of an annoyance for maintenance than anything else. It’s easy to break into them. You can download free tools to decrypt them off the internet easily.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Oops!!! [:0]<br />Do you know any link to a such tool.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by derrickleggett</i><br /><br />Yuck!! [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] Encrypting of stored procedures in SQL Server is more of an annoyance for maintenance than anything else. It’s easy to break into them. You can download free tools to decrypt them off the internet easily.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
http://ussun.com/decryptor/
http://www.fileheaven.com/Orbital’s-SQL-Decryptor/download/12294.htm
http://www.soft14.com/Software_Deve…Networks/SQL_Script_Recovery_5485_Review.html
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
[^]
Then no point of encrypting objects
Thankx derik
Thanks Derrick and Dinesh,
I create one SP by user sa. I had given only Exec permission to user ta. After that I checked with the ta user. But it allow to modify that SP. I don’t know what is wrong.
Check the Grant in BOL,
Accoding to that u assign ta to relevent role
quote:Originally posted by surendrakalekar Thanks Derrick and Dinesh,
I create one SP by user sa. I had given only Exec permission to user ta. After that I checked with the ta user. But it allow to modify that SP. I don’t know what is wrong.
What server and database roles is the user in? Also, look at the entire permissions of the user for that object. What does he have there? Have you granted the public role more access than it should have? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Thanks,
I found my bug. It was not working because ‘ta’ user was the dbowner for that database. After removing that permission it starts working.
Now, it is not allowing ‘ta’ user to modify sp. It shows one messagbox (as a critical) after clicking on apply button. "Error 3704: User does not have permission to perform this operation on procedure ‘DBO.PermissionTestSP’."
We cannot disable the SP like Master Database?
]]>