Cleanup your Roles

Time to get rid of oversized plans from the past

I remember when we started planning our Sitecore installation. There were a lot of requests from our stakeholders that alot of different groups of users need access on particular parts of the pages. So as planned to run multiple sites in one instance we created those roles for each site.

At the end it turned out that this was only needed for a small part of our customers. So now it's time to clean up a little bit. 

The below Script gives you all Roles that not used by any user currently.

read before run:

• you need read rights on your DB
• execute this script on your Core DB
• Before deleting roles that appear here check if they are assigned to other roles!!!
• Please also do not delete sitecore roles!!!

/*START*/
BEGIN


	PRINT '***********************************';
	PRINT 'Roles without users';
	PRINT '***********************************';
	declare @ApplicationID nvarchar(128);
	declare @RoleID nvarchar(128);
	declare @RoleName nvarchar(128);
	declare @LoweredRoleName nvarchar(128);
	declare @Description nvarchar(128);

	declare @URUserId nvarchar(128);
	declare @URRoleId nvarchar(128);
	declare @URuserName nvarchar(128);
	declare @URLoweredUserName nvarchar(128);

	declare @rowcount int=0;
	declare @totalcount int=0;

	
	/*Cursor to retrieve all roles*/
	DECLARE ALL_ROLES CURSOR FOR 
		    SELECT [ApplicationId]
				  ,[RoleId]
				  ,[RoleName]
				  ,[LoweredRoleName]
				  ,[Description]
			  FROM aspnet_roles
			  ORDER BY [RoleName];

	
		OPEN ALL_ROLES; 
		FETCH NEXT FROM ALL_ROLES into @ApplicationID, @RoleID, @RoleName, @LoweredRoleName , @Description;
		WHILE @@FETCH_STATUS = 0 
			 BEGIN 

				SET @rowcount = 0;

				
				/*Check if Role has Users*/
				DECLARE ALL_USERS_IN_ROLES CURSOR FOR 
				SELECT ur.UserId
					  ,ur.RoleId
					  ,u.userName
					  ,u.LoweredUserName
				  FROM aspnet_UsersInRoles ur, aspnet_Users u
				  WHERE ur.UserId  = u.UserId
				  AND ur.RoleId = @RoleID;

				  OPEN ALL_USERS_IN_ROLES; 
					FETCH NEXT FROM ALL_USERS_IN_ROLES into @URUserId, @URRoleId, @URuserName, @URLoweredUserName;
					WHILE @@FETCH_STATUS = 0 
						 BEGIN 
							SET @rowcount = @rowcount + 1;
							/*PRINT '    User' + ': ' + @URuserName;*/
							FETCH NEXT FROM ALL_USERS_IN_ROLES into @URUserId, @URRoleId, @URuserName, @URLoweredUserName;

						 END;

					CLOSE ALL_USERS_IN_ROLES; 
					DEALLOCATE ALL_USERS_IN_ROLES;

					if @rowcount = 0
						BEGIN
							PRINT 'Role' + ': ' + @RoleName;
							SET @totalcount = @totalcount + 1;
						END;
				
				FETCH NEXT FROM ALL_ROLES into @ApplicationID, @RoleID, @RoleName, @LoweredRoleName , @Description;

			 END;

		CLOSE ALL_ROLES; 
		DEALLOCATE ALL_ROLES; 

		PRINT 'Count: ' + CAST(@totalcount AS VARCHAR);

END;
/*END*/

Created: 2.2.2017

TSQL Scripts Cleanup Roles