I'm trying to create a View in a SQL Server 2005 database such that it
selects from a table in a different database on the same server.
However, I cannot find a way to create a new user that only has
'Select' access to the view. It does work if I give a database user
'Select' permissions for both the View and the Table but I don't want
to give 'Select' access to the table.
My objective is to abstract the table so that the user is not affected
by any future changes to the table. The user sees only the View which
I can modify if necessary to protect the user against any table
changes.
GaryHI,
You need to give select permission on the table that the view usess, if
you don't want the user to see whole columns value, then just give the
select permission on column level
regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gary Leighton" <leightongary@.hotmail.com> wrote in message
news:1180695366.627816.226130@.q69g2000hsb.googlegroups.com...
> I'm trying to create a View in a SQL Server 2005 database such that it
> selects from a table in a different database on the same server.
> However, I cannot find a way to create a new user that only has
> 'Select' access to the view. It does work if I give a database user
> 'Select' permissions for both the View and the Table but I don't want
> to give 'Select' access to the table.
> My objective is to abstract the table so that the user is not affected
> by any future changes to the table. The user sees only the View which
> I can modify if necessary to protect the user against any table
> changes.
> Gary
>|||You can enable DB_CHAINING for those databases so that standard ownership
chaining applies to cross-database references as well. As long as the
ownership chain is unbroken, permissions on indirectly referenced objects
are not needed. Keep in mind that the databases must share the same owner
login if your objects are in the dbo schema because the database ownership
determines the dbo schema owner.
Users will still need a security context in the indirectly referenced
database, either directly or via the guest user. However, no object
permissions need to be granted.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary Leighton" <leightongary@.hotmail.com> wrote in message
news:1180695366.627816.226130@.q69g2000hsb.googlegroups.com...
> I'm trying to create a View in a SQL Server 2005 database such that it
> selects from a table in a different database on the same server.
> However, I cannot find a way to create a new user that only has
> 'Select' access to the view. It does work if I give a database user
> 'Select' permissions for both the View and the Table but I don't want
> to give 'Select' access to the table.
> My objective is to abstract the table so that the user is not affected
> by any future changes to the table. The user sees only the View which
> I can modify if necessary to protect the user against any table
> changes.
> Gary
>|||> You need to give select permission on the table that the view usess, if
> you don't want the user to see whole columns value, then just give the
> select permission on column level
This is not true. Permissions on indirectly referenced objects are not
needed as long as the ownership chain is unbroken. However, there are
special considerations for cross-database references. See "ownership
chains" in the Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/762249ee-881a-4c3e-b8c0-3a9475039aca.htm).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:ecw8KEEpHHA.4512@.TK2MSFTNGP03.phx.gbl...
> HI,
> You need to give select permission on the table that the view usess, if
> you don't want the user to see whole columns value, then just give the
> select permission on column level
> regards
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Gary Leighton" <leightongary@.hotmail.com> wrote in message
> news:1180695366.627816.226130@.q69g2000hsb.googlegroups.com...
>> I'm trying to create a View in a SQL Server 2005 database such that it
>> selects from a table in a different database on the same server.
>> However, I cannot find a way to create a new user that only has
>> 'Select' access to the view. It does work if I give a database user
>> 'Select' permissions for both the View and the Table but I don't want
>> to give 'Select' access to the table.
>> My objective is to abstract the table so that the user is not affected
>> by any future changes to the table. The user sees only the View which
>> I can modify if necessary to protect the user against any table
>> changes.
>> Gary
>|||hi
Thanks for correcting me, Gray.. pls ignore my post...
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eobcnMEpHHA.3368@.TK2MSFTNGP02.phx.gbl...
>> You need to give select permission on the table that the view usess, if
>> you don't want the user to see whole columns value, then just give the
>> select permission on column level
> This is not true. Permissions on indirectly referenced objects are not
> needed as long as the ownership chain is unbroken. However, there are
> special considerations for cross-database references. See "ownership
> chains" in the Books Online
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/762249ee-881a-4c3e-b8c0-3a9475039aca.htm).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:ecw8KEEpHHA.4512@.TK2MSFTNGP03.phx.gbl...
>> HI,
>> You need to give select permission on the table that the view usess, if
>> you don't want the user to see whole columns value, then just give the
>> select permission on column level
>> regards
>> --
>> VT
>> Knowledge is power, share it...
>> http://oneplace4sql.blogspot.com/
>> "Gary Leighton" <leightongary@.hotmail.com> wrote in message
>> news:1180695366.627816.226130@.q69g2000hsb.googlegroups.com...
>> I'm trying to create a View in a SQL Server 2005 database such that it
>> selects from a table in a different database on the same server.
>> However, I cannot find a way to create a new user that only has
>> 'Select' access to the view. It does work if I give a database user
>> 'Select' permissions for both the View and the Table but I don't want
>> to give 'Select' access to the table.
>> My objective is to abstract the table so that the user is not affected
>> by any future changes to the table. The user sees only the View which
>> I can modify if necessary to protect the user against any table
>> changes.
>> Gary
>>
>
Showing posts with label seperate. Show all posts
Showing posts with label seperate. Show all posts
Monday, March 12, 2012
Permissions for a View on seperate database
I'm trying to create a View in a SQL Server 2005 database such that it
selects from a table in a different database on the same server.
However, I cannot find a way to create a new user that only has
'Select' access to the view. It does work if I give a database user
'Select' permissions for both the View and the Table but I don't want
to give 'Select' access to the table.
My objective is to abstract the table so that the user is not affected
by any future changes to the table. The user sees only the View which
I can modify if necessary to protect the user against any table
changes.
Gary
You can enable DB_CHAINING for those databases so that standard ownership
chaining applies to cross-database references as well. As long as the
ownership chain is unbroken, permissions on indirectly referenced objects
are not needed. Keep in mind that the databases must share the same owner
login if your objects are in the dbo schema because the database ownership
determines the dbo schema owner.
Users will still need a security context in the indirectly referenced
database, either directly or via the guest user. However, no object
permissions need to be granted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary Leighton" <leightongary@.hotmail.com> wrote in message
news:1180695366.627816.226130@.q69g2000hsb.googlegr oups.com...
> I'm trying to create a View in a SQL Server 2005 database such that it
> selects from a table in a different database on the same server.
> However, I cannot find a way to create a new user that only has
> 'Select' access to the view. It does work if I give a database user
> 'Select' permissions for both the View and the Table but I don't want
> to give 'Select' access to the table.
> My objective is to abstract the table so that the user is not affected
> by any future changes to the table. The user sees only the View which
> I can modify if necessary to protect the user against any table
> changes.
> Gary
>
|||> You need to give select permission on the table that the view usess, if
> you don't want the user to see whole columns value, then just give the
> select permission on column level
This is not true. Permissions on indirectly referenced objects are not
needed as long as the ownership chain is unbroken. However, there are
special considerations for cross-database references. See "ownership
chains" in the Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/762249ee-881a-4c3e-b8c0-3a9475039aca.htm).
Hope this helps.
Dan Guzman
SQL Server MVP
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:ecw8KEEpHHA.4512@.TK2MSFTNGP03.phx.gbl...
> HI,
> You need to give select permission on the table that the view usess, if
> you don't want the user to see whole columns value, then just give the
> select permission on column level
> regards
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Gary Leighton" <leightongary@.hotmail.com> wrote in message
> news:1180695366.627816.226130@.q69g2000hsb.googlegr oups.com...
>
selects from a table in a different database on the same server.
However, I cannot find a way to create a new user that only has
'Select' access to the view. It does work if I give a database user
'Select' permissions for both the View and the Table but I don't want
to give 'Select' access to the table.
My objective is to abstract the table so that the user is not affected
by any future changes to the table. The user sees only the View which
I can modify if necessary to protect the user against any table
changes.
Gary
You can enable DB_CHAINING for those databases so that standard ownership
chaining applies to cross-database references as well. As long as the
ownership chain is unbroken, permissions on indirectly referenced objects
are not needed. Keep in mind that the databases must share the same owner
login if your objects are in the dbo schema because the database ownership
determines the dbo schema owner.
Users will still need a security context in the indirectly referenced
database, either directly or via the guest user. However, no object
permissions need to be granted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary Leighton" <leightongary@.hotmail.com> wrote in message
news:1180695366.627816.226130@.q69g2000hsb.googlegr oups.com...
> I'm trying to create a View in a SQL Server 2005 database such that it
> selects from a table in a different database on the same server.
> However, I cannot find a way to create a new user that only has
> 'Select' access to the view. It does work if I give a database user
> 'Select' permissions for both the View and the Table but I don't want
> to give 'Select' access to the table.
> My objective is to abstract the table so that the user is not affected
> by any future changes to the table. The user sees only the View which
> I can modify if necessary to protect the user against any table
> changes.
> Gary
>
|||> You need to give select permission on the table that the view usess, if
> you don't want the user to see whole columns value, then just give the
> select permission on column level
This is not true. Permissions on indirectly referenced objects are not
needed as long as the ownership chain is unbroken. However, there are
special considerations for cross-database references. See "ownership
chains" in the Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/762249ee-881a-4c3e-b8c0-3a9475039aca.htm).
Hope this helps.
Dan Guzman
SQL Server MVP
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:ecw8KEEpHHA.4512@.TK2MSFTNGP03.phx.gbl...
> HI,
> You need to give select permission on the table that the view usess, if
> you don't want the user to see whole columns value, then just give the
> select permission on column level
> regards
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Gary Leighton" <leightongary@.hotmail.com> wrote in message
> news:1180695366.627816.226130@.q69g2000hsb.googlegr oups.com...
>
Permissions for a View on seperate database
I'm trying to create a View in a SQL Server 2005 database such that it
selects from a table in a different database on the same server.
However, I cannot find a way to create a new user that only has
'Select' access to the view. It does work if I give a database user
'Select' permissions for both the View and the Table but I don't want
to give 'Select' access to the table.
My objective is to abstract the table so that the user is not affected
by any future changes to the table. The user sees only the View which
I can modify if necessary to protect the user against any table
changes.
GaryHI,
You need to give select permission on the table that the view usess, if
you don't want the user to see whole columns value, then just give the
select permission on column level
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gary Leighton" <leightongary@.hotmail.com> wrote in message
news:1180695366.627816.226130@.q69g2000hsb.googlegroups.com...
> I'm trying to create a View in a SQL Server 2005 database such that it
> selects from a table in a different database on the same server.
> However, I cannot find a way to create a new user that only has
> 'Select' access to the view. It does work if I give a database user
> 'Select' permissions for both the View and the Table but I don't want
> to give 'Select' access to the table.
> My objective is to abstract the table so that the user is not affected
> by any future changes to the table. The user sees only the View which
> I can modify if necessary to protect the user against any table
> changes.
> Gary
>|||You can enable DB_CHAINING for those databases so that standard ownership
chaining applies to cross-database references as well. As long as the
ownership chain is unbroken, permissions on indirectly referenced objects
are not needed. Keep in mind that the databases must share the same owner
login if your objects are in the dbo schema because the database ownership
determines the dbo schema owner.
Users will still need a security context in the indirectly referenced
database, either directly or via the guest user. However, no object
permissions need to be granted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary Leighton" <leightongary@.hotmail.com> wrote in message
news:1180695366.627816.226130@.q69g2000hsb.googlegroups.com...
> I'm trying to create a View in a SQL Server 2005 database such that it
> selects from a table in a different database on the same server.
> However, I cannot find a way to create a new user that only has
> 'Select' access to the view. It does work if I give a database user
> 'Select' permissions for both the View and the Table but I don't want
> to give 'Select' access to the table.
> My objective is to abstract the table so that the user is not affected
> by any future changes to the table. The user sees only the View which
> I can modify if necessary to protect the user against any table
> changes.
> Gary
>|||> You need to give select permission on the table that the view usess, if
> you don't want the user to see whole columns value, then just give the
> select permission on column level
This is not true. Permissions on indirectly referenced objects are not
needed as long as the ownership chain is unbroken. However, there are
special considerations for cross-database references. See "ownership
chains" in the Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/762249ee-881a-4c3e-b8c0-3a9
475039aca.htm).
Hope this helps.
Dan Guzman
SQL Server MVP
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:ecw8KEEpHHA.4512@.TK2MSFTNGP03.phx.gbl...
> HI,
> You need to give select permission on the table that the view usess, if
> you don't want the user to see whole columns value, then just give the
> select permission on column level
> regards
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Gary Leighton" <leightongary@.hotmail.com> wrote in message
> news:1180695366.627816.226130@.q69g2000hsb.googlegroups.com...
>|||hi
Thanks for correcting me, Gray.. pls ignore my post...
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eobcnMEpHHA.3368@.TK2MSFTNGP02.phx.gbl...
> This is not true. Permissions on indirectly referenced objects are not
> needed as long as the ownership chain is unbroken. However, there are
> special considerations for cross-database references. See "ownership
> chains" in the Books Online
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/762249ee-881a-4c3e-b8c0-3
a9475039aca.htm).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:ecw8KEEpHHA.4512@.TK2MSFTNGP03.phx.gbl...
>
selects from a table in a different database on the same server.
However, I cannot find a way to create a new user that only has
'Select' access to the view. It does work if I give a database user
'Select' permissions for both the View and the Table but I don't want
to give 'Select' access to the table.
My objective is to abstract the table so that the user is not affected
by any future changes to the table. The user sees only the View which
I can modify if necessary to protect the user against any table
changes.
GaryHI,
You need to give select permission on the table that the view usess, if
you don't want the user to see whole columns value, then just give the
select permission on column level
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gary Leighton" <leightongary@.hotmail.com> wrote in message
news:1180695366.627816.226130@.q69g2000hsb.googlegroups.com...
> I'm trying to create a View in a SQL Server 2005 database such that it
> selects from a table in a different database on the same server.
> However, I cannot find a way to create a new user that only has
> 'Select' access to the view. It does work if I give a database user
> 'Select' permissions for both the View and the Table but I don't want
> to give 'Select' access to the table.
> My objective is to abstract the table so that the user is not affected
> by any future changes to the table. The user sees only the View which
> I can modify if necessary to protect the user against any table
> changes.
> Gary
>|||You can enable DB_CHAINING for those databases so that standard ownership
chaining applies to cross-database references as well. As long as the
ownership chain is unbroken, permissions on indirectly referenced objects
are not needed. Keep in mind that the databases must share the same owner
login if your objects are in the dbo schema because the database ownership
determines the dbo schema owner.
Users will still need a security context in the indirectly referenced
database, either directly or via the guest user. However, no object
permissions need to be granted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary Leighton" <leightongary@.hotmail.com> wrote in message
news:1180695366.627816.226130@.q69g2000hsb.googlegroups.com...
> I'm trying to create a View in a SQL Server 2005 database such that it
> selects from a table in a different database on the same server.
> However, I cannot find a way to create a new user that only has
> 'Select' access to the view. It does work if I give a database user
> 'Select' permissions for both the View and the Table but I don't want
> to give 'Select' access to the table.
> My objective is to abstract the table so that the user is not affected
> by any future changes to the table. The user sees only the View which
> I can modify if necessary to protect the user against any table
> changes.
> Gary
>|||> You need to give select permission on the table that the view usess, if
> you don't want the user to see whole columns value, then just give the
> select permission on column level
This is not true. Permissions on indirectly referenced objects are not
needed as long as the ownership chain is unbroken. However, there are
special considerations for cross-database references. See "ownership
chains" in the Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/762249ee-881a-4c3e-b8c0-3a9
475039aca.htm).
Hope this helps.
Dan Guzman
SQL Server MVP
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:ecw8KEEpHHA.4512@.TK2MSFTNGP03.phx.gbl...
> HI,
> You need to give select permission on the table that the view usess, if
> you don't want the user to see whole columns value, then just give the
> select permission on column level
> regards
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Gary Leighton" <leightongary@.hotmail.com> wrote in message
> news:1180695366.627816.226130@.q69g2000hsb.googlegroups.com...
>|||hi
Thanks for correcting me, Gray.. pls ignore my post...
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eobcnMEpHHA.3368@.TK2MSFTNGP02.phx.gbl...
> This is not true. Permissions on indirectly referenced objects are not
> needed as long as the ownership chain is unbroken. However, there are
> special considerations for cross-database references. See "ownership
> chains" in the Books Online
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/762249ee-881a-4c3e-b8c0-3
a9475039aca.htm).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:ecw8KEEpHHA.4512@.TK2MSFTNGP03.phx.gbl...
>
Subscribe to:
Posts (Atom)