Re: user defined Role - HELP

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: Column Level Permissions Security Issue
    ... role is granted SELECT rights as in "GRANT SELECT TO RWE", then a DENY ... When working with security in SQL Server it's imperative to understand ... discusses ownership chaining: http://www.sommarskog.se/dynamic_sql.html. ... there are users who have permission to access this ...
    (microsoft.public.sqlserver.security)
  • Re: SQL CLR Sproc isnt running right
    ... did you grant rights using the SQL Server 2005 Surface Area Config tool? ... and I got a permissions error back saying DBO doesn't have permission to insert records into the table... ... but the assemblie's stored procedures do all have execute permissions set for the role that the executing user is in... ... "William Vaughn" wrote in message ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Error !!! SQL XML Bulkload Please help
    ... Irwin Dolobowsky ... > The schedule job gets run by SQL server Agent and the SQL server agent is ... > which has also got the administrator permission. ... Is the scheduled job running as a different user? ...
    (microsoft.public.sqlserver.xml)
  • System.Security.SecurityException with .NET 2005 Only
    ... Crystal Report from a SQL Server 2000 table. ... Friend WithEvents scnSQLConnection As _ ... The dialog box suggests clicking on "Add Permission to the Project." ... Restart the debugging session, same problem again. ...
    (microsoft.public.dotnet.languages.vb)
  • After 2003 to 2005 Upgrade Wizard, Getting System.Security.SecurityException
    ... Crystal Report from a SQL Server 2000 table. ... Friend WithEvents scnSQLConnection As _ ... The dialog box suggests clicking on "Add Permission to the Project." ... Restart the debugging session, same problem again. ...
    (microsoft.public.dotnet.languages.vb)