Creating a read-only database user account for Microsoft SQL Server

book

Article ID: 179869

calendar_today

Updated On:

Products

Security Information Manager

Issue/Introduction

 

Resolution

Creating a read-only database user account for Microsoft SQL Server

Collectors that use a database sensor require that you create a read-only database user account so that the collector can query for events.

See Installing collectors that use a database sensor.

To create a read-only database user for Microsoft SQL Server

  1. From the Windows Start menu, choose Run, and then type the following command:

    cmd

  2. Navigate to the directory that contains the OSQL.EXE file.

    For Microsoft SQL Server 2008, the default directory location for this file is C:\Program Files\Microsoft SQL Server\100\Tools\Binn

    For Microsoft SQL Server 2005, the default directory location for this file is C:\Program Files\Microsoft SQL Server\90\Tools\Binn.

    For Microsoft SQL Server 2000, the default directory location for this file is C:\Program Files\Microsoft SQL Server\80\Tools\Binn.

  3. Log in as the system administrator user. Type the following command, and then at the Password prompt, type the system administrator password:

    osql -U sa

  4. At the command prompt, type the following commands:

    For Microsoft SQL Server 2000 or Microsoft SQL Server 2000 Desktop Engine (MSDE):

    EXEC sp_addlogin 'account_name', 'password', database_name'

    USE database_name

    EXEC sp_grantdbaccess 'account_name'

    EXEC sp_addrolemember 'db_datareader', 'account_name'

    go

    For Microsoft SQL Server 2005 and Microsoft SQL Server 2008:

    EXEC sp_addlogin 'account_name', 'password', 'database_name'

    USE database_name

    CREATE USER 'account_name' FOR LOGIN 'account_name'

    EXEC sp_addrolemember 'db_datareader', 'account_name'

    go

    quit

    The following is an example list of the commands that must be executed. The confirmation messages shows that a new logon was created, granted access to the database, and assigned to the db_datareader role:

    D:\>osql -U sa Password:
    
    1> EXEC sp_addlogin 'readonly', 'x$256wr', 'BVInternetSecuritySQL'
    
    2> USE BVInternetSecuritySQL
    
    3> EXEC sp_grantdbaccess 'readonly'
    
    4> EXEC sp_addrolemember 'db_datareader', 'readonly'
    
    5> go
    
    New login created.
    
    Granted database access to 'readonly'.
    
    'readonly' added to role 'db_datareader'.
    
    1> quit