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 (http://terenceluk.blogspot.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
SOMEDOMAIN\RTCComponentUniversalServices	cpsdyn	SOMEDOMAIN\RTCComponentUniversalServices	User
SOMEDOMAIN\RTCComponentUniversalServices	cpsdyn	ReadWriteRole	MemberOf
SOMEDOMAIN\RTCComponentUniversalServices	rgsconfig	SOMEDOMAIN\RTCComponentUniversalServices	User
SOMEDOMAIN\RTCComponentUniversalServices	rgsconfig	ReadWriteRole	MemberOf
SOMEDOMAIN\RTCComponentUniversalServices	rgsdyn	SOMEDOMAIN\RTCComponentUniversalServices	User
SOMEDOMAIN\RTCComponentUniversalServices	rgsdyn	ReadWriteRole	MemberOf
SOMEDOMAIN\RTCComponentUniversalServices	rtcab	SOMEDOMAIN\RTCComponentUniversalServices	User
SOMEDOMAIN\RTCComponentUniversalServices	rtcab	ServerRole	MemberOf
SOMEDOMAIN\RTCComponentUniversalServices	rtcab1	SOMEDOMAIN\RTCComponentUniversalServices	User
SOMEDOMAIN\RTCComponentUniversalServices	rtcab1	ServerRole	MemberOf
SOMEDOMAIN\RTCHSUniversalServices	rtc	SOMEDOMAIN\RTCHSUniversalServices	User
SOMEDOMAIN\RTCHSUniversalServices	rtc	ServerRole	MemberOf
SOMEDOMAIN\RTCHSUniversalServices	rtcdyn	SOMEDOMAIN\RTCHSUniversalServices	User
SOMEDOMAIN\RTCHSUniversalServices	rtcdyn	ServerRole	MemberOf
SOMEDOMAIN\RTCUniversalConfigReplicator	xds	SOMEDOMAIN\RTCUniversalConfigReplicator	User
SOMEDOMAIN\RTCUniversalConfigReplicator	xds	ReplicatorRole	MemberOf
SOMEDOMAIN\RTCUniversalReadOnlyAdmins	cpsdyn	SOMEDOMAIN\RTCUniversalReadOnlyAdmins	User
SOMEDOMAIN\RTCUniversalReadOnlyAdmins	cpsdyn	ReadOnlyRole	MemberOf
SOMEDOMAIN\RTCUniversalReadOnlyAdmins	rgsconfig	SOMEDOMAIN\RTCUniversalReadOnlyAdmins	User
SOMEDOMAIN\RTCUniversalReadOnlyAdmins	rgsconfig	ReadOnlyRole	MemberOf
SOMEDOMAIN\RTCUniversalReadOnlyAdmins	rgsdyn	SOMEDOMAIN\RTCUniversalReadOnlyAdmins	User
SOMEDOMAIN\RTCUniversalReadOnlyAdmins	rgsdyn	ReadOnlyRole	MemberOf
SOMEDOMAIN\RTCUniversalReadOnlyAdmins	rtc	SOMEDOMAIN\RTCUniversalReadOnlyAdmins	User
SOMEDOMAIN\RTCUniversalReadOnlyAdmins	rtc	ReadOnlyRole	MemberOf
SOMEDOMAIN\RTCUniversalReadOnlyAdmins	xds	ConsumerRole	MemberOf
SOMEDOMAIN\RTCUniversalReadOnlyAdmins	xds	SOMEDOMAIN\RTCUniversalReadOnlyAdmins	User
SOMEDOMAIN\RTCUniversalServerAdmins	cpsdyn	SOMEDOMAIN\RTCUniversalServerAdmins	User
SOMEDOMAIN\RTCUniversalServerAdmins	cpsdyn	ReadWriteRole	MemberOf
SOMEDOMAIN\RTCUniversalServerAdmins	lis	AdminRole	MemberOf
SOMEDOMAIN\RTCUniversalServerAdmins	lis	SOMEDOMAIN\RTCUniversalServerAdmins	User
SOMEDOMAIN\RTCUniversalServerAdmins	rgsconfig	SOMEDOMAIN\RTCUniversalServerAdmins	User
SOMEDOMAIN\RTCUniversalServerAdmins	rgsconfig	ReadWriteRole	MemberOf
SOMEDOMAIN\RTCUniversalServerAdmins	rgsdyn	SOMEDOMAIN\RTCUniversalServerAdmins	User
SOMEDOMAIN\RTCUniversalServerAdmins	rgsdyn	ReadWriteRole	MemberOf
SOMEDOMAIN\RTCUniversalServerAdmins	rtc	AdminRole	MemberOf
SOMEDOMAIN\RTCUniversalServerAdmins	rtc	SOMEDOMAIN\RTCUniversalServerAdmins	User
SOMEDOMAIN\RTCUniversalServerAdmins	rtcdyn	AdminRole	MemberOf
SOMEDOMAIN\RTCUniversalServerAdmins	rtcdyn	SOMEDOMAIN\RTCUniversalServerAdmins	User
SOMEDOMAIN\RTCUniversalServerAdmins	xds	SOMEDOMAIN\RTCUniversalServerAdmins	User
SOMEDOMAIN\RTCUniversalServerAdmins	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.