Search code examples
sql-serversqlclr

SQL Permissions Levels Needed to Deploy SQL CLR

I'm trying to deploy a quick test of a locally developed C# SQL-CLR stored procedure and assemblies to a DEV box and am getting various denied access messages.

The reverse engineering operation cannot continue because you do not have View Definition permission on the database.

Q: What permissions group or level do I need on the server to deploy SQL CLR?

Q: The message above implies DDLAdmin, but do I need any more?

VERSION: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)

I'm having trouble getting a list of minimum permissions needed from folks at work.

Thanks for the help.

Solution

  • The message above implies DDLAdmin

    No, it doesn't imply that. It clearly states that the Login needs VIEW DEFINITION. While ddl_admin might work, it might be much more than is necessary. However, that being said, why aren't you deploying as a privileged account? I have rarely, if ever, seen a deployment process that didn't run as sysadmin. That and how are you not a sysadmin on a dev box? ;-)

    There are two issues at hand:

    1. The SSDT process which does the reverse-engineering so that it can come up with an incremental deployment script, but is not making any changes at this point, and

    2. the act of publishing / deploying the script once it is generated.

    Your error is about part 1, but the question is, at least in how it is worded, about both parts.

    So to address part 2, you need CREATE permission (whether per object, or on the schema, or via a fixed DB role like ddl_admin) to create the objects. You also need to be able to ALTER the Assembly. Regarding the ALTER permissions for the Assembly, the MSDN page for ALTER ASSEMBLY states:

    Requires ALTER permission on the assembly. Additional requirements are as follows:

    • To alter an assembly whose existing permission set is EXTERNAL_ACCESS, requires EXTERNAL ACCESS ASSEMBLY permission on the server.
    • To alter an assembly whose existing permission set is UNSAFE requires UNSAFE ASSEMBLY permission on the server.
    • To change the permission set of an assembly to EXTERNAL_ACCESS, requires EXTERNAL ACCESS ASSEMBLY permission on the server. To change the permission set of an assembly to UNSAFE, requires UNSAFE ASSEMBLY permission on the server.
    • Specifying WITH UNCHECKED DATA, requires ALTER ANY SCHEMA permission.

    IF the Assembly is to be created with a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE, then prior to creating the Assembly, you should have signed the Assembly (might be a good idea in any case), then create an Asymmetric Key in [master] from that Assembly, then create a Login from that Asymmetric Key, and finally grant that Login either EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY. Do not set the DB containing the Assembly to TRUSTWORTHY ON unless you absolutely must, such as in the case of loading an unsupported .NET Framework library since you cannot re-sign those.

    For a step-by-step guide on handling the creation of the Asymmetric Key and Login while using Visual Studio / SSDT, please see the 3-part article I publish on SQL Server Central that addresses this and other nuances of VS and SSDT:

    Stairway to SQLCLR Level 6: Development Tools Intro (free registration is required by this site).

    Level 7 specifically deals with the security / Asymmetric Key / Login stuff, but it might be best to start with Level 6 as it is the setup for Levels 7 and 8 (it was really one long article that had to be broken up ;-).