SQL Server Express tutorial: Add a built-in user to a database

Posted on August 21, 2010 by ellen

In IIS, several built-in Windows accounts are created automatically during installation. These include IUSR_MachineName, IWAM_MachineName, and ASPNET, each of which has specific privileges. Web applications will often require that the IUSR account have rights to connect to SQL Server Express.

Here’s how to give this account permissions in the SQL Server database, when the database is located on the same box as the application.

Open SQL Server Management Studio.
Right click the folder “Users” in the path
Databases > Database Name > Security
Select New User. . . from the popup menu.
The Database User window will open. Select Login name, and click the button to the right of the Login name text box.
The Select Login window will open, defaulted to the “Logins” object type. Click the Browse button to choose from existing local accounts.
Select [MachineNameIUSR_MachineName] from the list of available accounts and click OK.
The selected name will show up in the object names window. Click OK.
You will be returned to the Database User window. The selected name will show up in the Login Name window. Copy and past the same name into the User Name box just above it.
Assign whichever schemas and roles are needed by the application. The one shown here, db_owner, is just for example and is too high for most applications. A more typical choice might be db_data reader and db_datawriter.