Professional Web Applications Themes

Truncate Access - Microsoft SQL / MS SQL Server

Hi guys, How can one provide a user and/or role access to truncate selective tables. For example, how can I give user 'appnet' access to truncate only 'customer_base' table? Thanks, Ramandeep...

  1. #1

    Default Truncate Access

    Hi guys,

    How can one provide a user and/or role access to truncate selective tables.
    For example, how can I give user 'appnet' access to truncate only
    'customer_base' table?

    Thanks,

    Ramandeep


    Ramandeep Singh Guest

  2. #2

    Default Re: Truncate Access

    You can't. Only members of the sysadmin, db_owner and ddl_admin roles can
    use TRUNCATE TABLE and you can't give the permission to other roles or
    users. TRUNCATE TABLE is not something you should use in an application,
    that is why the permissions are severely restricted.

    If you can tell us some more about your design we can advice you on how to
    do it without having to tuncate tables. You can for example use temporary
    tables (starting with a #) instead of permanent tables in some situations.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Ramandeep Singh" <ramandeepuhhotmail.com> wrote in message
    news:#7oyKW#PDHA.3016TK2MSFTNGP10.phx.gbl...
    > Hi guys,
    >
    > How can one provide a user and/or role access to truncate selective
    tables.
    > For example, how can I give user 'appnet' access to truncate only
    > 'customer_base' table?
    >
    > Thanks,
    >
    > Ramandeep
    >
    >

    Jacco Schalkwijk Guest

  3. #3

    Default Re: Truncate Access

    Actully, I am using a DTS package that first loads all the data into a
    "staging" table. Before the data is loaded into this table I want to
    truncate any records just to be safe. I know I can use delete command--but
    that does row-by-row and the transaction logs gets hit for each.

    I've seen a way in Oracle where you creat a proc to allow trancating of
    tables and you give your selective users access to the proc to execute. I
    was hoping there is a similar way here with sql server.


    CREATING TRUNCATE PROCEDURE:
    ===========================

    1. Set serveroutput on in the glogin.sql script so the user can see the
    output of this procedure. This is an optional step. The user can also
    issue a 'set serveroutput on' interactively in sql*plus.

    2. Create the procedure under the user's schema. This procedure accepts 2
    arguments. Table_name is the name of the table to truncate. It must be
    owned
    by the owner of this procedure. Storage_type can be 'drop storage' or
    'reuse storage'.

    create or replace procedure truncate_table (
    table_name varchar2,
    storage_type varchar2)
    as
    crsor integer;
    rval integer;
    begin
    dbms_output.put_line('Truncating Table : '|| table_name ||
    ' Storage : '|| storage_type);
    crsor := dbms_sql.open_cursor;
    dbms_sql.p(crsor, 'truncate table '|| table_name ||
    ' '|| storage_type ,dbms_sql.v7);
    rval := dbms_sql.execute(crsor);
    dbms_sql.close_cursor(crsor);
    end;
    /

    If you wish the user to be able to truncate only a specific table, you can
    embed the table name in the procedure instead of giving the procedure
    executor
    the option. For example, lets say we only want the procedure grantee to
    truncate the emp table, the procedure would be created as:

    create or replace procedure truncate_emp (
    storage_type varchar2)
    as
    crsor integer;
    rval integer;
    begin
    dbms_output.put_line('Truncating Table : EMP' ||
    ' Storage : '|| storage_type);
    crsor := dbms_sql.open_cursor;
    dbms_sql.p(crsor, 'truncate table emp' ||
    ' '|| storage_type ,dbms_sql.v7);
    rval := dbms_sql.execute(crsor);
    dbms_sql.close_cursor(crsor);
    end;
    /

    3. Grant execute on this procedure to whoever needs truncate privileges
    on this user's tables.

    Grant execute on truncate_table/truncate_emp to <username, role>;

    EXECUTING TRUNCATE PROCEDURE:
    ============================

    Interactively:
    -------------

    The user granted this procedure can issue the following to truncate the
    table.

    Execute <owner_name>.truncate_table('<table_name>', '<storage_option>';

    example:

    SQL> execute scott.truncate_table('EMP', 'REUSE STORAGE');
    Truncating Table : EMP Storage : REUSE STORAGE

    PL/SQL procedure successfully completed.

    SQL> execute scott.truncate_emp ('drop storage');
    Truncating Table : EMP Storage : drop storage



    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:uigpqd%23PDHA.2636TK2MSFTNGP10.phx.gbl...
    > You can't. Only members of the sysadmin, db_owner and ddl_admin roles can
    > use TRUNCATE TABLE and you can't give the permission to other roles or
    > users. TRUNCATE TABLE is not something you should use in an application,
    > that is why the permissions are severely restricted.
    >
    > If you can tell us some more about your design we can advice you on how to
    > do it without having to tuncate tables. You can for example use temporary
    > tables (starting with a #) instead of permanent tables in some situations.
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Ramandeep Singh" <ramandeepuhhotmail.com> wrote in message
    > news:#7oyKW#PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > Hi guys,
    > >
    > > How can one provide a user and/or role access to truncate selective
    > tables.
    > > For example, how can I give user 'appnet' access to truncate only
    > > 'customer_base' table?
    > >
    > > Thanks,
    > >
    > > Ramandeep
    > >
    > >
    >
    >

    Ramandeep Singh Guest

  4. #4

    Default Re: Truncate Access

    Nope, there is no such way in SQL Server. You can run the DTS package under
    a login with sufficient permissions though.


    "Ramandeep Singh" <ramandeepuhhotmail.com> wrote in message
    news:O3MvTq%23PDHA.3016TK2MSFTNGP10.phx.gbl...
    > Actully, I am using a DTS package that first loads all the data into a
    > "staging" table. Before the data is loaded into this table I want to
    > truncate any records just to be safe. I know I can use delete
    command--but
    > that does row-by-row and the transaction logs gets hit for each.
    >
    > I've seen a way in Oracle where you creat a proc to allow trancating of
    > tables and you give your selective users access to the proc to execute. I
    > was hoping there is a similar way here with sql server.
    >
    >
    > CREATING TRUNCATE PROCEDURE:
    > ===========================
    >
    > 1. Set serveroutput on in the glogin.sql script so the user can see the
    > output of this procedure. This is an optional step. The user can also
    > issue a 'set serveroutput on' interactively in sql*plus.
    >
    > 2. Create the procedure under the user's schema. This procedure accepts 2
    > arguments. Table_name is the name of the table to truncate. It must be
    > owned
    > by the owner of this procedure. Storage_type can be 'drop storage' or
    > 'reuse storage'.
    >
    > create or replace procedure truncate_table (
    > table_name varchar2,
    > storage_type varchar2)
    > as
    > crsor integer;
    > rval integer;
    > begin
    > dbms_output.put_line('Truncating Table : '|| table_name ||
    > ' Storage : '|| storage_type);
    > crsor := dbms_sql.open_cursor;
    > dbms_sql.p(crsor, 'truncate table '|| table_name ||
    > ' '|| storage_type ,dbms_sql.v7);
    > rval := dbms_sql.execute(crsor);
    > dbms_sql.close_cursor(crsor);
    > end;
    > /
    >
    > If you wish the user to be able to truncate only a specific table, you can
    > embed the table name in the procedure instead of giving the procedure
    > executor
    > the option. For example, lets say we only want the procedure grantee to
    > truncate the emp table, the procedure would be created as:
    >
    > create or replace procedure truncate_emp (
    > storage_type varchar2)
    > as
    > crsor integer;
    > rval integer;
    > begin
    > dbms_output.put_line('Truncating Table : EMP' ||
    > ' Storage : '|| storage_type);
    > crsor := dbms_sql.open_cursor;
    > dbms_sql.p(crsor, 'truncate table emp' ||
    > ' '|| storage_type ,dbms_sql.v7);
    > rval := dbms_sql.execute(crsor);
    > dbms_sql.close_cursor(crsor);
    > end;
    > /
    >
    > 3. Grant execute on this procedure to whoever needs truncate privileges
    > on this user's tables.
    >
    > Grant execute on truncate_table/truncate_emp to <username, role>;
    >
    > EXECUTING TRUNCATE PROCEDURE:
    > ============================
    >
    > Interactively:
    > -------------
    >
    > The user granted this procedure can issue the following to truncate the
    > table.
    >
    > Execute <owner_name>.truncate_table('<table_name>', '<storage_option>';
    >
    > example:
    >
    > SQL> execute scott.truncate_table('EMP', 'REUSE STORAGE');
    > Truncating Table : EMP Storage : REUSE STORAGE
    >
    > PL/SQL procedure successfully completed.
    >
    > SQL> execute scott.truncate_emp ('drop storage');
    > Truncating Table : EMP Storage : drop storage
    >
    >
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:uigpqd%23PDHA.2636TK2MSFTNGP10.phx.gbl...
    > > You can't. Only members of the sysadmin, db_owner and ddl_admin roles
    can
    > > use TRUNCATE TABLE and you can't give the permission to other roles or
    > > users. TRUNCATE TABLE is not something you should use in an application,
    > > that is why the permissions are severely restricted.
    > >
    > > If you can tell us some more about your design we can advice you on how
    to
    > > do it without having to tuncate tables. You can for example use
    temporary
    > > tables (starting with a #) instead of permanent tables in some
    situations.
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Ramandeep Singh" <ramandeepuhhotmail.com> wrote in message
    > > news:#7oyKW#PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > > Hi guys,
    > > >
    > > > How can one provide a user and/or role access to truncate selective
    > > tables.
    > > > For example, how can I give user 'appnet' access to truncate only
    > > > 'customer_base' table?
    > > >
    > > > Thanks,
    > > >
    > > > Ramandeep
    > > >
    > > >
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

Similar Threads

  1. How to truncate fields (newbie)
    By Josť Carlos Santos in forum MySQL
    Replies: 3
    Last Post: May 1st, 05:02 PM
  2. PHP Truncate Text
    By Energize in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 2
    Last Post: August 11th, 02:07 AM
  3. Truncate text in column
    By Joe in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: December 10th, 01:41 PM
  4. Truncate generated mails...
    By Ray at in forum ASP
    Replies: 1
    Last Post: September 19th, 06:41 PM
  5. syslog truncate
    By Sinisa in forum SCO
    Replies: 3
    Last Post: August 1st, 01:24 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139