SQL Server access via Windows Authentication

SQL Server access via Windows Authentication

Script to create an Administrator Windows account

The following script will create a new Windows authenticated login with the minimum database permissions required to use all features of HelpMaster. This style of login should be used for an administrator of the system. Copy everything below and paste it into a new query window of Microsoft SQL Server Management Studio.

NOTE: Currently Microsoft restricts the use of user groups when granting elevated SQL permissions. This means that each Administrator will need to be individually added rather than using a domain user group as you can with general users.

-- Server level execution
USE [master] 
GO

-- Add an AD user account to SQL server logins.  Update the name of your HelpMaster database in the line below
CREATE LOGIN [DOMAIN\UserName_or_Group] FROM WINDOWS WITH DEFAULT_DATABASE=[HelpMaster Database Name Goes Here], DEFAULT_LANGUAGE=[us_english]
GO

-- Now associate this new SQL Server login with a user in the HelpMaster database
-- Also associate this database user with the "dbo" schema. The "dbo" schema contains all of the HelpMaster database objects (tables, views, stored procedures etc)
-- Insert the name of your HelpMaster database in the line below
USE [HelpMaster Database Name Goes Here] -- Database level execution

CREATE USER [DOMAIN\UserName_or_Group] FOR LOGIN [DOMAIN\UserName_or_Group] WITH DEFAULT_SCHEMA=[dbo]
GO

-- Grant the build-in SQL Server role "db_datareader" allows logins to read data
EXEC sp_addrolemember N'db_datareader', N'DOMAIN\UserName_or_Group'
GO 

-- Grant the build-in SQL Server role "db_datawriter" allows logins to write data
EXEC sp_addrolemember N'db_datawriter', N'DOMAIN\UserName_or_Group'
GO

-- Grant the build-in SQL Server role "db_owner" allows logins to administer the database
EXEC sp_addrolemember N'db_owner',N'DOMAIN\UserName_or_Group'
GO

-- Grant the ability to "execute" the stored procedures contained within the "dbo" schema
GRANT EXECUTE ON SCHEMA::[dbo] TO [DOMAIN\UserName_or_Group]
GO

-- Grant the ability to "alter" the "dbo" schema which contains the views that need to be re-created
GRANT ALTER ON SCHEMA::[dbo] TO [DOMAIN\UserName_or_Group]
GO 

GRANT CREATE TABLE TO [DOMAIN\UserName_or_Group] -- Used to create tables during database upgrades
GO

GRANT CREATE PROCEDURE TO [DOMAIN\UserName_or_Group] -- Used to create stored procedures during database upgrades
GO

GRANT CREATE VIEW TO [DOMAIN\UserName_or_Group] -- Used to create views during database upgrades as well as updating system codes
GO 

GRANT VIEW DATABASE STATE TO [DOMAIN\UserName_or_Group]
GO 

Use [Master] -- Required for the settings below

GRANT ALTER SETTINGS TO [DOMAIN\UserName_or_Group] -- Required for a database reconfigure statement used for Full Text indexing operations
GO

GRANT VIEW SERVER STATE TO [DOMAIN\UserName_or_Group] -- Only required if using SQL Server High Availability
GO

GRANT VIEW ANY DEFINITION TO [DOMAIN\UserName_or_Group] -- Only required if using SQL Server High Availability

Script to create a non-administrator Windows account

The following script will create a new Windows authenticated login with the minimum database permissions required to use all features of HelpMaster, except the administrative features of creating new custom fields and other system codes. This style of login should be used for all non-administrative users of HelpMaster.

USE [master] -- Server level execution
GO 

-- Add an AD user account to SQL server logins
CREATE LOGIN [DOMAIN\UserName_or_Group] FROM WINDOWS WITH DEFAULT_DATABASE=[HelpMaster Database Name Goes Here], DEFAULT_LANGUAGE=[us_english]
GO

-- Now associate this new SQL Server login with a user in the HelpMaster database
-- Also associate this database user with the "dbo" schema. The "dbo" schema contains all of the HelpMaster database objects (tables, views, stored procedures etc)
-- Insert the name of your HelpMaster database in the line below
USE [HelpMaster Database Name Goes Here] -- Database level execution

CREATE USER [DOMAIN\UserName_or_Group] FOR LOGIN [DOMAIN\UserName_or_Group] WITH DEFAULT_SCHEMA=[dbo]
GO

-- Grant the build-in SQL Server role "db_datareader" allows logins to read data
EXEC sp_addrolemember N'db_datareader', N'DOMAIN\UserName_or_Group'
GO

-- Grant the build-in SQL Server role "db_datawriter" allows logins to write data
EXEC sp_addrolemember N'db_datawriter', N'DOMAIN\UserName_or_Group'
GO

-- Grant the ability to "execute" the stored procedures contained within the "dbo" schema
GRANT EXECUTE ON SCHEMA::[dbo] TO [DOMAIN\UserName_or_Group]
GO

Use [Master] -- Required for the settings below

GRANT VIEW SERVER STATE TO [DOMAIN\UserName_or_Group] -- Only required if using SQL Server High Availability
GO

GRANT VIEW ANY DEFINITION TO [DOMAIN\UserName_or_Group] -- Only required if using SQL Server High Availability