SiteAudit requires a
SQL database for its function. Once a SQL database is created, an authenticated
user with the appropriate rights must log onto the SQL server and install the
SiteAudit database schema. This article will focus on Windows authenticated
users - those who have domain credentials. The goal is to show how these users
can be granted access to the SQL server and given rights to perform certain
functions on a SiteAudit database.
The process to
prepare a database for use with SiteAudit is as follows
- Create a database
- Create a login for the Windows account. This can be the user of a Windows machine or a user having a domain account.
- Assign a user to the database. The user typically has the same credentials as created in 2.
- Assign rights to the user in 3.
- The user from 3. logs into SiteAudit and installs the database schema
Steps 1 and 2 are
performed by the SQL server system administrator. In corporate environments,
this is typically a DBA. In many cases, SQL Express or SQL Server is installed
solely for use with SiteAudit. In this case, the user who installs SQL is the
system administrator and can perform all functions on the database.
This document
describes how to create Windows authenticated users and assign roles to these
users.
Creating
Windows Authenticated Users & Assigning Roles
Creating Windows
authenticated users and assigning them a role to a SiteAudit database must be
performed using a tool such as Microsoft SQL Server Management Studio. SQL
authenticated users can be created and assigned a role within a database using
this tool as well. However, this article focuses on Windows authenticated
users.
Who
Can Create Windows Authenticated Users?
In order to create
login accounts and users within SQL Server Management Studio, one must be a
member of the system administrator, sysadmin,
role. The system administrator is the only role who can create logins for
Windows users. However, once the Windows logins have been created, users who
are members of the Database Administrator role can assign and revoke privileges
to a SiteAudit database for other users.
Often in enterprise
environments, one must request a database from the DBA. The DBA is a system
administrator of the SQL server and will create the database, assign a user to
the database, and give the user specific rights to log onto the SQL server and access
the database.
Creating
Windows Authenticated Users & Assigning Roles
This section
describes the basic tasks required to create a SQL login for a Windows user and
assign this user a role to the SiteAudit database.
- Create a Login for the Windows authenticated user
- Create a database for SiteAudit
- Assign the user from 1 to the SiteAudit database and give the user db_owner access
- Install the SiteAudit schema
- Open the SiteAudit Database
- Logon using the credentials from 1
- Select Maintenance > Install to install the SiteAudit schema
- Assign the SiteAudit Database Administrator role to the Windows user from step 1
Creating
User Logins
Before granting a
user access to a database, there must first exist a login for the user. There
are two types of logins: SQL authentication and Windows authentication.
Using
the SiteAudit Prerequisites with SQL
The SQL Express prerequisites packaged provided on the
Netaphor website installs using Windows authentication mode only. The user
credentials used to install SQL Express are assigned as the database
administrator. This allows the user logged into the Windows machine (presumably
the same user who had installed SQL Express) to access the SQL server without
requiring credentials. The credentials used to log onto Windows are passed to
SQL and the user is authenticated automatically. If a user other than the one
who installed SQL Express logs onto the machine, he/she cannot access SQL using
integrated security until this access is granted by the SQL administrator.
When SiteAudit is
first started, the server points to the instance of SQL Express on the local
machine and selects Use integrated security.
This allows the user who is logged into the Windows machine (the same user who
installed

SQL authentication
is not enabled and SQL users cannot be created unless this is enabled. The
Configuring SQL Express for Mixed Mode Authentication article describes how to enable mixed mode authentication, which
allows creation of both SQL and Windows authenticated users.
Adding
a Windows Authenticated User Login
Ensure there is a
login for the windows user who will be administering SiteAudit
- Open SQL Server Management Studio
- Expand the Security folder
- Right-click the Logins folder and select

- Select Windows authentication, enter the Windows user in the Login name text box and click the OK button.

- Right-click the Logins folder and click Refresh to refresh the list of user logins
- Expand the Logins folder and confirm that the user login has been created successfully
Creating
the SiteAudit Database
Creating a database
is a common task for the DBA.
- Right-click on the Databases folder and select New Database

- Enter the database name and set the Recovery model to Simple
- Click OK to create the database
- Right-click the Databases folder and select Refresh
Assigning
the SiteAudit Database Administrator Role to a Windows Authenticated User
- Expand the Databases folder
- Right-click on the SiteAudit database and select New Query
- Run the script below to assign the SiteAuditRoleDatabaseAdministrator role to the database administrator for SiteAudit
EXECUTE [dbo].[nsyssp_joinrole] 'domain\username', 'SiteAuditRoleDatabaseAdministrator'
The script can be used to assign role permissions to a SQL authenticated user as well.
EXECUTE [dbo].[nsyssp_joinrole] 'bob', 'SiteAuditRoleDatabaseAdministrator'
Any one of the following four roles can be assigned to a user
- Database Administrator - 'SiteAuditRoleDatabaseAdministrator'
- Tenant Administrator - 'SiteAuditRoleTenantAdministrator'
- Manager - 'SiteAuditRoleManager'
- Reviewer - 'SiteAuditRoleReviewer'
To verify the user
has been assigned to the SiteAuditRoleDatabaseAdministrator,
- Right-click on the SiteAudit database and select Refresh from the menu options
- Expand the SiteAudit database folder
- Expand the Security folder
- Expand the Users folder - the user should appear in the list
- Right-click the user name and select Properties from the menu options
- Select the Membership page
- Verify that SiteAuditRoleDatabaseAdministrator role is selected

Revoking
Role Privileges for Windows Authenticated Users
It is possible to
revoke SiteAudit role permissions for a user by executing the following script
for the desired user
EXECUTE [dbo].[nsyssp_unjoinroles] 'domain\username'
Note that SQL
authenticated users can have role permissions revoked using the same query. For
example, to revoke access privileges for the SQL user bob,
EXECUTE [dbo].[nsyssp_unjoinroles] 'bob'
Changing Role Privileges for Windows Authenticated Users
In order to change the role for Windows Authenticated users, first revoke the existing role privileges and then assign the desired role.