Security: Creating a Limited Access Database Account

Article
06/18/2008

Note

This information applies to database configurations that use only SQL Server Authentication. Windows Authentication does not require a limited access database account, because it uses Integrated Security.

When you access a database from a web application, you need to address security issues. In particular, it is important that the account used to access the database is limited in scope to only those functions that are strictly required by the web application.

For example, the Northwind database is used in the Crystal Reports documentation tutorials to display reports that are based on its Customers table and Orders table. Therefore, the limited access database account that is required to connect to the Northwind database would need only two permissions:

Permission to access the Customers and Orders table of the Northwind database.
Within these two tables, permission to SELECT records (but not INSERT, UPDATE, or DELETE).

In this section, you learn how to create limited access database account.

Note

The creation of this limited access database account does not prevent you from accessing this database with the 'sa' system administrator account for full control. Instead, you create a secondary account for use only by your web application.

These instructions are equally applicable to both MSDE and SQL Server.

Type the following command to logon to your MSDE or SQL Server.
- Use the system administrator password where you see this placeholder: [password].
- Use the database server name where you see this placeholder: [serverName].

<!-- end list -->

    osql -U sa -P [password] -S [serverName]

Type USE master to switch to the master database, then on the following line type GO, and then press Enter.

USE master GO
Run the database script "sp_addlogin" and include as command line arguments the name that you want to use for your limited access database account, the password, and the database to which it applies.

Customize the following values in your command line arguments:

  - Create a limited access database account name, such as "limitedPermissionAccount".
  - Create a new password to be used by the limited access account, where you see this placeholder: \[new\_password\]. Write down this password, as you will need it for some of the tutorials.

<!-- end list -->

    sp_addlogin 'limitedPermissionAccount','[new_password]','Northwind'
    GO

Switch to the Northwind database.
```
USE Northwind
GO
```
Run the database script "sp_grantdbaccess" and pass in the name of the new limited access database account that you have created.
```
sp_grantdbaccess 'limitedPermissionAccount'
GO
```
For the Customers table, grant the SELECT privilege for the new limited access database account that you have created for two tables, Customers and Orders.
```
GRANT SELECT ON Customers
TO limitedPermissionAccount
GO
GRANT SELECT ON Orders
TO limitedPermissionAccount
GO
```
Type exit to exit the osql command line, and then press Enter.
```
exit
```
You have successfully created a limited access account and granted it SELECT access only, for the Customers and Orders table only, of the Northwind database.

This completes the setup of your limited access account to connect to the Customers table of the Northwind database. You are now ready to create any of the following tutorials that access the Northwind database with SQL Authentication.

Share via

Security: Creating a Limited Access Database Account

Additional resources