Re: user defined Role - HELP

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
    ... SQL Server MVP ... 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. ...
    (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)
  • 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)
  • Re: Problem altering table and adding a default
    ... SQL Server does not allow that - you can only add nullable ... > After you add a new column in Enterprise Manager, uncheck Allow Nulls, ... the ALTER TABLE statement above will pass to the SQL Server ... > 4) Start Profiler and perform a new trace. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Express - Identity specification property - how to change
    ... UPDATE tbl ... You can use ALTER TABLE ALTER COLUMN to add an IDENTITY column, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)