CLR Sproc Permissions

Question

When I'm trying to execute a clr sproc (Still in C# IDE for this) I have permission errors. The error is " Request for the permission of type 'System.Data.SqlClient.SqlClientPermission,... failed", I've looked around online and it seems to throw back to IIS settings (I'm not using IIS nor am I using a website so these solutions don't apply to me), now I tried two separate connection strings for this as well (context connection = true, as well the way I usually do it which is the huge long one that says it's a trusted connection and the database name) but both return the same permissions failed.

Any help would be greatly appreciated

Answer 1

Speak with your DBA regarding your users database permissions. This is probably not an issue in code.

Answer 2

Is CLR enabled within your instance of SQL server?

You can enable it by running the following SQL statement against your SQL server:

sp_configure 'clr enabled', 1 GO

Once this is ran, the assembly needs to be registered to SQL:

CREATE ASSEMBLY ASSEMBLYNAME FROM 'Full path to assembly' --e.g. C:\sproc.dll WITH Permission_SET = SAFE GO

Create the stored procedure like this (if you haven't already) CREATE PROC clrsp_InsertEmployee (
Comma delimited parameters ) --Parameters example @City VARCHAR(25), @state VARCHAR(2)
AS EXTERNAL NAME AssemblyName.StoredProcedures.Function Go

Let me know if this helps. Other than that, you may need to setup permissions to the tables based on the user that you are utilizing to access SQL.