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

Ownership Chains and Linked Servers

I have 2 servers which are linked. All objects on server1 are owned by sa an
d
all objects on server2 are owned by sa. I have created a mapping between sa
on server1 and sa on server2. However the ownership chain seems to be broken
when I try and run a stored procedure from server1 which accesses objects on
server2 as a user other than sa.
Any ideas?Ownership chaining does not apply to queries across linked servers because
these are essentially different database connections.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stevo" <Stevo@.discussions.microsoft.com> wrote in message
news:02DAAFDA-584E-4148-8B26-A752D6D08A7F@.microsoft.com...
>I have 2 servers which are linked. All objects on server1 are owned by sa
>and
> all objects on server2 are owned by sa. I have created a mapping between
> sa
> on server1 and sa on server2. However the ownership chain seems to be
> broken
> when I try and run a stored procedure from server1 which accesses objects
> on
> server2 as a user other than sa.
> Any ideas?|||Hmm. That's what I feared.
Does this mean that the ownership chain will always be broken across linked
servers and so I have to give a user server2 select permisssions so that a
stored procedure on server1 is able to select from server2?
"Dan Guzman" wrote:

> Ownership chaining does not apply to queries across linked servers because
> these are essentially different database connections.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stevo" <Stevo@.discussions.microsoft.com> wrote in message
> news:02DAAFDA-584E-4148-8B26-A752D6D08A7F@.microsoft.com...
>
>|||> Does this mean that the ownership chain will always be broken across
> linked
> servers and so I have to give a user server2 select permisssions so that a
> stored procedure on server1 is able to select from server2?
Your understanding is correct that linked servers effectively break the
ownership chain. You'll need to grant permissions needed on server2 to the
account(s) used for the linked server connection. See sp_addlinkedsrvlogin
in the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stevo" <Stevo@.discussions.microsoft.com> wrote in message
news:AAFC2AE4-08BF-486A-A712-1BE6072B8DB0@.microsoft.com...[vbcol=seagreen]
> Hmm. That's what I feared.
> Does this mean that the ownership chain will always be broken across
> linked
> servers and so I have to give a user server2 select permisssions so that a
> stored procedure on server1 is able to select from server2?
> "Dan Guzman" wrote:
>

owner data objects

I've a problem.

In my MSSql db I've some tables named username.mytable and only one
store procedure named dbo.sp;

