SQL Server access via SQL Server account authentication

SQL Server access via SQL Server account authentication

Script to create an Administrator SQL account

The following script will create a new SQL Server 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. For regular users of the system, use the non-administrator permissions (Section below)

Copy everything below and paste it into a new query window of Microsoft SQL Server Management Studio.

-- Server level execution
USE [master] 
GO 
-- Create a new SQL Server login.  Update the name of your HelpMaster database in the line below
CREATE LOGIN [HelpMaster Administrator] WITH PASSWORD=N'Password goes here', DEFAULT_DATABASE=[HelpMaster Database Name Goes Here] , DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
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 [HelpMaster Administrator] FOR LOGIN [HelpMaster Administrator] 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'HelpMaster Administrator'
GO

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

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

-- Grant the ability to "execute" the stored procedures contained within the "dbo" schema
GRANT EXECUTE ON SCHEMA::[dbo] TO [HelpMaster Administrator]
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 [HelpMaster Administrator]
GO

GRANT CREATE TABLE TO [HelpMaster Administrator] -- Used to create tables during database upgrades
GO

GRANT CREATE PROCEDURE TO [HelpMaster Administrator] -- Used to create stored procedures during database upgrades
GO

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

GRANT VIEW DATABASE STATE TO [HelpMaster Administrator]
GO

Use [Master] -- Required for the settings below

GRANT ALTER SETTINGS TO [HelpMaster Administrator] -- Required for a database reconfigure statement used for Full Text indexing operations
GO

GRANT VIEW SERVER STATE TO [HelpMaster Administrator] -- Only required if using SQL Server High Availability
GO

GRANT VIEW ANY DEFINITION TO [HelpMaster Administrator] -- Only required if using SQL Server High Availability

Script to create a non-administrator SQL account

The following script will create a new SQL Server 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.

Copy everything below and paste it into a new query window of Microsoft SQL Server Management Studio.

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 [HelpMaster Administrator] -- Only required if using SQL Server High Availability
GO

GRANT VIEW ANY DEFINITION TO [HelpMaster Administrator] -- Only required if using SQL Server High Availability