Re: user defined Role - HELP

OK, can you get the list of logons from that Windows group? Are the users
in that Windows group members of other Windows groups that have been added
as logins to your SQL Server? If so, have these groups also been added to
the same DB as users? I'm wondering if there is a conflict, whereby a
person is a member of one group that has be3en granted permission and
another group that has been denied permission. The net effect is to deny.

Are you using dynamic SQL in the proc?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Charlie" <Charlie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D8CD9395-A7E9-4A6E-A999-DF8A4784EF9A@xxxxxxxxxxxxxxxx
I did give them specific rights to the underlying table (select, update &
delete) what else can I be missing? like I said, am totally stumped why it
wouldn't be working :-}

"Tom Moreau" wrote:

Are you using dynamic SQL within the proc? If so, they will need also
rights to the underlying table.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Charlie" <Charlie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B0A4EA02-61B2-4F75-A4A4-292EA380DCED@xxxxxxxxxxxxxxxx

Running 2000. I can't figure out why it is not working if I have given
the
exec rights on that sp.

"Tom Moreau" wrote:

They should not need any permissions on the table - unless you are using
dynamic SQL within the proc. Oh, are you using SQL 2000 or SQL 2005?
If
you are using SQL 2005, you can create the proc with EXEC AS and specify
a
user with elevated privileges (or the owner) and then you don't need
permissions on the underlying table.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Charlie" <Charlie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6FF180E7-10F4-423B-AA0E-0C62DE8D29A2@xxxxxxxxxxxxxxxx
Well, I have given them permission to exec the sp and gave them select,
delete and update on the table, but am still getting the same error
"user
does not have permission on tblUReport" am thoroughly stumped on why it
won't run.

"Tom Moreau" wrote:

Once the table has been created and the permissions set, you don't
have
to
reset them - even if you truncate the table. The SP shouldn't have to
do
anything with the permissions. Do they have access now?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Charlie" <Charlie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:421DB8AA-E754-4CE6-B52D-41B269DC3F5D@xxxxxxxxxxxxxxxx
Well now that is a very good question - lol. Ok, so changed the sp to
truncate instead of drop and recreate. Now what? The users I want to
access
this sp are part of a network group that is assigned as a r/w group in
sql
server. I do have another group that has the 3 users in it also (I
can't
get
my network guy to change this so am stuck). I created a role that
gives
the
permission to the group with three, but not sure how to set that in
the
sp.

Is this even the correct way to handle this? And do I use a new Role
or
an
Application Role?

Thank you!!!!

"Tom Moreau" wrote:

Having to drop and recreate a table all the time is a bad practice.
Why
can't you just truncate it? That said, once created and the
permissions
properly set, you can have the permissions scripted out via SSMS.
Reapply
those permissions each time the table is recreated.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Charlie" <Charlie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:673ABCF9-DC4A-4C48-A918-653707196418@xxxxxxxxxxxxxxxx
I have a stored procedure that requires dropping a table and then
recreating
it each time a user runs it. What / how do i set permissions to
allow
them
to run this one stored procedure????









.

Relevant Pages

  • Re: user defined Role - HELP
    ... I did give them specific rights to the underlying table (select, ... SQL Server MVP ... permissions on the underlying table. ...
    (microsoft.public.sqlserver.security)
  • Re: user defined Role - HELP
    ... Are you using dynamic SQL within the proc? ... SQL Server MVP ... permissions on the underlying table. ...
    (microsoft.public.sqlserver.security)
  • Re: Create table in schema
    ... SQL Server MVP ... Every time I try to grant control to the dds_pco_role, ... Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, ... I also got that when I ran grant alter on schema. ...
    (microsoft.public.sqlserver.security)
  • Re: Ownership Chain Issue
    ... SQL Server MVP ... > create a new database user and do not add them to any database or server ... I grant the new user select, update, insert and delete permissions ...
    (microsoft.public.sqlserver.security)
  • Re: Create table in schema
    ... "Tom Moreau" wrote: ... SQL Server MVP ... Every time I try to grant control to the dds_pco_role, ... I also got that when I ran grant alter on schema. ...
    (microsoft.public.sqlserver.security)