CREATE TABLE [pippo].[mytable] (
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [pluto].[mytable] (
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[sp]
AS
select * from mytable
GO

but when I try to execute dbo.sp (from pippo or pluto connection) I've
this error (users pippo and pluto are owner):

Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
Invalid object name 'mytable'.

How can I access to pippo's (or pluto's) data from dbo.sp?

thanks!!zMatteo (origma@.edpsistem.it) writes:
> I've a problem.
> In my MSSql db I've some tables named username.mytable and only one
> store procedure named dbo.sp;
>
> CREATE TABLE [pippo].[mytable] (
> [year] [int] NOT NULL ,
> [month] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [pluto].[mytable] (
> [year] [int] NOT NULL ,
> [month] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE [dbo].[sp]
> AS
> select * from mytable
> GO
>
> but when I try to execute dbo.sp (from pippo or pluto connection) I've
> this error (users pippo and pluto are owner):
> Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
> Invalid object name 'mytable'.
> How can I access to pippo's (or pluto's) data from dbo.sp?

SELECT * FROM pippo.mytable

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9629B0769BAFBYazorman@.127.0.0.1>...
> zMatteo (origma@.edpsistem.it) writes:
> > I've a problem.
> > In my MSSql db I've some tables named username.mytable and only one
> > store procedure named dbo.sp;
> > CREATE TABLE [pippo].[mytable] (
> > [year] [int] NOT NULL ,
> > [month] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [pluto].[mytable] (
> > [year] [int] NOT NULL ,
> > [month] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE PROCEDURE [dbo].[sp]
> > AS
> > select * from mytable
> > GO
> > but when I try to execute dbo.sp (from pippo or pluto connection) I've
> > this error (users pippo and pluto are owner):
> > Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
> > Invalid object name 'mytable'.
> > How can I access to pippo's (or pluto's) data from dbo.sp?
> SELECT * FROM pippo.mytable

but for user pluto i'd make a new store procedure...
I'd resolve the problem (attention: it's ok only for not sysadmin users)!!:

CREATE TABLE [dbo].[mytable] (
[user] [smallint] NOT NULL,
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[myview]
AS
select * from mytable
where user=user_id()
GO

CREATE PROCEDURE [dbo].[sp]
AS
select * from myview
GO

Owner

I have a few objects (views, tables, stored procedures) that are owned by a
user called 'appWebUser'. If I'm logged under another user, i.e.
(appTrainer), I have to qualify the owner.object to access it instead of
just the object name. Is there any way to just use the object name like the
dbo owner. For example.
Currently: logged in as appTrainer
select * from appWebUser.table1
What I Want: logged in as appTrainer
select * from table1Sorry, I should have included ...
If you want to get rid of the owner.object issue, change all of the object
owners to dbo.
"Morgan" <mfears@.spamcop.net> wrote in message
news:eCBjzulcDHA.3620@.TK2MSFTNGP11.phx.gbl...
> As long as all of the object names are unique for each type of object
> (table, view, etc) across the whole database, you can rename them using
> sp_changeobjectowner. However, as you'll see in BOL, any permissions on
the
> table must be reapplied once you change the owner. I would go about this
> very carefully.
>
> "Tim" <Tim@.NOSPAM.com> wrote in message
> news:#8dn8XlcDHA.2436@.TK2MSFTNGP12.phx.gbl...
> > I have a few objects (views, tables, stored procedures) that are owned
by
> a
> > user called 'appWebUser'. If I'm logged under another user, i.e.
> > (appTrainer), I have to qualify the owner.object to access it instead of
> > just the object name. Is there any way to just use the object name like
> the
> > dbo owner. For example.
> >
> > Currently: logged in as appTrainer
> > select * from appWebUser.table1
> >
> > What I Want: logged in as appTrainer
> > select * from table1
> >
> >
>

Owership of database objects, Yu'll login to use?

I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
Sybase we would alias a developer to DBO (of that database only) so
that all objects (tables/stored procedures) would show up as owned by
DBO.
In SQL Server it looks to me that the only way to get obects owned by
DBO is to load them as SA or add the developer to the fixed role of
sysadmin. Neither of those appears to be the way to go.
So I'm wondering how other shows handle this issue. Do you set up an
specific account for loading objects and maintaining a SQL server
database?
Thanks,
Randy K
wawork@.hotmail.comHi Randy
I worked with Sybase for 8 years, and have been working with Microsoft SQL
Server since its inception.
In SQL Server, you can alias logins to the dbo user of a database exactly
the same way you do with Sybase, with the sp_addalias procedure.
Is it possible you're trying to achieve this functionality using the
db_owner role? Putting a user in that role will give her all the rights and
privileges of the database owner, but her name will still be her own name,
and not dbo. So any objects she creates, by default will not be owned by
dbo. However, someone in the db_owner role can create objects and specify
that the owner should be dbo:
CREATE TABLE dbo.newtable
(column ...)
So there are really two different things going on. You can actually give
someone the name dbo using sp_addalias, or you can put them in the db_owner
role, and they can specify that new obects are to be owned by dbo.
It's actually recommended that you always specify the owner name of objects,
both when creating them and when referencing them, and if you get into this
habit, you don't lose anything by just using the db_owner role.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randy K" <wawork@.hotmail.com> wrote in message
news:3f27f6bc.59865578@.msnews.microsoft.com...
> I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
> Sybase we would alias a developer to DBO (of that database only) so
> that all objects (tables/stored procedures) would show up as owned by
> DBO.
> In SQL Server it looks to me that the only way to get obects owned by
> DBO is to load them as SA or add the developer to the fixed role of
> sysadmin. Neither of those appears to be the way to go.
> So I'm wondering how other shows handle this issue. Do you set up an
> specific account for loading objects and maintaining a SQL server
> database?
> Thanks,
> Randy K
> wawork@.hotmail.com|||Unfortunately, a user with the db_owner role can
> not change the ownership of a object when creating it using the ESRI
> software.
Yes, this can be a problem. Even Microsoft's own Enterprise Manager didn't
allow specifying a different owner in SQL Server 7, and although SQL 2000
allows it, it is not obvious how to do it. That's one of the reasons why I
never use GUIs to create tables. :-)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randy K" <wawork@.hotmail.com> wrote in message
news:3f284734.18529640@.msnews.microsoft.com...
> Thank you Kalen that's what I was looking for. I had always used
> Sybase Central GUI to alias users as DBO and mistaking thought
> assigning a user to the db_owner role in SQL Server was the same
> thing.
> I'll need to do some testing since we are using SQL Server with ArcSDE
> by ESRI on top. Of the two methods only the first, spp_addalias,
> works with ArcSDE. Unfortunately, a user with the db_owner role can
> not change the ownership of a object when creating it using the ESRI
> software.
>
> On Wed, 30 Jul 2003 10:21:16 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
> >Hi Randy
> >
> >I worked with Sybase for 8 years, and have been working with Microsoft
SQL
> >Server since its inception.
> >
> >In SQL Server, you can alias logins to the dbo user of a database exactly
> >the same way you do with Sybase, with the sp_addalias procedure.
> >
> >Is it possible you're trying to achieve this functionality using the
> >db_owner role? Putting a user in that role will give her all the rights
and
> >privileges of the database owner, but her name will still be her own
name,
> >and not dbo. So any objects she creates, by default will not be owned by
> >dbo. However, someone in the db_owner role can create objects and specify
> >that the owner should be dbo:
> >
> >CREATE TABLE dbo.newtable
> >(column ...)
> >
> >So there are really two different things going on. You can actually give
> >someone the name dbo using sp_addalias, or you can put them in the
db_owner
> >role, and they can specify that new obects are to be owned by dbo.
> >
> >It's actually recommended that you always specify the owner name of
objects,
> >both when creating them and when referencing them, and if you get into
this
> >habit, you don't lose anything by just using the db_owner role.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Randy K" <wawork@.hotmail.com> wrote in message
> >news:3f27f6bc.59865578@.msnews.microsoft.com...
> >> I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
> >> Sybase we would alias a developer to DBO (of that database only) so
> >> that all objects (tables/stored procedures) would show up as owned by
> >> DBO.
> >>
> >> In SQL Server it looks to me that the only way to get obects owned by
> >> DBO is to load them as SA or add the developer to the fixed role of
> >> sysadmin. Neither of those appears to be the way to go.
> >>
> >> So I'm wondering how other shows handle this issue. Do you set up an
> >> specific account for loading objects and maintaining a SQL server
> >> database?
> >>
> >> Thanks,
> >> Randy K
> >> wawork@.hotmail.com
> >
> >
>