Permissions

Hi:
Actually I have a store proc which pulls data from a table. I have given
execute permission to a user on this store proc. Since owner of store proc
and table are the same (dbo), I do not need to give select permission on the
table for this user.
But i am having problem when I use dynamic sql to build a query string and
execute this sql. It then gives an error that permission denied on this
table for this user. I think It starts a new session when you build a
querystring (Select * from Table), it checks for permission on that table
because the ownership chain breaks.
If you know of any workaround to this problem, please let me know becuase I
do not want to give select permission for this user. It should be executed
thru the stored proc.
ThanksYou will need to re-write the stored procedure to not use dynamic SQL,
which requires the user to have permissions on the base tables.
--Mary
On Thu, 12 Aug 2004 14:09:03 -0700, Sal
<Sal@.discussions.microsoft.com> wrote:

>Hi:
>Actually I have a store proc which pulls data from a table. I have given
>execute permission to a user on this store proc. Since owner of store proc
>and table are the same (dbo), I do not need to give select permission on th
e
>table for this user.
>But i am having problem when I use dynamic sql to build a query string and
>execute this sql. It then gives an error that permission denied on this
>table for this user. I think It starts a new session when you build a
>querystring (Select * from Table), it checks for permission on that table
>because the ownership chain breaks.
>If you know of any workaround to this problem, please let me know becuase I
>do not want to give select permission for this user. It should be executed
>thru the stored proc.
>Thanks|||Sal,
There is one work-around I have found. If you are trying to insert
data into a permanent table via dynamic sql, first create a temporary
table with the same structure as the destination permenant table.
Insert into the temporary table via the dynamic statement.
Then, with non-dynamic sql, insert from the temp table into the
permanent table.
Hope this helps.
Sal wrote:
> *Hi:
> Actually I have a store proc which pulls data from a table. I have
> given
> execute permission to a user on this store proc. Since owner of
> store proc
> and table are the same (dbo), I do not need to give select permission
> on the
> table for this user.
> But i am having problem when I use dynamic sql to build a query
> string and
> execute this sql. It then gives an error that permission denied on
> this
> table for this user. I think It starts a new session when you build
> a
> querystring (Select * from Table), it checks for permission on that
> table
> because the ownership chain breaks.
> If you know of any workaround to this problem, please let me know
> becuase I
> do not want to give select permission for this user. It should be
> executed
> thru the stored proc.
> Thanks *
lxstewart
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message952424.html

Permission VS Owner

