Re: user defined Role - HELP

It appeared that he was using dynamic SQL; that's why I thought he needed to
grant 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


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9A2260C6C2DF4Yazorman@xxxxxxxxxxxx
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: 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: 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: Newbie to security
    ... Use sp_grantdbaccess to grant access to the database. ... databases when you are new to security. ... Microsoft SQL Server 2000 SP3 Security Features and Best ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server Express
    ... GRANT them permissions to do so with the GRANT command. ... GRANT CREATE DATABASE on DATABASE::xxx to SAM ... Yes, I am very familiar with configuring specific SQL Server accounts for access, and I am having no real problems in this area. ... really a good way to block administrator access without making your database pretty hard to administer. ...
    (microsoft.public.sqlserver.msde)
  • 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)