Re: user defined Role - HELP

FINALLY got it to work, not crazy about the way it has to be done, but i am
tired of messing with it at this point.

THANK YOU SO MUCH for all your help.

"Tom Moreau" wrote:

I'm afraid that you will have to go with using DELETE instead of TRUNCATE
TABLE to remove the data. Consequently, the GRANT will be for DELETE.

--
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:D853E5C2-7CB7-4181-A938-6B96C0D92969@xxxxxxxxxxxxxxxx
I get an error "Privilege ALTER TABLE may not be granted or revoked.

"Tom Moreau" wrote:

Don't put it in the SP. Just do the GRANT as follows:

Grant ALTER TABLE on tblReportNums to 'UM Case Mgmt'


--
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:AE2280AC-C8F9-4777-B699-99D0CF93184F@xxxxxxxxxxxxxxxx
Ok, apparently I can't even set the Grant statement correct (probably due
to
fact that am extremely irritated with this at this point). What am I
doing
wrong and exactly where in the stored procedure should I put it?

Grant ALTER on tblReportNums to 'UM Case Mgmt'

"Erland Sommarskog" wrote:

Charlie (Charlie@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
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.

This is because ownership chaining does not apply to TRUNCATE TABLE. To
perform TRUNCATE TABLE, the user needs to have ALTER permission on the
table, even if it's packaged in a stored procedure. The only permissions
that can be transferred through stored procedure ownership are SELECT,
DELETE, INSERT, UPDATE and EXECUTE.

Had you been on SQL 2005, this could easily have been addressed by
certificate signing, but since you are on SQL 2000, there are only two
options, and none of them are appetising:

o Grant the role ALTER permission on the table.
o Change TRUNCATE to DELETE. (And pay the price in performance.)

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx





.

Relevant Pages

  • Re: user defined Role - HELP
    ... I get an error "Privilege ALTER TABLE may not be granted or revoked. ... Grant ALTER TABLE on tblReportNums to 'UM Case Mgmt' ... Grant the role ALTER permission on the table. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: user defined Role - HELP
    ... Grant ALTER TABLE on tblReportNums to 'UM Case Mgmt' ... Grant the role ALTER permission on the table. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • How to GRANT *all* permissions to *all* users?
    ... permission to do anything ... I'm asking because I recently converted from SQL Server 2000 to 2005 ... GRANT VIEW ANY DEFINITION TO public ... But I still can't ALTER tables. ...
    (microsoft.public.sqlserver.security)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Problem altering table and adding a default
    ... ALTER TABLE dbo.Test_tbl ADD Notnull_col1 charNOT NULL ... SQL Server does not allow that - you can only add nullable columns. ... After you add a new column in Enterprise Manager, uncheck Allow Nulls, bind the uder- ... Start Profiler and perform a new trace. ...
    (microsoft.public.sqlserver.server)