Post reply

Assigning Permissions

  • July 21, 2004 at 8:12 pm

    #108459

    Hi there,

    I am pretty new to SQL Server and was wondering if it is possible to assign SELECT, INSERT, DELETE, UPDATE etc, permissions using a script?

    We are converting Clarion tables to SQL and when they arrive in SQL they don't have any permissions set and I don't fancy going through each table individually!

    Any help would be appreciated.

     

    Regards

    Brendan

  • July 21, 2004 at 8:34 pm

    #515564

    I believe you are looking for the GRANT command.  See "Books on Line" for details.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 21, 2004 at 10:09 pm

    #515575

    Am I doing something wrong?  The command says it's working but it's not changing the permissions for me..

     

    select 'Grant select, insert, update , delete on '

  • July 22, 2004 at 8:33 pm

    #515785

    The systax of the GRANT command should be as follows:

    GRANT permissiontype ON sqlobject TO grouporrole

    See "Books on Line" under "GRANT, GRANT (described)" for a complete list...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 23, 2004 at 7:22 am

    #515870

    To keep from executing the grants against all the individual tables, point to your database in Query Analyzer and run this:

    SELECT

    'GRANT SELECT,INSERT,UPDATE,DELETE ON ' + NAME + ' TO xxxxxx;'

    FROM SYSOBJECTS

    WHERE XTYPE = 'U'

    Then run the results of that.

  • July 24, 2004 at 7:59 am

    #515977

    You can also create roles and assign individuals to roles.  That way, if you need to change the permissions for a whole group of people, you just change the permissions for the role and every member of that role will be changed.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply