Re: user defined Role - HELP

Tom Moreau (tom@xxxxxxxxxxxxxxxxxxxx) writes:
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, for DELETE he does not need to GRANT, the procedure takes care of that.

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

Grant ALTER TABLE on tblReportNums to 'UM Case Mgmt'

No, that's wrong. The correct command would be

GRANT ALTER ON tblReportNums TO [UM Case Mgmt]

Two errors: 1) You don't grant ALTER TABLE on an object. In fact ALTER TABLE
is not any permission at all. 2) The role name should not be in single
quotes as it is an identifier. Since it includes spaces, the name needs to
be quoted, but identifiers are quoted with brackets or double quotes.
--
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
    ... 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: Create table in schema
    ... I also got that when I ran grant alter on schema. ... A_Role and want to assign all the permissions to the role. ...
    (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: Create table in schema
    ... "Tom Moreau" wrote: ... SQL Server MVP ... Every time I try to grant control to the dds_pco_role, ... I also got that when I ran grant alter on schema. ...
    (microsoft.public.sqlserver.security)