Query dbowner

[paulwragg2323]

Hi

We have a script that allows customers to change the users of the
database including the database owner. This script can be run at any
time. However, sometimes (and it really is only sometimes!) when the
following statement executes:

"exec sp_changedbowner @USER_OWNER"

the following error is reported:

"The proposed new database owner is already a user in the database."

I have checked the other postings and the Books Online but cannot find
a way to query the current name of the dbowner i.e. the value of
@USER_OWNER above. We have to be able to do this within the script so
that if the @USER_OWNER variable is already the dbowner we do not
bother to execute the statement as otherwise this could display the
error. Can anybody help?

Thanks in Advance

Paul

[Danny]

EM gets it from exec sp_MSdbuseraccess N'db', N'%'

which calls

select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, o.status,
o.category, o.status2, DatabaseProperty(o.name, N'isfulltextenabled')
from master.dbo.sysdatabases o where o.name = @qual

so

select suser_sname(o.sid) from master.dbo.sysdatabases where name = 'DBNAME'
should do it.

Danny

<pa***********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi

We have a script that allows customers to change the users of the
database including the database owner. This script can be run at any
time. However, sometimes (and it really is only sometimes!) when the
following statement executes:

"exec sp_changedbowner @USER_OWNER"

the following error is reported:

"The proposed new database owner is already a user in the database."

I have checked the other postings and the Books Online but cannot find
a way to query the current name of the dbowner i.e. the value of
@USER_OWNER above. We have to be able to do this within the script so
that if the @USER_OWNER variable is already the dbowner we do not
bother to execute the statement as otherwise this could display the
error. Can anybody help?

Thanks in Advance

Paul

[Paul]

This works fine (except missing o for alias for
master.dbo.sysdatabases) and is just what I needed.

Thanks Danny.

[Paul]

This works fine (except missing o for alias for
master.dbo.sysdatabases) and is just what I needed.

Thanks Danny.

[Dan Guzman]

One method to retrieve the current database owner:

DECLARE @CurrentOwner nvarchar(256)
SELECT @CurrentOwner = SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE name = DB_NAME()

You can then conditionally change the owner:

IF @CurrentOwner <> @USER_OWNER
EXEC sp_changedbowner @USER_OWNER

However, your error message is most likely because the new database owner is
a current database user rather than the database owner. The query below
will identify this condition as well as the database owner.

SELECT
l.name as LoginName,
u.name AS UserName
FROM master..syslogins l
JOIN sysusers u ON u.sid = l.sid
WHERE l.sid = SUSER_SID(@USER_OWNER)

--
Hope this helps.

Dan Guzman
SQL Server MVP

<pa***********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi

We have a script that allows customers to change the users of the
database including the database owner. This script can be run at any
time. However, sometimes (and it really is only sometimes!) when the
following statement executes:

"exec sp_changedbowner @USER_OWNER"

the following error is reported:

"The proposed new database owner is already a user in the database."

I have checked the other postings and the Books Online but cannot find
a way to query the current name of the dbowner i.e. the value of
@USER_OWNER above. We have to be able to do this within the script so
that if the @USER_OWNER variable is already the dbowner we do not
bother to execute the statement as otherwise this could display the
error. Can anybody help?

Thanks in Advance

Paul

[Paul]

Thanks to both of you for replying to my post.
I have now solved this and proven that both methods will work.

Thanks.