Monday, March 12, 2012

permissions for new user to use stored procedure

Using SS2000 SP4. If I have a table and revoke select, insert, update and
delete to public on the table. Then create a new user and don't give them an
y
permissions to the table. If I create stored procedures to select from,
insert into, update and delete from the table do I only have to give execute
permissions on the stored procedure to the new user for the user to be able
to execute those stored procedures? Am I correct in saying that the new user
doesn't have to have any kind of permissions on the table itself?
Thanks,
--
Dan D.That's correct. Indeed, you didn't need to revoke or deny anything on the
underlying tables, since a user has no permissions on an object by default.
All you have to do is grant EXEC permission on the proc.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D2083411-5A89-4F04-B1A5-373403FE75EC@.microsoft.com...
Using SS2000 SP4. If I have a table and revoke select, insert, update and
delete to public on the table. Then create a new user and don't give them
any
permissions to the table. If I create stored procedures to select from,
insert into, update and delete from the table do I only have to give execute
permissions on the stored procedure to the new user for the user to be able
to execute those stored procedures? Am I correct in saying that the new user
doesn't have to have any kind of permissions on the table itself?
Thanks,
--
Dan D.|||Wouldn't the user have permissions on the table because they are in the
'public' role by default?
Also, is the same true with views? If the user has select permission to the
view they don't need select on the underlying table?
Thanks,
--
Dan D.
"Tom Moreau" wrote:

> That's correct. Indeed, you didn't need to revoke or deny anything on the
> underlying tables, since a user has no permissions on an object by default
.
> All you have to do is grant EXEC permission on the proc.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D2083411-5A89-4F04-B1A5-373403FE75EC@.microsoft.com...
> Using SS2000 SP4. If I have a table and revoke select, insert, update and
> delete to public on the table. Then create a new user and don't give them
> any
> permissions to the table. If I create stored procedures to select from,
> insert into, update and delete from the table do I only have to give execu
te
> permissions on the stored procedure to the new user for the user to be abl
e
> to execute those stored procedures? Am I correct in saying that the new us
er
> doesn't have to have any kind of permissions on the table itself?
> Thanks,
> --
> Dan D.
>|||Let's say you create a table. By default, there are no permissions on the
table - even to the public role. Same goes for any other object.
You can grant permission on a view without granting permission on the
underlying tables. (This gets messed up if you're not the owner of the
underlying tables, though.)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:EC40014C-0943-4CC3-9FDD-BFE610495BFD@.microsoft.com...
Wouldn't the user have permissions on the table because they are in the
'public' role by default?
Also, is the same true with views? If the user has select permission to the
view they don't need select on the underlying table?
Thanks,
--
Dan D.
"Tom Moreau" wrote:

> That's correct. Indeed, you didn't need to revoke or deny anything on the
> underlying tables, since a user has no permissions on an object by
> default.
> All you have to do is grant EXEC permission on the proc.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D2083411-5A89-4F04-B1A5-373403FE75EC@.microsoft.com...
> Using SS2000 SP4. If I have a table and revoke select, insert, update and
> delete to public on the table. Then create a new user and don't give them
> any
> permissions to the table. If I create stored procedures to select from,
> insert into, update and delete from the table do I only have to give
> execute
> permissions on the stored procedure to the new user for the user to be
> able
> to execute those stored procedures? Am I correct in saying that the new
> user
> doesn't have to have any kind of permissions on the table itself?
> Thanks,
> --
> Dan D.
>|||I understand. Thanks.
--
Dan D.
"Tom Moreau" wrote:

> Let's say you create a table. By default, there are no permissions on the
> table - even to the public role. Same goes for any other object.
> You can grant permission on a view without granting permission on the
> underlying tables. (This gets messed up if you're not the owner of the
> underlying tables, though.)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:EC40014C-0943-4CC3-9FDD-BFE610495BFD@.microsoft.com...
> Wouldn't the user have permissions on the table because they are in the
> 'public' role by default?
> Also, is the same true with views? If the user has select permission to th
e
> view they don't need select on the underlying table?
> Thanks,
> --
> Dan D.
>
> "Tom Moreau" wrote:
>
>

No comments:

Post a Comment