Install datastore module without sysadmin role

For some customers or a specific IT-infrastructure it could be necessary to work only with the security rights that are really needed. For productive work of the Voxtron Communication Center the datastore module need less security server roles as for the installation required. With datastore < 1.2.8 you need the sysadmin role to install the module.
With Datastore 1.2.8 it’s possible to install the DS module without the sysadmin role but you/your DBA have to handle some settings manually first and within the installation.
Remark: Don’t let your DBA go to lunch if you install the DS module 😉
The installation script of the DS do some changes automatically if the user has the sysadmin role, then this document is obsolete for you.

It’s also possible to install the datastore with sysadmin role and after that you change the roles to db_owner and public. You don’t need the sysadmin role for an upgrade of any new datastore module.

If you can’t get the sysadmin role from your DBA then you have to read further.

Minimum requirements

To install the Voxtron Communication Center datastore module we need some rights.
First of all we need a user. This could be a SQL user (mixed mode) or a windows user.
If you are using a windows user I hope you are knowing what you are doing. Otherwise check this blog entry: SQL Server configuration with Windows Authentication
The user need the role “dbcreator” and “public”.
That’s are the minimum required rights but in generally that should not be a problem.

login_properties

Check these tasks/settings first if you want a smooth installation otherwise you get an install error with a hint to solve the issue like this:

install_hint

CLR functionality

The Voxtron Communication Center is using a .NET assembly for the VoicemailBox password encryption. That assembly is part of the database and so you need the CLR functionality to install that component and for using this.

clr_functionality

Enable the CLR functionality – you need a user with sysadmin or serveradmin role to run this script.
https://msdn.microsoft.com/de-de/library/ms131048(v=sql.120).aspx

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

LinkedServer

The Voxtron10_Warehouse and Voxtron10_File database are using a LinkedServer to the Voxtron10 Database. This is necessary to support the distribution of the databases to dedicated server.

For adding a LinkedServer you need the setupadmin or sysadmin right. If you don’t have this right and that’s why you are here you have to add the LinkedServer manually.

You can do this per Wizard or with a T-SQL script. We are using a SQL-Mixed-User setup and adding a new user named “voxtron” but you can use your already existing user for installation or another. You or your DBA could add a linkedserver with windows authentication as well.

The LinkedServer could be create before the installation of the DS.

Remark: If you use different servers for the databases and don’t use the same db user with the same credentials on all servers you should add an own user for the LinkedServer and add them to each database and server. But then the Voxtron10 database needs the login credential for the linked server and you have do add this user to the Voxtron10 database (at this point the Voxtron10 database does not exists).
To prevent any wrong settings you should not create the Voxtron10 database manually! Install the DS module with the Installation Center. The Installation will fail for the Voxtron10_File database but then you have the Voxtron10 database with the right settings and can add the user to the database with “sp_addrolemember” (example below).

Upgrade the DS with the last installed version again to create the Voxtron10_Files and Voxtron10_Warehouse database.

Create the LinkedServer

The LinkedServer must be created on the SQL Server where the Voxtron10_Warehouse and Voxtron10_Files database are installed.

The LinkedServer must be named SRV_VOXTRON10 and the catalog Voxtron10. You can use the credentials of your db user instead of rtmuser=’voxtron’. If you use another user you have to add this user to all databases and SQL server.

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
@server = N'SRV_VOXTRON10', -- Logical name
@srvproduct=N'',
@provider=N'SQLNCLI',
@catalog=N'Voxtron10', -- Initial catalog
@datasrc='(local)' -- SQL Server instance name with Voxtron10 DB
-- Define security aspects
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'SRV_VOXTRON10',
@useself=N'False',
@locallogin=NULL,
@rmtuser='{YOURVOXTRONUSER}',
@rmtpassword='{YOURPASSWORD}'

Optional: Adding the user to the databases (after DS Installation)

You must add the user you have used for the LinkedServer to the DBMS and to the Voxtron10, Voxtron10_Warehouse and Voxtron10_Files database. Not if the LinkedServer user is the same as configured at Installation Center.

Change the password {YOURPASSWORD} and username with your own.
These scripts are samples for adding a new user “voxtron” because we added a LinkedServer with the username “voxtron”. If you have used the username for installation (db_owner of the database) you don’t need any other user for the database and can skip this part.

Voxtron10

USE [Voxtron10]
GO
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'voxtron')
BEGIN
-- Create voxtron login
CREATE LOGIN [voxtron] WITH PASSWORD=N'{YOURPASSWORD}', DEFAULT_DATABASE=[Voxtron10], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
END
ELSE
BEGIN
-- Change the default database for the existing login
ALTER LOGIN [voxtron] WITH DEFAULT_DATABASE=[Voxtron10]
END
-- Add user voxtron to the current database, using the login voxtron
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'voxtron')
BEGIN
CREATE USER voxtron FOR LOGIN voxtron
END
-- Add this user to the db_owner role of the current database
EXEC sp_addrolemember 'db_owner', 'voxtron'

Voxtron10_Warehouse

USE [Voxtron10_Warehouse]
GO
-- Check whether the login voxtron already exists or not
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'voxtron')
BEGIN
-- Create voxtron login
CREATE LOGIN [voxtron] WITH PASSWORD=N'{YOURPASSWORD}', DEFAULT_DATABASE=[Voxtron10_Warehouse], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
END
-- Add user voxtron to the current database, using the login voxtron
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'voxtron')
BEGIN
CREATE USER voxtron FOR LOGIN voxtron
END
-- Add this user to the db_owner role of the current database
EXEC sp_addrolemember 'db_owner', 'voxtron'

Voxtron10_Files

USE [Voxtron10_Files]
GO
-- Check whether the login voxtron already exists or not
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'voxtron')
BEGIN
-- Create voxtron login
CREATE LOGIN [voxtron] WITH PASSWORD=N'V@xtr@n233_', DEFAULT_DATABASE=[Voxtron10_Files], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
END
-- Add user voxtron to the current database, using the login voxtron
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'voxtron')
BEGIN
CREATE USER voxtron FOR LOGIN voxtron
END
-- Add this user to the db_owner role of the current database
EXEC sp_addrolemember 'db_owner', 'voxtron'

Checklist
Preparing (before DS install) Between (DS install)
 [ ] Adding SQL User with dbcreator and public role
 [ ] Enabled CLR integration
 [ ] Added LinkedServer to all SQL Server with Voxtron10_Warehouse and Voxtron10_Files
 [ ] Optional: Add LinkedServer user to Voxtron10, Voxtron10_Warehouse & Voxtron10_Files database if different.

Leave a Reply