Can''t deploy "unsafe assembly", permission problem(?)

Hello,

I think I have some kind of permission problem. But first things first:

I have code which I would like to run in SQL Server (CLR Integration). First thing is that my code uses third-party-dll. I have to deploy my code as unsafe because of

"

CREATE ASSEMBLY failed because method "add_FunctionAdd" on type "USP.Express.Pro.FunctionsCollection" in safe assembly "USP.Express.Pro.2.0" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies.

"

Of course I can not create "asymmetric key" for third-party-dll (Or can I?).

So, I tried to use trustworthy DB. But I get all the time error Msg 10327: "Assembly is not authorised for PERMISSION_SET=UNSAFE"

I am using Windows Login to log on Sql Server. Login is granted "Unsafe assembly" and DB has trustworthy setting "on".

Login has server roles "sysadmin" and "securityadmin".

Login is mapped with DB User who has same name ( DOMAIN\UserName ) and has default schema "dbo".

Login has DB memberships "db_owner" and "db_securityadmin".

DB user owns schemas "db_owner" and "db_securityadmin".

Am I missing something?

Interesting thing is that I can do deployment (as unsafe assembly) in master-database. But not in the other databases.

Questions are:

- Is there other way to authorise third-party-dll than using trustworthy?

- Why deployment can be done in master-database?

And finally:

- Why deployment can not be done in other database?

So when you are deploying an assembly with the UNSAFE permission set (or EXTERNAL_ACCESS) and are using the TRUSTWORTHY settings (i.e. not assymetric keys etc), the login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY.

Niels
|||

Hello Niels,

" login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY."

As far as I know, permission _should_ be OK.

I checked following:

1. database -> properties (the database I want unsafe assembly to be created): owner is <DOMAIN\username>.

2. corresponding database user -> properties: Login name is same.

I have done "Grant UNSAFE ASSEMBLY to <DOMAIN\username>;" and "ALTER DATABASE <database> SET trustworthy on". Both are executed without errors.

<DOMAIN\username> is the Login name of owner of <database> and <database> is the db I want my assembly to be created.

Is there something still missing?

What I should check next?

- Jorma

|||Hmm, what is the exact error message you are getting? Is this database a new database, or is it restored from (perhaps) SQL 2000? What happens if you were to create a new database (with DOMAIN\username as owner), setting the database to TRUSTWORTHY and deply the assembly?

Niels
|||

Hello again,

Error message is:

Msg 10327, Level 14, State 1, Line 1

CREATE ASSEMBLY for assembly 'MillCC' failed because assembly 'USP.Express.Pro.2.0' is not authorized for PERMISSION_SET = UNSAFE.

"USP.Express.Pro.2.0" is the third-party-dll my code is using. Previously message were same but assembly named in the message was my code... Interesting.

I created new database as you suggested. And assembly was created without errors. Why in the world I didn't try this before... So, problem is somehow related with the database I am using.

The database is restored from SQL Server 2005 Express Edition.

Is there something I should know when using restored database and CLR?

|||Ah, it is restored. Try this for me please:
1. Run ALTER AUTHORIZATION against the database and change it to [Domain\username] (I know that you said that was the owner, but do it once again, please.
2. Run ALTER DATABASE and reset TRUSTWORTHY to on
3. Try and deploy

Niels
|||

I tried

Alter authorization on DATABASE::<database> to <[DOMAIN\username]>

but there came out following error:

Msg 15110, Level 16, State 1, Line 1

The proposed new database owner is already a user or aliased in the database.

Should I remove something before running ALTER AUTHORIZATION? I am quite confused about the permissions at this stage |||OK, drop the user in the database, which is the user for the login [Domain\username], then run the ALTER AUTHORIZATION again.

Alternatively, you can create a SQL login (just a login) and make that login the owner of the database and assign the login the necessary permissions.

Niels
|||

OK,

first I tried to drop my user but there came out errors because user owns schemas etc.

I was impatient to test this, so I created new login and made login owner of the database. After givin permissions to the login I was able to create assembly.

In other words, now it is working! Thank you very much Niels!

Can you explain to me what was wrong with the "original" owner? I quess there happens "something" when database is restored...