Re: user defined Role - HELP

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: PRIVILEGE PROBLEMS USING EXECUTE IMMEDIATE!
    ... >>> Michel Cadot ... > The permissions are a direct grant to the SCHEMA2 user. ... > SQL> grant alter,insert, update, delete, select ON TESTTAB to scott2; ... > PL/SQL procedure successfully completed. ...
    (comp.databases.oracle.server)
  • Re: all operation not work (insert delete update) / maybepermissionproblem
    ... The database server cannot find a table or view specified in the statement. ... trying to grant USAGE privileges on a user-defined type. ... Betreff: Re: all operation not work / maybe permissionproblem ... I called this command just you are told me: onstat -g sql and I got the following: ...
    (comp.databases.informix)
  • Re: SQL connection
    ... >>> I need to use user authentication, ... Maybe set in SQL ... >> privileges both on the instance (grant login) and on the db and relative ...
    (microsoft.public.vb.database)
  • Re: a user to use only import
    ... At system user ... ligne créée. ... SQL> grant create session to batch; ...
    (comp.databases.oracle.server)
  • Re: PRIVILEGE PROBLEMS USING EXECUTE IMMEDIATE!
    ... >> Michel Cadot ... The permissions are a direct grant to the SCHEMA2 user. ... SQL> grant alter,insert, update, delete, select ON TESTTAB to scott2; ... SQL> create or replace procedure DoIt IS ...
    (comp.databases.oracle.server)