Post reply

permissions for connect any database

  • February 24, 2015 at 12:37 pm

    #294812

    We have a monitor tool to monitor our SQL servers.

    The vendor has a script to grant permission to application account to SQL server so that they can do the monitoring.

    I don't want to grant system admin role, but the script they have is very specific, the minimum is:

    must be a member of db_datareader role on the msdb database

    must have view server state permissions

    view any definition

    connection permission to master database

    execute permission on the xp_readerrorlog stored procedure

    connect permission to the msdb database

    must be member of db_Datareader role in the msdb datab ase

    connect permission to all databases.

    The script given by vendor is as follows in the attachment.

    I don't like the last execute statement for if a new database is created, we have to remember manually add the monitoring acccount to that database.

    I know in SQL 2014 it has a new feature of connect to any database, but unfortunately we are using SQL 2012 and 2008.

    Any suggestions for the script to connect to any database but no need to add that each time we creates a new database.

    Thanks,

  • February 24, 2015 at 2:06 pm

    #1779747

    You could add the permissions to the model database so each time a new db is created the permissions are there.

  • February 24, 2015 at 3:11 pm

    #1779766

    i believe when you grant VIEW ANY DEFINITION TO [SpecialUser], that inherently grants connect permissions to each database; you do not need to add anything additional for that permission.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 27, 2015 at 4:17 pm

    #1780828

    Thanks, It is good to know view any databases permission or view any definition implies that the account has a connect permission to the database.

    But I cannot find anywhere online, can anyone point me to the right documentation about this?

    Thanks,

  • February 28, 2015 at 9:24 am

    #1780885

    JeremyE (2/24/2015)

    You could add the permissions to the model database so each time a new db is created the permissions are there.

    I'd strongly suggest against such auto-granting of privs.

    --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)

  • February 28, 2015 at 9:27 am

    #1780886

    ANn -425914 (2/27/2015)

    Thanks, It is good to know view any databases permission or view any definition implies that the account has a connect permission to the database.

    But I cannot find anywhere online, can anyone point me to the right documentation about this?

    Thanks,

    In "Books Online" (the help system for SQL Server), lookup GRANT (SQL Server) and follow your nose.

    Close to the same in Google. Search for "grant sql server permissions"

    --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