MS SQL Server Tools - Permissions needed to Create Assembly

Author

Permissions needed to Create Assembly

Chris Wood

2006-03-07, 11:24 am

Hi,

I am having difficulties in creating an assembly in a user database.

I am using an SQL login that is a db_owner of a database. The assembly has
PERMISSION_SET = EXTERNAL_ACCESS.

The first attempt gave these 2 messages:-

Error 1: Msg 6585, Level 16, State 1, Line 2

Could not impersonate the client during assembly file operation.

Error 2: Msg 300, Level 14, State 1, Line 2

EXTERNAL ACCESS ASSEMBLY permission denied on object 'server', database
'master'.

I then gave External Access Assemblies permission. This took away the 2nd
message but not the

Error 1: Msg 6585, Level 16, State 1, Line 2

Does the SQL Login have to be a sysadmin to do this?

Thanks

Chris

Kent Tegels

2006-03-07, 11:24 am

Hello Chris,

In a word, yes. You also need the right rights to read the DLL from the source
location (eg, a DACL for the account that windows is running under IIRC).

If you didn't ALTER DATABASE with SET TRUSTWORTHY on, you'll need to do the
"Safety Dance" too. See [0] for more information on that.

[0]: http://www.sqljunkies.com/WebLog/kt...
ies.aspx

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Chris Wood

2006-03-07, 11:24 am

Kent,

I forgot to mention that I did alter the user db to set TRUSTWORTHY ON. I
still get the 1st message

Msg 6585, Level 16, State 1, Line 2
Could not impersonate the client during assembly file operation.

BOL talks a lot about the Windows Account. Does this message mean Windows
permissions to the actual DLL?

Chris

"Kent Tegels" <ktegels@develop.com> wrote in message
news:b87ad74140e08c8
0fe968b1a9f0@news.microsoft.com...
> Hello Chris,
>
> In a word, yes. You also need the right rights to read the DLL from the
> source location (eg, a DACL for the account that windows is running under
> IIRC).
>
> If you didn't ALTER DATABASE with SET TRUSTWORTHY on, you'll need to do
> the "Safety Dance" too. See [0] for more information on that.
>
> [0]:
> http://www.sqljunkies.com/WebLog/kt...
ies.aspx
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

Kent Tegels

2006-03-07, 1:23 pm

Hello Chris,

> Msg 6585, Level 16, State 1, Line 2
> Could not impersonate the client during assembly file operation.
> BOL talks a lot about the Windows Account. Does this message mean
> Windows permissions to the actual DLL?

Yes, that's what that means.

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Chris Wood

2006-03-07, 8:24 pm

Kent,

I have the DLL on my workstation. Do I need to have the DLL on the server
and have the authority on the server?

Thanks

Chris

"Kent Tegels" <ktegels@develop.com> wrote in message
news:b87ad74142918c8
0ff76b247aa0@news.microsoft.com...
> Hello Chris,
>
>
> Yes, that's what that means.
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

Kent Tegels

2006-03-07, 8:24 pm

Hello Chris,

> I have the DLL on my workstation. Do I need to have the DLL on the
> server and have the authority on the server?

Well, what you need to have is a way for you to read that file from your
client from the server. I'm guessing at the moment that there's probably
a firewall between you and the server, right? Or can you simply not logon
that Windows Server on which the SQL Server instance is running?

Is there a particular reason you're not using Visual Studio to deploy here?
The reason that I ask is that it issues the create assembly command with
a binary serialization of the assembly, so there's no reason to "read the
file" from your machine. You can see it doing this with SQL profiler.

If nothing else, deploy the assembly to a local SQL Server, then use management
studio to script the assembly out to .SQL file. You could then run that file
on the remote server since the script will have the assembly inline as a
byte stream.

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Sponsored Links