How can I encrypt a function in SQL Server so that it cannot be edited by another users? Thanks in advance.
2 Answers
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).
3 Comments
DROP the function anyway.The only way to control that is to control access to the DB....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.