Creating a read-only database user account for Microsoft SQL Server 2000 Desktop Engine (MSDE)

Products

• Security Information Manager

Issue/Introduction

 

Resolution

Creating a read-only database user account for Microsoft SQL Server 2000 Desktop Engine (MSDE)

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 account for Microsoft SQL Server 2000 Desktop Engine (MSDE)

1. From the Start menu, select Programs > Accessories > Command Prompt.

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

   The default directory location for this file is C:\Program Files\Microsoft SQL Server\80\Tools\Binn.

3. To log in as the system administrator user, type the following command:

   osql -U sa

4. At the Password prompt, type the system administrator password.

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

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

   USE database_name

   EXEC sp_grantdbacces 'account_name'

   EXEC sp_addrolemember 'db_datareader', 'account_name'

   go

6. At the prompt, type the following command:

   quit

   The following is an example list of the commands that must be executed. The confirmation message 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