Re: user defined Role - HELP


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: one SQL DB getting info from another - user setup?
    ... It appears from your narrative that the Main and DB1 databases ... EXEC sp_helpdb 'Main' ... SQL 2000 SP3 and is turned off by default. ... > In the permissions for Main, USER1 and USER2 are listed, but have NO ...
    (microsoft.public.sqlserver.security)
  • Re: SQL CE Synching Problems
    ... Have you granted IUSER_ServerName access to your publication within SQL ... It looks like the permissions problem is getting access to the publication. ... so the issue has to be between the server tools and the publisher. ... > A request to send data to the computer running IIS has failed. ...
    (microsoft.public.sqlserver.ce)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)