none
Orphaned User MS_DataCollectorInternalUser in MSDB

    Question

  • Found Orphaned User MS_DataCollectorInternalUser in MSDB databases on our SQL2008 and SQL2008R2 servers.  My assumption is that it has to do with the Data Collector. 

    Although this is not a priority item for us, we do have to explain to our clients why they are getting reports of orphaned users on their SQL Servers.  At this point, I'm reporting "working as designed".  Is it an intentional that MS_DataCollectorInternalUser is and orphaned user? 

     

    Friday, July 15, 2011 3:00 PM

Answers

All replies

  • As of SQL Server 2005, we can create a user without login (see CREATE USER command for syntax). I,e,, a "deliberate" user without a login. My guess is that this user is such a user without login. In other words, just removing users without login is no longer a good thing, because they may have been created deliberately and souldn't be removed. Unfortunately, I have not found a way to differentiate a user which is truly orphaned (once upon a time had a login) and a user wihout login. I've asked around about this particular user and will get back here if I get a reply.


    Tibor Karaszi, SQL Server MVP | web | blog
    Saturday, July 16, 2011 12:37 AM
  • Thanks for taking a look at this item.  Having Microsoft come back with a way to differentiate between "purposeful" orphaned users and "accidental" orphaned users would be helpful.
    Monday, July 18, 2011 6:44 PM
  • Hi,

    You can refer below link. Hope you may find it useful.

    http://technet.microsoft.com/en-us/library/bb677179.aspx

     


    Regards, Vishal Srivastava
    Tuesday, July 26, 2011 10:32 AM
  • This query will differentiate between orphaned users and users created WITHOUT LOGIN:

    DECLARE 
    	@dbname varchar(200),
    	@sql varchar(8000)
    
    DECLARE @temp table
    (
    	dbname VARCHAR(500)
    )
    
    CREATE TABLE #dbOrphanUsers
    (
    	DbName varchar(500),
    	[User] varchar(500)
    )
    
    INSERT INTO @temp
    	SELECT name 
    	FROM sysdatabases
    	WHERE 
    		category IN ('0', '1','16')
    		AND 
    		name NOT IN ('tempdb', 'model', 'pubs', 'northwind')
    		AND 
    		name NOT LIKE 'adventurework%' 
    		AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
    	ORDER BY name
    
    SELECT @dbname = MIN(dbname) FROM @temp
    
    WHILE @dbname IS NOT NULL
    BEGIN
    	
    	SET @sql =
    	'INSERT INTO #dbOrphanUsers
    	(DbName, [User])
    
    	SELECT 
    		DbName = ''' + @dbname + ''',
    		[User] = 
    		CASE
    			WHEN b.[User] IS NULL THEN a.[User] + '' (not an orphan, but db user was created using the new SQL 2005 "WITHOUT LOGIN" clause)''
    			ELSE a.[User]
    		END
    	FROM 
    	(
    		SELECT DbName = ''' + @dbname + ''', u.name AS [User]
    		FROM [' + @dbname + '].dbo.sysusers u
    		WHERE 
    			issqluser = 1 
    			and (sid is not null 
    			and sid <> 0x0) 
    		--	and (len(sid) <= 16)
    			and suser_sname(sid) is null
    		) AS a
    	LEFT JOIN
    		(
    		SELECT DbName = ''' + @dbname + ''', u.name AS [User]
    		FROM [' + @dbname + '].dbo.sysusers u
    		WHERE 
    			issqluser = 1 
    			and (sid is not null 
    			and sid <> 0x0) 
    			and (len(sid) <= 16)
    			and suser_sname(sid) is NULL
    		) AS b
    	ON a.[User] = b.[User]
    	
    	UNION
    
    	SELECT DbName = ''' + @dbname + ''', name 
    	FROM [' + @dbname + '].dbo.sysusers 
    	WHERE suser_sname(sid) is NULL AND isntname = 1
    	ORDER BY DbName, [User]'
    		
    	EXEC(@sql)	
    	
    	SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname
    
    END
    
    SELECT * FROM #dbOrphanUsers ORDER BY DbName, [User]
    
    DROP TABLE #dbOrphanUsers
    

    Tuesday, April 01, 2014 3:07 PM