Results 1 to 5 of 5

Thread: Sql 2000 permissions,HELP ME!!!

  1. #1
    Join Date
    Oct 2005
    Posts
    6

    Sql 2000 permissions,HELP ME!!!

    Hi guy,this is my problem:
    user Sam has permission only like db_writer,db_reader and another, test, customizable. He isn't a ddladmin or db owner. Now, in the db, there are object only with dbo as owner, and this is the way that have to remain. But in the application there are a lot of statements (like truncate or alter table) that need higher permissions to be executed, higher Sam's permissions. I read that in sql 2005 it is possible with EXECUTE AS command on a s.procedure, but it seems that in sql 2000 there is any way to do a similiar operation. So, my question is:
    is there a procedure that grant permission to Sam to manipulate dbo.object (truncate or alter table)?
    Please answer me in simple english...i'm italian guy.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    No, you need to grant ddl_admin.

  3. #3
    Join Date
    Oct 2005
    Posts
    6

    Re to Skhanal

    But in any way???not even with application roles or an external application??
    Moreover, i can substitude the truncate table command with delete *, but the transaction log slow my apllication (more over 1 day...), let alone all triggers that starts after delete command...Is there a parameter or command that deletes all rows of a table without pass to transact log???Thanks

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    This is from book online

    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.

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    Deleting is logged process, you can change db recovery model to bulk-logged to reduce log. But it requires db_owner rights to change db recovery model, higher than db_ddladmin.

Posting Permissions

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