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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Encypt function

Author  Topic 

shezad
Starting Member

32 Posts

Posted - 2013-03-19 : 02:37:01
Hi,

I have couple of queries

1) What is the way to encrypt any stored procedure or function

2) once it is encrypted it is possible that anyone can
decrypt it

3) 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 function

WITH ENCRYPTION option is available

2) once it is encrypted it is possible that anyone can
decrypt it

You can't decrypt the encrypted procedure/function


--
Chandu
Go to Top of Page

shezad
Starting Member

32 Posts

Posted - 2013-03-19 : 02:51:43
Hi

Thanks 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
Go to Top of Page

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 information
http://www.mssqltips.com/sqlservertip/2646/using-a-ddl-trigger-to-block-schema-changes-in-sql-server/
--
Chandu
Go to Top of Page

shezad
Starting Member

32 Posts

Posted - 2013-03-19 : 04:04:37
Hi Chandu,

that newly DDL trigger can also modified
there is nothing which will prevent it
from deleting or modifying it.

Am i right or wrong

Regards
Go to Top of Page

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 trigger


GO
CREATE PROCEDURE testProc
WITH ENCRYPTION
AS
BEGIN
select 'This is test proc for preventing users fron droping this procedure'
END
GO
CREATE TRIGGER Utr_deny_DROP_Procedure
ON DATABASE
FOR DROP_PROCEDURE
AS
BEGIN
RAISERROR('Can not drop This Procedure',
10, -- Severity.
1 -- State.
);
END
GO
DROP PROCEDURE testProc


EDIT: you can't drop any procedure in the database unless you alter Utr_deny_DROP_Procedure trigger
--
Chandu
Go to Top of Page
   

- Advertisement -