Re: user defined Role - HELP

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
    ... 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
    ... Tom Moreau writes: ... the GRANT will be for DELETE. ... Grant ALTER TABLE on tblReportNums to 'UM Case Mgmt' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: user defined Role - HELP
    ... Tom Moreau writes: ... Tom, for DELETE he does not need to GRANT, the procedure takes care of that. ... Grant ALTER TABLE on tblReportNums to 'UM Case Mgmt' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (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)