Query dbowner

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

PaulEM 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

<paulwragg2323@.hotmail.com> wrote in message
news:1102498820.367939.212520@.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|||This works fine (except missing o for alias for
master.dbo.sysdatabases) and is just what I needed.

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

Thanks Danny.|||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

<paulwragg2323@.hotmail.com> wrote in message
news:1102498820.367939.212520@.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|||Thanks to both of you for replying to my post.
I have now solved this and proven that both methods will work.

Thanks.

Query Compilation Plans

Hi all,
We have a third party application that makes extensive use of
sp_executesql which allows it to reuse compilation plans for the sql
queries. Now, I am trying to monitor what the application is doing using the
Profiler. Since the application reuses compilation plans, often I am not
able to see the actual queries in the profile. So I would like to
temporarilly force recompilation of all queries executed by the application.
I don't know how to do it. So please help me and tell me how to do this in
Query Analyzer; I don't have access to Enterprise Manager.
Thanks in advance.> Since the application reuses compilation plans, often I am not able to see
the
> actual queries in the profile.
You should be able to see statement sent to sql server, unless the client
app is using sp_prepare / sp_execute, in that case you will see sp
_execute and parameters.
AMB
"helpful sql" wrote:

> Hi all,
> We have a third party application that makes extensive use of
> sp_executesql which allows it to reuse compilation plans for the sql
> queries. Now, I am trying to monitor what the application is doing using t
he
> Profiler. Since the application reuses compilation plans, often I am not
> able to see the actual queries in the profile. So I would like to
> temporarilly force recompilation of all queries executed by the applicatio
n.
> I don't know how to do it. So please help me and tell me how to do this in
> Query Analyzer; I don't have access to Enterprise Manager.
> Thanks in advance.
>
>|||try
dbcc freeproccache
for removing all the execution plans from the cache|||Yes, the application is using sp_prepare/sp_execute. Is there a way to see
the actual queries used in sp_prepare? I did not capture them in the
Profiler.
Thanks.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:10C6CECD-CD3B-458F-8171-1337EBC78B0A@.microsoft.com...[vbcol=seagreen]
> the
> You should be able to see statement sent to sql server, unless the client
> app is using sp_prepare / sp_execute, in that case you will see sp
> _execute and parameters.
>
> AMB
> "helpful sql" wrote:
>|||helpful sql,
Try looking inside the system table syscacheobjects.
select
objtype,
sql
from
master.dbo.syscacheobjects
where
objtype = 'Prepared'
go
syscacheobjects
http://msdn.microsoft.com/library/d...br />
3dyr.asp
AMB
"helpful sql" wrote:

> Yes, the application is using sp_prepare/sp_execute. Is there a way to see
> the actual queries used in sp_prepare? I did not capture them in the
> Profiler.
> Thanks.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:10C6CECD-CD3B-458F-8171-1337EBC78B0A@.microsoft.com...
>
>|||Lookup fn_get_sql() in BOL.
"helpful sql" wrote:

> Hi all,
> We have a third party application that makes extensive use of
> sp_executesql which allows it to reuse compilation plans for the sql
> queries. Now, I am trying to monitor what the application is doing using t
he
> Profiler. Since the application reuses compilation plans, often I am not
> able to see the actual queries in the profile. So I would like to
> temporarilly force recompilation of all queries executed by the applicatio
n.
> I don't know how to do it. So please help me and tell me how to do this in
> Query Analyzer; I don't have access to Enterprise Manager.
> Thanks in advance.
>
>

Query Compilation Plans

Hi all,
We have a third party application that makes extensive use of
sp_executesql which allows it to reuse compilation plans for the sql
queries. Now, I am trying to monitor what the application is doing using the
Profiler. Since the application reuses compilation plans, often I am not
able to see the actual queries in the profile. So I would like to
temporarilly force recompilation of all queries executed by the application.
I don't know how to do it. So please help me and tell me how to do this in
Query Analyzer; I don't have access to Enterprise Manager.
Thanks in advance.> Since the application reuses compilation plans, often I am not able to see
the
> actual queries in the profile.
You should be able to see statement sent to sql server, unless the client
app is using sp_prepare / sp_execute, in that case you will see sp
_execute and parameters.
AMB
"helpful sql" wrote:
> Hi all,
> We have a third party application that makes extensive use of
> sp_executesql which allows it to reuse compilation plans for the sql
> queries. Now, I am trying to monitor what the application is doing using the
> Profiler. Since the application reuses compilation plans, often I am not
> able to see the actual queries in the profile. So I would like to
> temporarilly force recompilation of all queries executed by the application.
> I don't know how to do it. So please help me and tell me how to do this in
> Query Analyzer; I don't have access to Enterprise Manager.
> Thanks in advance.
>
>|||try
dbcc freeproccache
for removing all the execution plans from the cache|||Yes, the application is using sp_prepare/sp_execute. Is there a way to see
the actual queries used in sp_prepare? I did not capture them in the
Profiler.
Thanks.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:10C6CECD-CD3B-458F-8171-1337EBC78B0A@.microsoft.com...
>> Since the application reuses compilation plans, often I am not able to
>> see
> the
>> actual queries in the profile.
> You should be able to see statement sent to sql server, unless the client
> app is using sp_prepare / sp_execute, in that case you will see sp
> _execute and parameters.
>
> AMB
> "helpful sql" wrote:
>> Hi all,
>> We have a third party application that makes extensive use of
>> sp_executesql which allows it to reuse compilation plans for the sql
>> queries. Now, I am trying to monitor what the application is doing using
>> the
>> Profiler. Since the application reuses compilation plans, often I am not
>> able to see the actual queries in the profile. So I would like to
>> temporarilly force recompilation of all queries executed by the
>> application.
>> I don't know how to do it. So please help me and tell me how to do this
>> in
>> Query Analyzer; I don't have access to Enterprise Manager.
>> Thanks in advance.
>>|||helpful sql,
Try looking inside the system table syscacheobjects.
select
objtype,
sql
from
master.dbo.syscacheobjects
where
objtype = 'Prepared'
go
syscacheobjects
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-c_3dyr.asp
AMB
"helpful sql" wrote:
> Yes, the application is using sp_prepare/sp_execute. Is there a way to see
> the actual queries used in sp_prepare? I did not capture them in the
> Profiler.
> Thanks.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:10C6CECD-CD3B-458F-8171-1337EBC78B0A@.microsoft.com...
> >> Since the application reuses compilation plans, often I am not able to
> >> see
> > the
> >> actual queries in the profile.
> >
> > You should be able to see statement sent to sql server, unless the client
> > app is using sp_prepare / sp_execute, in that case you will see sp
> > _execute and parameters.
> >
> >
> > AMB
> >
> > "helpful sql" wrote:
> >
> >> Hi all,
> >> We have a third party application that makes extensive use of
> >> sp_executesql which allows it to reuse compilation plans for the sql
> >> queries. Now, I am trying to monitor what the application is doing using
> >> the
> >> Profiler. Since the application reuses compilation plans, often I am not
> >> able to see the actual queries in the profile. So I would like to
> >> temporarilly force recompilation of all queries executed by the
> >> application.
> >> I don't know how to do it. So please help me and tell me how to do this
> >> in
> >> Query Analyzer; I don't have access to Enterprise Manager.
> >>
> >> Thanks in advance.
> >>
> >>
> >>
>
>|||Lookup fn_get_sql() in BOL.
"helpful sql" wrote:
> Hi all,
> We have a third party application that makes extensive use of
> sp_executesql which allows it to reuse compilation plans for the sql
> queries. Now, I am trying to monitor what the application is doing using the
> Profiler. Since the application reuses compilation plans, often I am not
> able to see the actual queries in the profile. So I would like to
> temporarilly force recompilation of all queries executed by the application.
> I don't know how to do it. So please help me and tell me how to do this in
> Query Analyzer; I don't have access to Enterprise Manager.
> Thanks in advance.
>
>

Query Compilation Plans

Hi all,
We have a third party application that makes extensive use of
sp_executesql which allows it to reuse compilation plans for the sql
queries. Now, I am trying to monitor what the application is doing using the
Profiler. Since the application reuses compilation plans, often I am not
able to see the actual queries in the profile. So I would like to
temporarilly force recompilation of all queries executed by the application.
I don't know how to do it. So please help me and tell me how to do this in
Query Analyzer; I don't have access to Enterprise Manager.
Thanks in advance.> Since the application reuses compilation plans, often I am not able to see
the
> actual queries in the profile.
You should be able to see statement sent to sql server, unless the client
app is using sp_prepare / sp_execute, in that case you will see sp
_execute and parameters.
AMB
"helpful sql" wrote:

> Hi all,
> We have a third party application that makes extensive use of
> sp_executesql which allows it to reuse compilation plans for the sql
> queries. Now, I am trying to monitor what the application is doing using t
he
> Profiler. Since the application reuses compilation plans, often I am not
> able to see the actual queries in the profile. So I would like to
> temporarilly force recompilation of all queries executed by the applicatio
n.
> I don't know how to do it. So please help me and tell me how to do this in
> Query Analyzer; I don't have access to Enterprise Manager.
> Thanks in advance.
>
>|||try
dbcc freeproccache
for removing all the execution plans from the cache|||Yes, the application is using sp_prepare/sp_execute. Is there a way to see
the actual queries used in sp_prepare? I did not capture them in the
Profiler.
Thanks.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:10C6CECD-CD3B-458F-8171-1337EBC78B0A@.microsoft.com...
> the
> You should be able to see statement sent to sql server, unless the client
> app is using sp_prepare / sp_execute, in that case you will see sp
> _execute and parameters.
>
> AMB
> "helpful sql" wrote:
>|||helpful sql,
Try looking inside the system table syscacheobjects.
select
objtype,
sql
from
master.dbo.syscacheobjects
where
objtype = 'Prepared'
go
syscacheobjects
http://msdn.microsoft.com/library/d...br />
3dyr.asp
AMB
"helpful sql" wrote:

> Yes, the application is using sp_prepare/sp_execute. Is there a way to see
> the actual queries used in sp_prepare? I did not capture them in the
> Profiler.
> Thanks.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:10C6CECD-CD3B-458F-8171-1337EBC78B0A@.microsoft.com...
>
>|||Lookup fn_get_sql() in BOL.
"helpful sql" wrote:

> Hi all,
> We have a third party application that makes extensive use of
> sp_executesql which allows it to reuse compilation plans for the sql
> queries. Now, I am trying to monitor what the application is doing using t
he
> Profiler. Since the application reuses compilation plans, often I am not
> able to see the actual queries in the profile. So I would like to
> temporarilly force recompilation of all queries executed by the applicatio
n.
> I don't know how to do it. So please help me and tell me how to do this in
> Query Analyzer; I don't have access to Enterprise Manager.
> Thanks in advance.
>
>