Db Owner

[jack-b@humlog.com]

Hi,

I currently have two databases (DEV & TEST) with the same users but
different owners. The TEST database is on a remote machine with 2
database users - USR1 & DBO. The DEV database is exactly the same.
However, when I create procedures etc the DEV database recognises the
owner as DBO and the TEST database USR1.

I have made USR1 an owner on DEV and I login using the correct
credentials. However it still is not recognising this when creating
procedures.

This is very frustrating when doing a SQL Compare as it thinks the
stored procs are different!

I have done a sp_changedbowne r and this didn't work. There must be
another config value that need changing?

Any ideas?

Thanks.

[dbmonitor]

Re: Db Owner



jack-b@humlog.com wrote:[color=blue]
> Hi,
>
> I currently have two databases (DEV & TEST) with the same users but
> different owners. The TEST database is on a remote machine with 2
> database users - USR1 & DBO. The DEV database is exactly the same.
> However, when I create procedures etc the DEV database recognises the
> owner as DBO and the TEST database USR1.
>
> I have made USR1 an owner on DEV and I login using the correct
> credentials. However it still is not recognising this when creating
> procedures.
>
> This is very frustrating when doing a SQL Compare as it thinks the
> stored procs are different!
>
> I have done a sp_changedbowne r and this didn't work. There must be
> another config value that need changing?
>
> Any ideas?
>
> Thanks.[/color]

Could you try:

CREATE PROCEDURE dbo.procname

The Owner can be included in the create syntax

--
David Rowland
NEW DBMonitor Version Out Now! New EMAIL Alert Feature!

http://dbmonitor.tripod.com

[Erland Sommarskog]

Re: Db Owner

(jack-b@humlog.com) writes:[color=blue]
> I currently have two databases (DEV & TEST) with the same users but
> different owners. The TEST database is on a remote machine with 2
> database users - USR1 & DBO. The DEV database is exactly the same.
> However, when I create procedures etc the DEV database recognises the
> owner as DBO and the TEST database USR1.
>
> I have made USR1 an owner on DEV and I login using the correct
> credentials. However it still is not recognising this when creating
> procedures.
>
> This is very frustrating when doing a SQL Compare as it thinks the
> stored procs are different!
>
> I have done a sp_changedbowne r and this didn't work. There must be
> another config value that need changing?[/color]

Not really sure that I understood your setup correctly. But if you
login as USR1 and USR1 is the owner of DEV, the login USR1 will map
to the user dbo.

I'm not really sure what you want to achieve, but the simplest is to
have dbo as owner of all stored procedures. You can use
sp_changeobject owner to change the owners of the procedure in TEST.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp