SQL Server Data Dude dpproj - on deploy can't change owner to sa

Question

I have a CLR function that requires TRUSTWORTHY ON and owner of the db = 'sa' in order to function correctly.

In my deploy script I am calling this code:

  Use MySecurityDB
  go
  EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false 
  go
  Use MyMainDB
  go
  EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false  
  go
  ALTER DATABASE MyMainDB SET TRUSTWORTHY ON 
  go

When the deploy finishes running, the owner of MyMainDB is set to my windows authenticated user name. I have to manually run the above code (which is in my deploy script) in order to allow the CLR function to work. For some reason, MySecurityDB is changed to owner SA but not MyMainDB.

Any idea on why this is? Is there another practical way to acomplish the task of setting ownership and TrustWorthy within the deploy?

Thanks in advance,

teddy

Answer 1

Are you putting those commands in the post-deployment script file?

Have you looked at the generated deployment script, to see if it's doing what you expect?

To do so: show hidden files in Visual Studio; that will make a sql folder visible; in sql\debug, look for YourProject.sql