Assigning Roles for Windows Authenticated Users

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

1. Create a database
2. Create a login for the Windows account. This can be the user of a Windows machine or a user having a domain account.
3. Assign a user to the database. The user typically has the same credentials as created in 2.
4. Assign rights to the user in 3.
5. 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.

 

1. Create a Login for the Windows authenticated user
2. Create a database for SiteAudit
3. Assign the user from 1 to the SiteAudit database and give the user db_owner access
4. Install the SiteAudit schema
• Open the SiteAudit Database
• Logon using the credentials from 1
• Select Maintenance > Install to install the SiteAudit schema
5. 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.