Monday, November 27, 2006

SQL 2005 : CLR - External Access permissions

Msg 6522, Level 16, State 1, Procedure SP_Backup, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "SP_Backup":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at DatabaseName.dbo.SP_Backup(Parameter1, Parmeter2, Parameter3...)


The CLR stored procedure is using XP_CMDShell to access the file system.

The permissions on the assembly therefore need to be increased to allow this.


Changing assembly permissions in Management Studio -


1) Navigate to (databasename) > Programmability > Assemblies > (expand)
2) Double click assembly name to get assembly properties.
3) Set permission set of assembly to 'external access'.


Changing assembly permissions in tsql -
GRANT EXTERNAL ACCESS ASSEMBLY TO AssemblyName

No comments: