Deploying CLR trigger to SQL-Server 2005 -- Permissions Required.

I've written a C# trigger on Developer_Box, using VS 2005
Professional.
I need to deploy it on Test_Server, running SQLServer 2005.

It compile fine, and the references to the appropriate database and
table are correct.


First attempt:
You do not have CREATE Trigger permission.
Solution - Walk over to Test_Server, open management studio and give
myself CREATE Trigger permission.

Second Attempt:
"Cannot create the trigger 'MyTrigger', because you do not have
permission."
What it doesn't tell me is what permission is missing.

I could run this as admin, but I'm going to need to deploy it on
Production_Server eventually, so I want to request the minimal
permission set possible. (And I won't get admin on Production)

compatibility mode is 90;
CLR is enabled via sp_configure (and RECONFIGURE was run)

Re: Deploying CLR trigger to SQL-Server 2005 -- Permissions Required.

chris (chris.cudmore@gmail.com) writes:
> First attempt:
> You do not have CREATE Trigger permission.
> Solution - Walk over to Test_Server, open management studio and give
> myself CREATE Trigger permission.
>
> Second Attempt:
> "Cannot create the trigger 'MyTrigger', because you do not have
> permission."
> What it doesn't tell me is what permission is missing.
>
> I could run this as admin, but I'm going to need to deploy it on
> Production_Server eventually, so I want to request the minimal
> permission set possible. (And I won't get admin on Production)

The minimal set would be the permissions CREATE ASSEMBLY, CREATE TRIGGER
and ALTER on the table in question.

In practice, being member of the database role db_ddladmin, may be an
easier path.

For the deployment on the production server, I recommend that you build
a T-SQL script to do it all. Keep in mind that you can specify an
assembly as a binary string with the CREATE ASSEMBLY statement.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Deploying CLR trigger to SQL-Server 2005 -- Permissions Required.

Alter Table did it.

Create Assembly
and
Create Trigger

Scripts run on the server itself turns out to be the preferred option.
I was just trying to save myself a walk over to the Test_Server, by
deploying directly from Visual Studio.






On Jan 28, 5:44 pm, Erland Sommarskog wrote:
> chris (chris.cudm...@gmail.com) writes:
> > First attempt:
> > You do not have CREATE Trigger permission.
> > Solution - Walk over to Test_Server, open management studio and give
> > myself CREATE Trigger permission.
>
> > Second Attempt:
> > "Cannot create the trigger 'MyTrigger', because you do not have
> > permission."
> > What it doesn't tell me is what permission is missing.
>
> > I could run this as admin, but I'm going to need to deploy it on
> > Production_Server eventually, so I want to request the minimal
> > permission set possible. (And I won't get admin on Production)
>
> The minimal set would be the permissions CREATE ASSEMBLY, CREATE TRIGGER
> and ALTER on the table in question.
>
> In practice, being member of the database role db_ddladmin, may be an
> easier path.
>
> For the deployment on the production server, I recommend that you build
> a T-SQL script to do it all. Keep in mind that you can specify an
> assembly as a binary string with the CREATE ASSEMBLY statement.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx