Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
shezad
Starting Member
32 Posts |
Posted - 2013-03-19 : 02:37:01
|
Hi,I have couple of queries1) What is the way to encrypt any stored procedure or function2) once it is encrypted it is possible that anyone can decrypt it3) How to make it Hidden or prevent it from delete Regards |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-19 : 02:44:11
|
1) What is the way to encrypt any stored procedure or functionWITH ENCRYPTION option is available2) once it is encrypted it is possible that anyone can decrypt itYou can't decrypt the encrypted procedure/function--Chandu |
|
|
shezad
Starting Member
32 Posts |
Posted - 2013-03-19 : 02:51:43
|
HiThanks for the response.I tried this and it is working only thing is that it is visible and anyone can drop or who has privileged for delete can drop it. How it is possible to prevent user from doing this |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-19 : 02:53:05
|
For third question, you can write a DDL trigger to prevent permission of any DDL of procedure as follows:CREATE TRIGGER [block_ddl] ON DATABASE FOR ALTER_PROCEDURE, DROP_PROCEDURE[, ALTER_TABLE, DROP_TABLE, ALTER_FUNCTION, DROP_FUNCTION, ALTER_INDEX, DROP_INDEX, ALTER_VIEW, DROP_VIEW, ALTER_TRIGGER, DROP_TRIGGER] AS BEGIN -- RAISE error so that we can prevent drop procedure END EDIT: Check the following link for further informationhttp://www.mssqltips.com/sqlservertip/2646/using-a-ddl-trigger-to-block-schema-changes-in-sql-server/--Chandu |
|
|
shezad
Starting Member
32 Posts |
Posted - 2013-03-19 : 04:04:37
|
Hi Chandu,that newly DDL trigger can also modifiedthere is nothing which will prevent it from deleting or modifying it.Am i right or wrongRegards |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-19 : 05:09:48
|
You can provide WITH ENCRYPTION option so that no one can alter that procedure....and you can prevent users from DROP PROCEDURE by creating DDL triggerGOCREATE PROCEDURE testProcWITH ENCRYPTIONASBEGIN select 'This is test proc for preventing users fron droping this procedure'ENDGOCREATE TRIGGER Utr_deny_DROP_ProcedureON DATABASEFOR DROP_PROCEDUREASBEGIN RAISERROR('Can not drop This Procedure', 10, -- Severity. 1 -- State. );ENDGODROP PROCEDURE testProc EDIT: you can't drop any procedure in the database unless you alter Utr_deny_DROP_Procedure trigger--Chandu |
|
|
|
|
|
|
|