Monday, January 31, 2011

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

image

  • lis

image

  • rgsconfig

image

  • rgsdyn

image

  • rtc

image

  • rtcab

image

  • rtcab1

image

  • rtcdyn

image

  • xds

image

Logins

  • RTCComponentUniversalServices
  • RTCHSUniversalServices
  • RTCUniversalConfigReplicator
  • RTCUniveresalReadOnlyAdmins
  • RTCUniversalServerAdmins

image

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

image image

image image

--Skipping Securables and Status tabs--

RTCHUniversalServices

imageimage

image image

--Skipping Securables and Status tabs--

RTCUniversalConfigReplicator

image image

image image

--Skipping Securables and Status tabs--

RTCUniversalReadOnlyAdmins

image image

image image

--Skipping Securables and Status tabs--

RTCUniversalServerAdmins

image image

image image

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:

image

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:

 image image

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?

image image

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.

2 comments:

Anonymous said...

Hi
Great post!
Now how about RTCLOCAL? :D

Terence Luk said...

Hi,

You can find it here: http://terenceluk.blogspot.com/2011/04/microsoft-lync-server-2010-enterprise.html

Terence