Error Message from CLR assembly on SQL Server 2005 Restored Database
Okay, you may run into the situation where you restore a database from Server A onto Server B and the database contains CLR assemblies. When you try to execute a given CLR assembly it blows an error similar to the one below.
Msg 10314, Level 16, State 11, Line 2
This occurs even though you have the database Trustworthy property set to ON and the assembly is set to either external_access or unsafe for the permission level. Additionally, it will not matter if you are the sysadmin or not. So what gives since the assembly worked just fine on the first machine?
Well, it seems that the packages are pretty attached to their owners. So if the owner whom originally created the package on Server A does not have an account on Server B then it goes haywire. Microsoft has kind of a hokey way to get around this in copying over the account and then matching up the SID and giving the account the proper permissions on the database instance.
This is outlined in the following kb article
However, a much easier workaround is just to simply script out the dropping of the procedures and functions that rely on the assembly as well as the assembly itself. Then script out a set of create statements to recreate them. Put it all together and run it under a new account on the new server and you are good to go.
In practice a better method of handling this is to ensure that your assemblies are deployed with an AD service account with permissions on the SQL Server. The AD accounts are easier to handle in this scenario because you just have to make sure that the service account is on the new database instance with the proper permissions. No synching of SIDS is required.
Hopefully, this post saves someone a little bit of time down the road…..