Microsoft Lync Server 2010 Enterprise Pool SQL Database Permissions

As with one of my previous posts that documents the file share permissions of a Microsoft Lync Server 2010 Enterprise Pool (https://blog.terenceluk.com/2011/01/microsoft-lync-server-2010-enterprise.html), I had some time over the weekend to document the SQL database permissions of a Lync Server 2010 Enterprise Pool.  Most administrators will probably never have to check the database permissions during entire life cycle of their Lync Server 2010 but I’m sure there will be a few out there that will find themselves having to verify the security permissions of these databases while troubleshooting services which are dependent on them.  This post serves to list out the security objects created and the permissions they have for each database.

Once you’ve successfully deployed a Lync Server 2010 Enterprise Pool with a SQL backend server, you’ll see the following databases and security logins created:

Databases (excludes Monitoring and Archiving databases)

• cpsdyn

• lis

• rgsconfig

• rgsdyn

• rtc

• rtcab

• rtcab1

• rtcdyn

• xds

Logins

• RTCComponentUniversalServices
• RTCHSUniversalServices
• RTCUniversalConfigReplicator
• RTCUniveresalReadOnlyAdmins
• RTCUniversalServerAdmins

The method I usually use to do a quick audit of databases is actually to execute the stored procedure: sp_helplogins which will allow me to quickly have a look at which account has what permissions.  With that being said, since there may be administrators reading this post who are not familiar with SQL, I will paste the screenshots for the properties of each login before I paste the table:

RTCComponentUniversalServices

–Skipping Securables and Status tabs–

RTCHUniversalServices

–Skipping Securables and Status tabs–

RTCUniversalConfigReplicator

–Skipping Securables and Status tabs–

RTCUniversalReadOnlyAdmins

–Skipping Securables and Status tabs–

RTCUniversalServerAdmins

As I mentioned earlier, the method I usually use to perform a quick audit of databases is actually to execute the stored procedure: sp_helplogins which will allow me to quickly have a look at which account has what permissions.  The following is what the tables look like when you execute the stored procedure:

The table of interest in the 2 above is the second one at the bottom where it will list out all the services and their respective role membership.  The following is the table copy and pasted into a table:

LoginName	DBName	UserName	UserOrAlias
##MS_AgentSigningCertificate##	master	##MS_AgentSigningCertificate##	User
##MS_PolicyEventProcessingLogin##	master	##MS_PolicyEventProcessingLogin##	User
##MS_PolicyEventProcessingLogin##	msdb	##MS_PolicyEventProcessingLogin##	User
##MS_PolicyEventProcessingLogin##	msdb	PolicyAdministratorRole	MemberOf
##MS_PolicyTsqlExecutionLogin##	msdb	##MS_PolicyTsqlExecutionLogin##	User
##MS_PolicyTsqlExecutionLogin##	msdb	PolicyAdministratorRole	MemberOf
SOMEDOMAINRTCComponentUniversalServices	cpsdyn	SOMEDOMAINRTCComponentUniversalServices	User
SOMEDOMAINRTCComponentUniversalServices	cpsdyn	ReadWriteRole	MemberOf
SOMEDOMAINRTCComponentUniversalServices	rgsconfig	SOMEDOMAINRTCComponentUniversalServices	User
SOMEDOMAINRTCComponentUniversalServices	rgsconfig	ReadWriteRole	MemberOf
SOMEDOMAINRTCComponentUniversalServices	rgsdyn	SOMEDOMAINRTCComponentUniversalServices	User
SOMEDOMAINRTCComponentUniversalServices	rgsdyn	ReadWriteRole	MemberOf
SOMEDOMAINRTCComponentUniversalServices	rtcab	SOMEDOMAINRTCComponentUniversalServices	User
SOMEDOMAINRTCComponentUniversalServices	rtcab	ServerRole	MemberOf
SOMEDOMAINRTCComponentUniversalServices	rtcab1	SOMEDOMAINRTCComponentUniversalServices	User
SOMEDOMAINRTCComponentUniversalServices	rtcab1	ServerRole	MemberOf
SOMEDOMAINRTCHSUniversalServices	rtc	SOMEDOMAINRTCHSUniversalServices	User
SOMEDOMAINRTCHSUniversalServices	rtc	ServerRole	MemberOf
SOMEDOMAINRTCHSUniversalServices	rtcdyn	SOMEDOMAINRTCHSUniversalServices	User
SOMEDOMAINRTCHSUniversalServices	rtcdyn	ServerRole	MemberOf
SOMEDOMAINRTCUniversalConfigReplicator	xds	SOMEDOMAINRTCUniversalConfigReplicator	User
SOMEDOMAINRTCUniversalConfigReplicator	xds	ReplicatorRole	MemberOf
SOMEDOMAINRTCUniversalReadOnlyAdmins	cpsdyn	SOMEDOMAINRTCUniversalReadOnlyAdmins	User
SOMEDOMAINRTCUniversalReadOnlyAdmins	cpsdyn	ReadOnlyRole	MemberOf
SOMEDOMAINRTCUniversalReadOnlyAdmins	rgsconfig	SOMEDOMAINRTCUniversalReadOnlyAdmins	User
SOMEDOMAINRTCUniversalReadOnlyAdmins	rgsconfig	ReadOnlyRole	MemberOf
SOMEDOMAINRTCUniversalReadOnlyAdmins	rgsdyn	SOMEDOMAINRTCUniversalReadOnlyAdmins	User
SOMEDOMAINRTCUniversalReadOnlyAdmins	rgsdyn	ReadOnlyRole	MemberOf
SOMEDOMAINRTCUniversalReadOnlyAdmins	rtc	SOMEDOMAINRTCUniversalReadOnlyAdmins	User
SOMEDOMAINRTCUniversalReadOnlyAdmins	rtc	ReadOnlyRole	MemberOf
SOMEDOMAINRTCUniversalReadOnlyAdmins	xds	ConsumerRole	MemberOf
SOMEDOMAINRTCUniversalReadOnlyAdmins	xds	SOMEDOMAINRTCUniversalReadOnlyAdmins	User
SOMEDOMAINRTCUniversalServerAdmins	cpsdyn	SOMEDOMAINRTCUniversalServerAdmins	User
SOMEDOMAINRTCUniversalServerAdmins	cpsdyn	ReadWriteRole	MemberOf
SOMEDOMAINRTCUniversalServerAdmins	lis	AdminRole	MemberOf
SOMEDOMAINRTCUniversalServerAdmins	lis	SOMEDOMAINRTCUniversalServerAdmins	User
SOMEDOMAINRTCUniversalServerAdmins	rgsconfig	SOMEDOMAINRTCUniversalServerAdmins	User
SOMEDOMAINRTCUniversalServerAdmins	rgsconfig	ReadWriteRole	MemberOf
SOMEDOMAINRTCUniversalServerAdmins	rgsdyn	SOMEDOMAINRTCUniversalServerAdmins	User
SOMEDOMAINRTCUniversalServerAdmins	rgsdyn	ReadWriteRole	MemberOf
SOMEDOMAINRTCUniversalServerAdmins	rtc	AdminRole	MemberOf
SOMEDOMAINRTCUniversalServerAdmins	rtc	SOMEDOMAINRTCUniversalServerAdmins	User
SOMEDOMAINRTCUniversalServerAdmins	rtcdyn	AdminRole	MemberOf
SOMEDOMAINRTCUniversalServerAdmins	rtcdyn	SOMEDOMAINRTCUniversalServerAdmins	User
SOMEDOMAINRTCUniversalServerAdmins	xds	SOMEDOMAINRTCUniversalServerAdmins	User
SOMEDOMAINRTCUniversalServerAdmins	xds	PublisherRole	MemberOf
sa	cpsdyn	db_owner	MemberOf
sa	cpsdyn	dbo	User
sa	lis	db_owner	MemberOf
sa	lis	dbo	User
sa	master	db_owner	MemberOf
sa	master	dbo	User
sa	model	db_owner	MemberOf
sa	model	dbo	User
sa	msdb	db_owner	MemberOf
sa	msdb	dbo	User
sa	rgsconfig	db_owner	MemberOf
sa	rgsconfig	dbo	User
sa	rgsdyn	db_owner	MemberOf
sa	rgsdyn	dbo	User
sa	rtc	db_owner	MemberOf
sa	rtc	dbo	User
sa	rtcab	db_owner	MemberOf
sa	rtcab	dbo	User
sa	rtcab1	db_owner	MemberOf
sa	rtcab1	dbo	User
sa	rtcdyn	db_owner	MemberOf
sa	rtcdyn	dbo	User
sa	tempdb	db_owner	MemberOf
sa	tempdb	dbo	User
sa	xds	db_owner	MemberOf
sa	xds	dbo	User

If you’re not familiar with the table and the cells, try reconciling it with the following examples:

 

Notice how I right-clicked on the user RTCUniversalReadOnlyAdmins under the database cpsdyn (sorry the screen got cut off but take my word for it) and you see how only ReadOnlyRole was checked?

Sorry about the cut off for the screenshot but the RTCUniversalServerAdmins account was selected under the database cpsdyn and notice how the role ReadWriteRole was listed in the permissions spreadsheet.  Note that ReadOnlyRole isn’t listed because ReadWriteRole includes that permissions.

Hope this helps anyone that may come across a problem where their Lync Server 2010 front-end pool’s database permissions has been tampered with or modified unintendedly and needs to reference default settings.