CODEWITHSUNDEEP
sql-server-2005manageduser-defined-functions
Maciej
Maciej

Reputation: 10805

How to force dbo schema name?

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?

Upvotes: 1

Views: 1210

Answers (2)

Maciej
Maciej

Reputation: 10805

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

Upvotes: 1

Nick Kavadias
Nick Kavadias

Reputation: 7338

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.

Upvotes: 1

Related Questions