SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Encypt function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shezad
Starting Member

32 Posts

Posted - 03/19/2013 :  02:37:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/19/2013 :  02:44:11  Show Profile  Reply with Quote
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 - 03/19/2013 :  02:51:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/19/2013 :  02:53:05  Show Profile  Reply with Quote
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

Edited by - bandi on 03/19/2013 02:54:28
Go to Top of Page

shezad
Starting Member

32 Posts

Posted - 03/19/2013 :  04:04:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/19/2013 :  05:09:48  Show Profile  Reply with Quote
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

Edited by - bandi on 03/19/2013 05:15:53
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000