Thursday, July 21, 2011

MS_DataCollectorInternalUser User with No Login

Found what I thought was an orphaned User MS_DataCollectorInternalUser in the MSDB databases on our SQL2008 and SQL2008R2 servers.  My assumption is that it has to do with the Data Collector.  What I did not realize is that this user account is not an orphaned user at all.  It is a user account intentially created with no SQL Login.

As of SQL Server 2005, a user can be created without login - see MSDN reference.  The example given in this link for the use of this kind of user with no login is to grant access to the user and then grant impersonation of that user to an SQL Login.  From the same link above:

USE AdventureWorks2008R2 ;
CREATE USER CustomApp WITHOUT LOGIN ;
GRANT IMPERSONATE ON USER::CustomApp TO [adventure-works\tengiz0] ;
GO

To use the CustomApp access rights:

EXECUTE AS USER = 'CustomApp' ;
GO

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 a login. I've left messages on Microsoft forums; however, no one seems to know how to differentiate between orphaned users and users created with no login.

1 comment:

  1. Hi, John!

    Same problem to me - I found out the length of SID did the job, see also here:

    http://ask.sqlservercentral.com/questions/77192/sql-user-without-a-login-or-without-login


    Regards from Berlin / Germany

    SeBaFlu

    ReplyDelete