2

How can I encrypt a function in SQL Server so that it cannot be edited by another users? Thanks in advance.

1
  • 1
    Remove the privilege to view the function's for another users Commented Feb 9, 2016 at 1:26

2 Answers 2

6

Use the ENCRYPTION option:

CREATE FUNCTION testFunction ()
RETURNS int
WITH ENCRYPTION
AS
BEGIN
RETURN 1
END

This means the definition won't be stored anywhere that is easily readable, for example:

SELECT m.definition
FROM sys.sql_modules m
JOIN sys.objects o 
on M.object_id = o.object_id AND o.type = 'FN' AND o.name = 'testFunction'

will return null, and if a user (even sysadmin) tries to use SSMS to modify the function they'll get a MessageBox stating that the text is encrypted and can't be retrieved. However, per MSDN,

The definition of functions created by using the ENCRYPTION option cannot be viewed by using sys.sql_modules; however, other information about the encrypted functions is displayed.

and

Applies to: SQL Server 2008 through SQL Server 2016.

Indicates that the Database Engine will convert the original text of the CREATE FUNCTION statement to an obfuscated format. The output of the obfuscation is not directly visible in any catalog views. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration.

Using this option prevents the function from being published as part of SQL Server replication. This option cannot be specified for CLR functions.

You should still ensure that proper privileges are maintained to deal with such scenarios. You can read a bit more in depth about that here.

In the end, if this is meant to be shipped out to a client that you don't want to be able to access the source to, they'll be able to access it if they're reallllly persistent (using the debugger).

Sign up to request clarification or add additional context in comments.

3 Comments

Is there a way where I will just put a password (But not necessarily a password, just an example) on it and when I wanted to change something on it then I will just use that password? @Dan Field
Doesn't really work that way. You'd have a password on an account, and that account could belong to a role that's allowed to modify functions.. However, anyone with owner on the DB or sysadmin on the server will be able to DROP the function anyway.The only way to control that is to control access to the DB....
Ok. seems like you're right. I haven't found anything like that on the net too. But I do really hope I can find one.. haha.
4

You can't prevent other users from editing it, if they have the right permissions.

You can't prevent them from having the right permissions if they have physical access to the SQL Server.

  • They can simply drop it and then create whatever they want with the same name; thus having their own code take over for your original code

  • They can decrypt it in a variety of ways; see the dba.stackexchange question How to view an encrypted view or stored procedure

    • Red Gate SQL Prompt does it on its own

    • Replace the contents with the same name, then XOR the results to see the original

    • Get even trickier with looking into RAM.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.