SQL Server – Should MS_DataCollectorInternalUser Be Dropped in msdb?

Securitysql server

From SQL Server 2008, there is a user in msdb called MS_DataCollectorInternalUser. This user has no login associated with this and can be considered as a orphan user.

It's a security best practice to drop orphan users.

The MS_DataCollectorInternalUser cannot just be dropped using DROP USER TSQL command because it has granted a database permission IMPERSONATEto a user called dc_admin.

We don't use the Data Collector feature of SQL Server.

What do you suggest ?

Best Answer

I cannot find reference from Microsoft Website but according to the Blog post from Alfred Songy. MS_DataCollectorInternalUser has to do with the Data Collector It is a user account intentially created with no SQL Login and A user created without login obviously cannot connect to the database and therefore I don't see a security risk. My suggestion is that it should not be removed deleting,modifying anything within system database is not a good idea. :)

This query below helps to identify Orphaned SQL Server Users.

select DB_NAME() [database], name as [user_name], type_desc,default_schema_name,create_date,modify_date from sys.database_principals 
where type in ('G','S','U') 
and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"
and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') ) 
and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')

After we know which users we will drop, the below script can be used to drop the orphaned users taking in account the need to first remove the association to schemas and database roles.

use [master]
go
create proc dbo.sp_Drop_OrphanedUsers
as
begin
 set nocount on
 -- get orphaned users  
 declare @user varchar(max) 
 declare c_orphaned_user cursor for 
  select name
  from sys.database_principals 
  where type in ('G','S','U') 
  and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"
  and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') ) 
  and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')  open c_orphaned_user 
 fetch next from c_orphaned_user into @user
 while(@@FETCH_STATUS=0)
 begin
  -- alter schemas for user 
  declare @schema_name varchar(max) 
  declare c_schema cursor for 
   select name from  sys.schemas where USER_NAME(principal_id)=@user
  open c_schema 
  fetch next from c_schema into @schema_name
  while (@@FETCH_STATUS=0)
  begin
   declare @sql_schema varchar(max)
   select @sql_schema='ALTER AUTHORIZATION ON SCHEMA::['+@schema_name+ '] TO [dbo]'
   print @sql_schema
   exec(@sql_schema)
   fetch next from c_schema into @schema_name
  end
  close c_schema
  deallocate c_schema   

  -- alter roles for user 
  declare @dp_name varchar(max) 
  declare c_database_principal cursor for 
   select name from sys.database_principals
   where type='R' and user_name(owning_principal_id)=@user
  open c_database_principal
  fetch next from c_database_principal into @dp_name
  while (@@FETCH_STATUS=0)
  begin
   declare @sql_database_principal  varchar(max)
   select @sql_database_principal ='ALTER AUTHORIZATION ON ROLE::['+@dp_name+ '] TO [dbo]'
   print @sql_database_principal 
   exec(@sql_database_principal )
   fetch next from c_database_principal into @dp_name
  end
  close c_database_principal
  deallocate c_database_principal

  -- drop roles for user 
  declare @role_name varchar(max) 
  declare c_role cursor for 
   select dp.name--,USER_NAME(member_principal_id)
   from sys.database_role_members drm
   inner join sys.database_principals dp 
   on dp.principal_id= drm.role_principal_id
   where USER_NAME(member_principal_id)=@user 
  open c_role 
  fetch next from c_role into @role_name
  while (@@FETCH_STATUS=0)
  begin
   declare @sql_role varchar(max)
   select @sql_role='EXEC sp_droprolemember N'''+@role_name+''', N'''+@user+''''
   print @sql_role
   exec (@sql_role)
   fetch next from c_role into @role_name
  end
  close c_role
  deallocate c_role   

  -- drop user
  declare @sql_user varchar(max)
  set @sql_user='DROP USER ['+@user +']'
  print @sql_user
  exec (@sql_user)
  fetch next from c_orphaned_user into @user
 end
 close c_orphaned_user
 deallocate c_orphaned_user
 set nocount off
end
go
-- mark stored procedure as a system stored procedure
exec sys.sp_MS_marksystemobject sp_Drop_OrphanedUsers
go

Here is how to execute it for all databases in a SQL Server instance:

USE [master]
GO
EXEC sp_msforeachdb 'USE [?]; EXEC sp_Drop_OrphanedUsers'

Another helpful Link which address your concern

Hope my answer helps!

Related Question