Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721

    Unanswered: Execute privileges on sp

    Hi. I'm trying to test something on a test db I have installed on my pc, but I am unable to process as I'm doing it. So, basically what I want is to give execute privilege on a procedure to a user, so the user can execute this procedure without having the privileges explicity granted on it (what this procedure do is to truncate a table on which the user has no access). As I've read, SQL Server stored procedures privileges runs with the definers permissions, not the one that is actually executing the procedure. So, what I'm doing is this: in query analyzer, logged in as sa, I did
    Code:
    use test
    
    create table t ( a integer )
    
    create procedure can_truncate as
    truncate table t
    
    sp_addlogin 'jmartinez',''
    
    sp_grantdbaccess 'jmartinez','jmartinez'
    
    grant execute on can_truncate to jmartinez
    Then I went to connect again, as jmartinez and did:
    Code:
    exec can_truncate
    and I get
    Code:
    Server: Msg 3704, Level 16, State 1, Procedure can_truncate, Line 2
    User does not have permission to perform this operation on table 't'.
    So, I wonder what more permissions would user jmartinez need in order to execute this procedure successfully. I hope you all understand what I am trying to achieve.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Shake things up a bit, and try:
    Code:
    use test
    GO
    sp_addlogin 'jmartinez',''
    
    sp_grantdbaccess 'jmartinez','jmartinez'
    GO
    create table t ( a integer )
    GO
    create procedure can_truncate as
    truncate table t
    RETURN
    GO
    
    grant execute on can_truncate to jmartinez
    -PatP

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I get the same exact error message using your method. Btw, I am using MSDE, this is what I get when I do SELECT @@VERSION:
    Code:
    Microsoft SQL Server  2000 - 8.00.194 (Intel X86)   Aug  6 2000 00:57:48   Copyright (c) 1988-2000 Microsoft Corporation  Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Reading through BOL, I found out this:
    Permissions
    TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
    I guess this mean I cannot transfer TRUNCATE TABLE privilege to a user, but since I am executing a procedure I created on my own ( as sa in this case ), woulnd't it have to run with my privileges instead of the user who is executing it ( in this case, jmartinez ) ?

  5. #5
    Join Date
    Jan 2006
    Posts
    10
    You need to run this code after granting rights:

    sp_addrolemember 'db_ddladmin', 'jmartinez'

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    But then I'm giving the user privileges to basically *destroy* my db..

  7. #7
    Join Date
    Jan 2006
    Posts
    10
    Hmm.. TRUNCATE TABLE is an operation that requires high priviledges as long as I know, so you will have to give a user them.

    By the way, why does user need to perform this? IMHO, You may create something like and administration panel which will login to database on its own and perform TRUNCATE TABLE instead of user - e.g. he selects a table from the combo box and then presses SUBMIT button, and then script connects as someone special with appropriate role membership and runs TRUNCATE TABLE.

  8. #8
    Join Date
    Dec 2003
    Posts
    61

    Previleges

    You might use
    Delete
    From <TableName>

  9. #9
    Join Date
    Jan 2006
    Posts
    10
    Quote Originally Posted by forXLDB
    You might use
    Delete
    From <TableName>
    The question is about
    what this procedure do is to truncate a table on which the user has no access
    LongByte [delta]studio rockz
    Life is a short way from /dev/zero to /dev/null.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •