Re: user defined Role - HELP

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: Truncate Table vs. Delete From
    ... TRUNCATE TABLE permissions default to the table owner, ... of the sysadmin fixed server role, ...
    (microsoft.public.sqlserver.security)
  • Permissions for executing a stored procedure
    ... Truncate table permissions only default to table owner, ... So, eventhough, you have granted execute permissions to ... Instead using TRUNCATE TABLE, you could use DELETE FROM, ... >We have a stored procedure that has code in it to ...
    (microsoft.public.sqlserver.security)
  • Re: With in a SP Truncate dbo.table table-name permissions..
    ... TRUNCATE TABLE permissions default to the table owner, ... According to sql server ... If a user who creates a stored procedure does ...
    (microsoft.public.sqlserver.security)
  • Re: Truncate Table vs. Delete From
    ... permissions (SELECT, INSERT, UPDATE, DELETE, EXECUTE and REFERENCES). ... TRUNCATE TABLE) are different. ...
    (microsoft.public.sqlserver.security)
  • Truncate Table vs. Delete From
    ... If you want to delete all the rows in a table, TRUNCATE ... Delete permissions default to members of the sysadmin ... permissions on a Stored Procedure overrides any ... Truncate Table command. ...
    (microsoft.public.sqlserver.security)