Saturday, 3 November 2012

Find the list of Sitecore Admin Users (through database)

If you want to list down the users who have Sitecore admin access, you have to go through each user (to check IsAdministrator checkbox is checked or not) in User Manager in Security tools. We can also find the list by querying Sitecore ASP.Net membership tables. Mostly these table will be located in Sitecore Core database.

Query:

/**  Getting the list of Users who have Sitecore admin access **/
SELECT [ApplicationId]
      ,[UserId]
      ,[UserName]
      ,[LoweredUserName]
      ,[MobileAlias]
      ,[IsAnonymous]
      ,[LastActivityDate]
  FROM [NewInstance1Sitecore_Core].[dbo].[aspnet_Users]
  WHERE UserId IN
  /** Getting the list of user-ids **/
  (SELECT [UserId]
  FROM [NewInstance1Sitecore_Core].[dbo].[aspnet_Profile]
  WHERE
  /** IsAdministrator Property Name Check **/
  [PropertyNames] LIKE '%IsAdministrator%'
  AND
  /** Property Value : True or False Check **/
  /** !Important: This check should be avoided when you have other boolean type
            custom profile property. And in that case, just get the User name
without this check and manually check in User Manager **/
  (PropertyValuesString LIKE '%True%' AND PropertyValuesString NOT LIKE '%False%'))

This query will return the user details who have admin access.

Note: If you have any custom Boolean type profile property, you may need to avoid using the PropertyValuesString values check in the query. 

3 comments:

  1. That's a useful little script. I modified it slightly to bring back the users email address information.

    /** Getting the list of Users who have Sitecore admin access **/
    SELECT u.[UserId]
    ,[UserName]
    ,m.Email
    FROM [NewInstance1Sitecore_Core].[dbo].[aspnet_Users] u
    inner join [NewInstance1Sitecore_Core].[dbo].aspnet_Membership m
    on (u.UserId = m.UserId)
    WHERE u.UserId IN
    /** Getting the list of user-ids **/
    (SELECT [UserId]
    FROM [NewInstance1Sitecore_Core].[dbo].[aspnet_Profile] p
    WHERE
    /** IsAdministrator Property Name Check **/
    [PropertyNames] LIKE '%IsAdministrator%'
    AND
    /** Property Value : True or False Check **/
    /** !Important: This check should be avoided when you have other boolean type
    custom profile property. And in that case, just get the User name
    without this check and manually check in User Manager **/
    (PropertyValuesString LIKE '%True%' AND PropertyValuesString NOT LIKE '%False%'))
    order by UserName

    ReplyDelete
  2. Thanks, that just came in very handy

    ReplyDelete

Articles

Sitecore 9.3 released!

Sitecore 9.3 has the Solr 8.1.1 support (Sitecore compatibility table is yet to be updated). SIF 2.2.0 Got a new editor - The Horizon edi...