Why every securable has an owner?
A securable's owner is some one has full control over the securable.
so the term "owner" can be replaced by some one who has full control
over securable.Hi Benny
That may usually be the initial case, but then you can also deny permissions
to the owner so they don't have full control. Others can also be granted
CONTROL permission that gives them owner-like permissions, but they are not
the owner.
John
"Benny" wrote:
> Why every securable has an owner?
> A securable's owner is some one has full control over the securable.
> so the term "owner" can be replaced by some one who has full control
> over securable.
>|||Thanks John.
If permission can handle security thing, why SQL Server provide
object's owner?
Sql Server can works well with permission and without owner.
On Dec 23, 7:33 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Benny
> That may usually be the initial case, but then you can also deny permissions
> to the owner so they don't have full control. Others can also be granted
> CONTROL permission that gives them owner-like permissions, but they are not
> the owner.
> John
>
> "Benny" wrote:
> > Why every securable has an owner?
> > A securable's owner is some one has full control over the securable.
> > so the term "owner" can be replaced by some one who has full control
> > over securable.- Hide quoted text -- Show quoted text -|||Hi Benny
I can't say for certain, and someone else may be able to give you a better
reply... but ownership tends to be more to do with management of objects than
security.
John
"Benny" wrote:
> Thanks John.
> If permission can handle security thing, why SQL Server provide
> object's owner?
> Sql Server can works well with permission and without owner.
> On Dec 23, 7:33 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi Benny
> >
> > That may usually be the initial case, but then you can also deny permissions
> > to the owner so they don't have full control. Others can also be granted
> > CONTROL permission that gives them owner-like permissions, but they are not
> > the owner.
> >
> > John
> >
> >
> >
> > "Benny" wrote:
> > > Why every securable has an owner?
> > > A securable's owner is some one has full control over the securable.
> > > so the term "owner" can be replaced by some one who has full control
> > > over securable.- Hide quoted text -- Show quoted text -
>|||This is true. On SQL-Server 2000, you can have multiple objects with the
same name but with different owners; for example: dbo.table1, user1.table1,
user2.table1, ... When the owner (user) of an object is the same as the
owner of the database then dbo. is used. Also, only the database owner or
the object owner can define or change the permissions of an object; for
exemple, only dbo or user1 can change the permissions of the object
user1.table1 while user2 cannot.
For SQL-Server 2000, the owner of an object is also called the schema, to be
on par with other sql-servers (Oracle for exemple). However, this is not a
true sql schema because it can't be hierarchical.
On SQL-Server 2005, with have true schemas because not only they can be
hierarchical but also the name of the schema is not necessarily the same as
the name of the owner.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:390699C8-1F0E-4564-BDDC-CC29AB7A5E10@.microsoft.com...
> Hi Benny
> I can't say for certain, and someone else may be able to give you a better
> reply... but ownership tends to be more to do with management of objects
> than
> security.
> John
> "Benny" wrote:
>> Thanks John.
>> If permission can handle security thing, why SQL Server provide
>> object's owner?
>> Sql Server can works well with permission and without owner.
>> On Dec 23, 7:33 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>> > Hi Benny
>> >
>> > That may usually be the initial case, but then you can also deny
>> > permissions
>> > to the owner so they don't have full control. Others can also be
>> > granted
>> > CONTROL permission that gives them owner-like permissions, but they are
>> > not
>> > the owner.
>> >
>> > John
>> >
>> >
>> >
>> > "Benny" wrote:
>> > > Why every securable has an owner?
>> > > A securable's owner is some one has full control over the securable.
>> > > so the term "owner" can be replaced by some one who has full control
>> > > over securable.- Hide quoted text -- Show quoted text -
>>|||I still can not understand why ownership exist in SQL Server
Sylvain Lafontaine (fill the blanks, no spam please) wrote:
> This is true. On SQL-Server 2000, you can have multiple objects with the
> same name but with different owners; for example: dbo.table1, user1.table1,
> user2.table1, ... When the owner (user) of an object is the same as the
> owner of the database then dbo. is used. Also, only the database owner or
> the object owner can define or change the permissions of an object; for
> exemple, only dbo or user1 can change the permissions of the object
> user1.table1 while user2 cannot.
> For SQL-Server 2000, the owner of an object is also called the schema, to be
> on par with other sql-servers (Oracle for exemple). However, this is not a
> true sql schema because it can't be hierarchical.
> On SQL-Server 2005, with have true schemas because not only they can be
> hierarchical but also the name of the schema is not necessarily the same as
> the name of the owner.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:390699C8-1F0E-4564-BDDC-CC29AB7A5E10@.microsoft.com...
> > Hi Benny
> >
> > I can't say for certain, and someone else may be able to give you a better
> > reply... but ownership tends to be more to do with management of objects
> > than
> > security.
> >
> > John
> >
> > "Benny" wrote:
> >
> >> Thanks John.
> >> If permission can handle security thing, why SQL Server provide
> >> object's owner?
> >> Sql Server can works well with permission and without owner.
> >>
> >> On Dec 23, 7:33 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> >> > Hi Benny
> >> >
> >> > That may usually be the initial case, but then you can also deny
> >> > permissions
> >> > to the owner so they don't have full control. Others can also be
> >> > granted
> >> > CONTROL permission that gives them owner-like permissions, but they are
> >> > not
> >> > the owner.
> >> >
> >> > John
> >> >
> >> >
> >> >
> >> > "Benny" wrote:
> >> > > Why every securable has an owner?
> >> > > A securable's owner is some one has full control over the securable.
> >> > > so the term "owner" can be replaced by some one who has full control
> >> > > over securable.- Hide quoted text -- Show quoted text -
> >>
> >>|||This is for giving the possibility for two different users to access two
different sets of data from within the same application: if multiple objects
with the same name exists and you don't specify the schema (or owner), SQL
Server will first search for an object belonging to the user and if it don't
find one, for an object belonging to the owner of the database (dbo.).
For example, take the case where three views exists with the same name:
UserA.View1, UserB.View1 and dbo.View1. If an application ask for View1 but
without making an explicit reference to the the ownership, SQL-Server will
automatically assign the use of UserA.View1 for user UserA; UserB.View1 for
UserB and dbo.View1 for all other users.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1167011614.441378.271850@.42g2000cwt.googlegroups.com...
>I still can not understand why ownership exist in SQL Server
> Sylvain Lafontaine (fill the blanks, no spam please) wrote:
>> This is true. On SQL-Server 2000, you can have multiple objects with the
>> same name but with different owners; for example: dbo.table1,
>> user1.table1,
>> user2.table1, ... When the owner (user) of an object is the same as the
>> owner of the database then dbo. is used. Also, only the database owner
>> or
>> the object owner can define or change the permissions of an object; for
>> exemple, only dbo or user1 can change the permissions of the object
>> user1.table1 while user2 cannot.
>> For SQL-Server 2000, the owner of an object is also called the schema, to
>> be
>> on par with other sql-servers (Oracle for exemple). However, this is not
>> a
>> true sql schema because it can't be hierarchical.
>> On SQL-Server 2005, with have true schemas because not only they can be
>> hierarchical but also the name of the schema is not necessarily the same
>> as
>> the name of the owner.
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:390699C8-1F0E-4564-BDDC-CC29AB7A5E10@.microsoft.com...
>> > Hi Benny
>> >
>> > I can't say for certain, and someone else may be able to give you a
>> > better
>> > reply... but ownership tends to be more to do with management of
>> > objects
>> > than
>> > security.
>> >
>> > John
>> >
>> > "Benny" wrote:
>> >
>> >> Thanks John.
>> >> If permission can handle security thing, why SQL Server provide
>> >> object's owner?
>> >> Sql Server can works well with permission and without owner.
>> >>
>> >> On Dec 23, 7:33 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>> >> > Hi Benny
>> >> >
>> >> > That may usually be the initial case, but then you can also deny
>> >> > permissions
>> >> > to the owner so they don't have full control. Others can also be
>> >> > granted
>> >> > CONTROL permission that gives them owner-like permissions, but they
>> >> > are
>> >> > not
>> >> > the owner.
>> >> >
>> >> > John
>> >> >
>> >> >
>> >> >
>> >> > "Benny" wrote:
>> >> > > Why every securable has an owner?
>> >> > > A securable's owner is some one has full control over the
>> >> > > securable.
>> >> > > so the term "owner" can be replaced by some one who has full
>> >> > > control
>> >> > > over securable.- Hide quoted text -- Show quoted text -
>> >>
>> >>
>

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
QuentinAlthough you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
>|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
permissions[vbcol=seagreen]
can[vbcol=seagreen]
>|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...
tables.[vbcol=seagreen]
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
tables.[vbcol=seagreen]
> the
> permissions
> can
>

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
Quentin
Although you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>
|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
>
|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
permissions[vbcol=seagreen]
can
>
|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
tables.[vbcol=seagreen]
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
tables.
> the
> permissions
> can
>

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
QuentinAlthough you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > Hi group,
> >
> > is there a way to create a role, assign permission to it so that it can
> > create procedures with dbo as owner, and not able to drop tables?
> >
> > Quentin
> >
> >
>|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > Although you can't create a user role for this, you can add a user to
the
> > db_ddladmin fixed database role and then DENY those statement
permissions
> > you don't want the user to have For example:
> >
> > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > DENY CREATE TABLE TO MyUser1
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Quentin Ran" <ab@.who.com> wrote in message
> > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > Hi group,
> > >
> > > is there a way to create a role, assign permission to it so that it
can
> > > create procedures with dbo as owner, and not able to drop tables?
> > >
> > > Quentin
> > >
> > >
> >
> >
>|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> > thanks for the response. I have been there -- you can still drop
tables.
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> > Dan,
> >
> > thanks for the response. I have been there -- you can still drop
tables.
> >
> > Quentin
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > > Although you can't create a user role for this, you can add a user to
> the
> > > db_ddladmin fixed database role and then DENY those statement
> permissions
> > > you don't want the user to have For example:
> > >
> > > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > > DENY CREATE TABLE TO MyUser1
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Quentin Ran" <ab@.who.com> wrote in message
> > > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > > Hi group,
> > > >
> > > > is there a way to create a role, assign permission to it so that it
> can
> > > > create procedures with dbo as owner, and not able to drop tables?
> > > >
> > > > Quentin
> > > >
> > > >
> > >
> > >
> >
> >
>