Post reply

Grant update previllage on all databases

  • May 11, 2009 at 12:47 pm

    #384563

    I want to give "Grant UPDATE" privilege to a user on all user databases. Is there anyway to automatic this process. It will take long time for me to give for each table in each database.

    Thanks.

  • May 11, 2009 at 1:09 pm

    #992400

    You could use the sp_msforeachtable stored procedure. The following code will grant update to public for all tables. ? is just a place holder for the table name.

    sp_msforeachtable 'GRANT UPDATE ON ? TO [public]'

  • May 11, 2009 at 2:25 pm

    #992447

    For each database, you could do the following:

    GRANT UPDATE ON SCHEMA::dbo TO {user/role};

    Assuming the schema you want to grant update privileges to is 'dbo'. If not, change the schema and the user/role will have update privileges to all objects in that schema.

    To perform this in all databases, you can use 'msforeachdatabase' - or, build it dynamically using something like:

    SELECT 'USE ' + quotename([Name]) + '; GRANT UPDATE ON SCHEMA::dbo TO MyUserOrRole'

    FROM sys.databases

    WHERE database_id > 4;

    Modify the above filter to include only those databases you want. Take the results from the above and copy/paste into a new window and execute.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

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