Ownership of objects

I want to drop a user by running sp_dropuser.
I get this error:
The user owns objects in the database and cannot be dropped.
How do I find the object the user owns?
Thanks,
AntoninHere's a quick way:
select * from sysobjects where uid = user_id('dbo')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>I want to drop a user by running sp_dropuser.
> I get this error:
> The user owns objects in the database and cannot be dropped.
> How do I find the object the user owns?
> Thanks,
> Antonin
>|||Hi Tibor,
Thanks for your reply.
I ran
select * from sysobjects where uid = user_id('BME054')
It worked. I got view user defined functions and views, one of them
'SerialQuery'.
Then I tried
sp_changeobjectowner 'SerialQuery', 'dbo'
and got this
Object 'SerialQuery' does not exists or is not a valid object for this
platform.
Any idea, suggestion?
Thanks,
Antonin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
> Here's a quick way:
> select * from sysobjects where uid = user_id('dbo')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
> news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>>I want to drop a user by running sp_dropuser.
>> I get this error:
>> The user owns objects in the database and cannot be dropped.
>> How do I find the object the user owns?
>> Thanks,
>> Antonin
>>
>|||Hi all,
I read the books online and found out I have to do it like this:
sp_changeobjectowner 'BME054.SerialQuery', 'dbo'
It worked OK and then I could drop the user.
Antonin
"Antonin" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:%23mPi$a$rEHA.868@.TK2MSFTNGP12.phx.gbl...
> Hi Tibor,
> Thanks for your reply.
> I ran
> select * from sysobjects where uid = user_id('BME054')
> It worked. I got view user defined functions and views, one of them
> 'SerialQuery'.
> Then I tried
> sp_changeobjectowner 'SerialQuery', 'dbo'
> and got this
> Object 'SerialQuery' does not exists or is not a valid object for this
> platform.
> Any idea, suggestion?
> Thanks,
> Antonin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
>> Here's a quick way:
>> select * from sysobjects where uid = user_id('dbo')
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
>> news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>>I want to drop a user by running sp_dropuser.
>> I get this error:
>> The user owns objects in the database and cannot be dropped.
>> How do I find the object the user owns?
>> Thanks,
>> Antonin
>>
>>
>

Ownership of objects

I want to drop a user by running sp_dropuser.
I get this error:
The user owns objects in the database and cannot be dropped.
How do I find the object the user owns?
Thanks,
AntoninHere's a quick way:
select * from sysobjects where uid = user_id('dbo')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>I want to drop a user by running sp_dropuser.
> I get this error:
> The user owns objects in the database and cannot be dropped.
> How do I find the object the user owns?
> Thanks,
> Antonin
>|||Hi Tibor,
Thanks for your reply.
I ran
select * from sysobjects where uid = user_id('BME054')
It worked. I got view user defined functions and views, one of them
'SerialQuery'.
Then I tried
sp_changeobjectowner 'SerialQuery', 'dbo'
and got this
Object 'SerialQuery' does not exists or is not a valid object for this
platform.
Any idea, suggestion?
Thanks,
Antonin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
> Here's a quick way:
> select * from sysobjects where uid = user_id('dbo')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
> news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>|||Hi all,
I read the books online and found out I have to do it like this:
sp_changeobjectowner 'BME054.SerialQuery', 'dbo'
It worked OK and then I could drop the user.
Antonin
"Antonin" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:%23mPi$a$rEHA.868@.TK2MSFTNGP12.phx.gbl...
> Hi Tibor,
> Thanks for your reply.
> I ran
> select * from sysobjects where uid = user_id('BME054')
> It worked. I got view user defined functions and views, one of them
> 'SerialQuery'.
> Then I tried
> sp_changeobjectowner 'SerialQuery', 'dbo'
> and got this
> Object 'SerialQuery' does not exists or is not a valid object for this
> platform.
> Any idea, suggestion?
> Thanks,
> Antonin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
>

Ownership of objects

I want to drop a user by running sp_dropuser.
I get this error:
The user owns objects in the database and cannot be dropped.
How do I find the object the user owns?
Thanks,
Antonin
Here's a quick way:
select * from sysobjects where uid = user_id('dbo')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>I want to drop a user by running sp_dropuser.
> I get this error:
> The user owns objects in the database and cannot be dropped.
> How do I find the object the user owns?
> Thanks,
> Antonin
>
|||Hi Tibor,
Thanks for your reply.
I ran
select * from sysobjects where uid = user_id('BME054')
It worked. I got view user defined functions and views, one of them
'SerialQuery'.
Then I tried
sp_changeobjectowner 'SerialQuery', 'dbo'
and got this
Object 'SerialQuery' does not exists or is not a valid object for this
platform.
Any idea, suggestion?
Thanks,
Antonin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
> Here's a quick way:
> select * from sysobjects where uid = user_id('dbo')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
> news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>
|||Hi all,
I read the books online and found out I have to do it like this:
sp_changeobjectowner 'BME054.SerialQuery', 'dbo'
It worked OK and then I could drop the user.
Antonin
"Antonin" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:%23mPi$a$rEHA.868@.TK2MSFTNGP12.phx.gbl...
> Hi Tibor,
> Thanks for your reply.
> I ran
> select * from sysobjects where uid = user_id('BME054')
> It worked. I got view user defined functions and views, one of them
> 'SerialQuery'.
> Then I tried
> sp_changeobjectowner 'SerialQuery', 'dbo'
> and got this
> Object 'SerialQuery' does not exists or is not a valid object for this
> platform.
> Any idea, suggestion?
> Thanks,
> Antonin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
>

Owner of tables

I have a stored procedure, that among other things drops and recreates two
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1|||Try naming the stored procedure dbo.[sproc name]
--
Thomas
"Robert R via SQLMonster.com" wrote:
> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1
>