[Managed UDF/ SqlServer2005 ] How to force dbo schema name?

Question

I'm using "SQL SERVER PROJECT" in VS 2008 to create UDF in C# Then I'm using DEPLOY command to publish DLL into MS SQL server 2005

All works well except all created UDFs are owned by me (as user) But I wanted to keep dbo schema (eg: dbo.UDF_TEST - not jonny.UDF_TEST)

Any idea how to manage thar?

Answer 1

deploy the UDF from an account that is sysadmin, or dbo on the database. You can build the dll and then manually deploy it by logging into management studio as dbo to the database and running these commands:

CREATE ASSEMBLY Geocode FROM

'C:\PATH\YourUDF.dll' WITH

PERMISSION_SET = SAFE

or

1. From within Visual Studio in the solution explorer click on the UDF project (not the solution).
2. Go to properties.
3. Click on the database tab.
4. You can change the connection string, and also change the assembly owner here.

Answer 2

I've found another approach allowing do everything inside VS.

I've added to project SQL script named "postdeployscript.sql":

DECLARE @SQL NVARCHAR(200)

SET @SQL = 'alter schema dbo transfer ' + CURRENT_USER +'.[UDF_GET_AUDIT_VALUE]'

EXECUTE (@SQL)

GO

script is executed automaticcally by VS during depolyment process and changes schemas from current user to DBO.

Cons: you need to add each UDF/USP you are ceating manually

PS: Please note you can also use predeployscript.sql to execute some commmands before deployment