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 of maintenance jobs-permission hole?

Hello,
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jjjj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>

owner of maintenance jobs-permission hole?

Hello,
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jj
jj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>

Owner of a database

Hello,
What are the effects in working with a database owned by a user 'X'? In other
words, what are the differences whether i.e. the owner is 'sa', a user which is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
Joachim
Hi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim
|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly
|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegro ups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>

Owner of a database

Hello,
What are the effects in working with a database owned by a user 'X'? In othe
r
words, what are the differences whether i.e. the owner is 'sa', a user which
is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
JoachimHi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegroups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>

Owner of a database

Hello,
What are the effects in working with a database owned by a user 'X'? In other
words, what are the differences whether i.e. the owner is 'sa', a user which is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
JoachimHi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegroups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>

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

owned schema or role members?

Hi,
I want to create a login (for account ASPNET from the Active Directory) in
sql server express 2005 for a specific database.
When addidng a new user to a specific database, i see:
Owned schemas, where i take db_datareader and db_datawriter
Roles members: also db_datareader and db_datawriter
What's the difference between both and are they both required fpr account
ASPNET?
Tbanks
BartBart
--db_datareader
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1cd
ab0fab43.htm
--db_datawriter
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/08a5c847-f993-4402-b3ac-a511
3f41e8c8.htm
"Bart" <b@.sdq.dc> wrote in message
news:OMfgIZlbHHA.3420@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I want to create a login (for account ASPNET from the Active Directory) in
> sql server express 2005 for a specific database.
> When addidng a new user to a specific database, i see:
> Owned schemas, where i take db_datareader and db_datawriter
> Roles members: also db_datareader and db_datawriter
> What's the difference between both and are they both required fpr account
> ASPNET?
> Tbanks
> Bart
>
>
>|||Thanks, but my question was more about the difference between Owned schema
and Role members.
Is it enough to take db_anything in Role members or must the user also owns
a schema with db_anything?
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:eDumYbsbHHA.2088@.TK2MSFTNGP04.phx.gbl...
> Bart
> --db_datareader
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1
cdab0fab43.htm
>
> --db_datawriter
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/08a5c847-f993-4402-b3ac-a5
113f41e8c8.htm
>
> "Bart" <b@.sdq.dc> wrote in message
> news:OMfgIZlbHHA.3420@.TK2MSFTNGP05.phx.gbl...
>|||Bart
SCHEMA and Roles are different things. When you create a user ut should be
mapped to schema you have specified ir DEFAULT schema--DBO.
Can you elaborate on what you are trying to achive?
"Bart" <b@.sdq.dc> wrote in message
news:uEq8NwvbHHA.4140@.TK2MSFTNGP06.phx.gbl...
> Thanks, but my question was more about the difference between Owned schema
> and Role members.
> Is it enough to take db_anything in Role members or must the user also
> owns a schema with db_anything?
> "Uri Dimant" <urid@.iscar.co.il> schreef in bericht
> news:eDumYbsbHHA.2088@.TK2MSFTNGP04.phx.gbl...
>|||Well, i want to create a new login for account ASPNET (which runs under
ASP.NET) and then define an user (aspnet) for 'mydatabase'. That user must
get read/write prights to the db. At that level, i see in the window
configuration two things about read /write: Owned Schema and Role members.
So my question is: should i take db_readreader / db_writer in the Ownd
Schema or in Role members or in both?
Thanks
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:%23p8We%232bHHA.4632@.TK2MSFTNGP03.phx.gbl...
> Bart
> SCHEMA and Roles are different things. When you create a user ut should be
> mapped to schema you have specified ir DEFAULT schema--DBO.
> Can you elaborate on what you are trying to achive?
>
> "Bart" <b@.sdq.dc> wrote in message
> news:uEq8NwvbHHA.4140@.TK2MSFTNGP06.phx.gbl...
>|||Bart
Does the user should run queries which manipulate with tables belong to
anohter SCHEMA?
I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the
user needs to query
"Bart" <b@.sdq.dc> wrote in message
news:u1ddKM3bHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Well, i want to create a new login for account ASPNET (which runs under
> ASP.NET) and then define an user (aspnet) for 'mydatabase'. That user
> must get read/write prights to the db. At that level, i see in the window
> configuration two things about read /write: Owned Schema and Role members.
> So my question is: should i take db_readreader / db_writer in the Ownd
> Schema or in Role members or in both?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> schreef in bericht
> news:%23p8We%232bHHA.4632@.TK2MSFTNGP03.phx.gbl...
>|||"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:OOCCsw3bHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Bart
> Does the user should run queries which manipulate with tables belong to
> anohter SCHEMA?
> I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the
> user needs to query
Thanks, but I still don't understand the difference between giving the Role
db_datareader / db_datawriter to user 'aspnet' and adding user 'aspnet' to
Schema dbdatareader and Schema db_datawriter ...|||Bart (b@.sdq.dc) writes:
> Thanks, but I still don't understand the difference between giving the
> Role db_datareader / db_datawriter to user 'aspnet' and adding user
> 'aspnet' to Schema dbdatareader and Schema db_datawriter ...
You should not need the schemss. They exist of legacy reasons. In SQL 2000
there was no difference between a role/user on the one hand and a schema
on the other. If you created a user/role X, you also got a schema X included
in the price.
In SQL 2005 schemas and database principals (users and roles) are separated.
But Microsoft still by default creates schemas for all pre-defined roles
and users, since old applications may rely on these and create objects
in these schemas.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ok, thanks
"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns99004B34D29Yazorman@.127.0.0.1...
> Bart (b@.sdq.dc) writes:
> You should not need the schemss. They exist of legacy reasons. In SQL 2000
> there was no difference between a role/user on the one hand and a schema
> on the other. If you created a user/role X, you also got a schema X
> included
> in the price.
> In SQL 2005 schemas and database principals (users and roles) are
> separated.
> But Microsoft still by default creates schemas for all pre-defined roles
> and users, since old applications may rely on these and create objects
> in these schemas.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx