Yesterday there was an interesting question about SQL user MS_DataCollectorInternalUser.The question was something like –

” ms_datacollectorinternaluser is showing up as a login less user.  Where did this come from? Can I remove? ”

This question gave me the idea to write a blog post about –

“User without a login”

My initial response to the question was – No, as it’s related to Data Collector you should not remove it.

I was interested to check more about this user. I haven’t heard about this user before, however I was very sure that it’s related to Data Collector and deleting the same will be a bad idea.

I checked more to find that the user belongs to MSDB database. I knew that we can create a user without a login, however this was the first time I was observing a user without a login for a system database.

Let’s look what is a user without a login –

Starting SQL 2005 we have the ability to create users without logins.This feature was added to replace application roles.

One of a best explanation for login less users was once quoted by Michael Hotek(B/T) in one of the forums –

” When you create a user without a login, there is no path that allows a login to be mapped to that particular user in the database.  The only way to “utilize” this particular user in the database is through impersonation.  Again this is BY DESIGN.  Login less users were added in SQL Server 2005 as a replacement to application roles.  The main reasons behind this were that you did not need to embed a password into an application and you also did not suddenly have multiple people coming into your SQL Server all with the same “login”.  By utilizing login less users, the user’s of an application login to the SQL Server using THEIR login credentials (NOT a generic login), so that you can audit activity to a given login.  Once they change context to the application database, you can issue an EXECUTE AS command to change their user context thereby giving them the authority they need within the application.”

Let’s try to create a user Test_User without a login for database

--Create a user without login  
USE [AdventureWorks]  CREATE USER test_user without login; 
go 

We will now grant db_reader access to Test_User for the database Adventureworks

--Grant db_reader access to the user without a login 
USE [AdventureWorks] 
go 
EXEC Sp_addrolemember 
  N'db_datareader', 
  N'Test_User' 
go

We will now take a login Sam which doesn’t have access to Adventureworks database at all.User Sam is trying to access a table within the Adventureworks database 

--Select statement by the user who doesn't have access to the database
USE [AdventureWorks] 
SELECT * 
FROM   humanresources.employee 
Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'Employee', database 'AdventureWorks', 
schema 'HumanResources'.

We knew that this error is obvious,and now we will impersonate user Test_User who already has access to the Adventureworks database to login Sam

This should help Sam to read the database tables isnt? Lets see

--Impersonate a user to a login
GRANT IMPERSONATE ON User::[Test_User] TO [Sam]

Once this is successful Sam should be able to Execute As user Test_User to read the tables.

--Read the database using Execute AS   
EXECUTE AS User='Test_User'
USE [AdventureWorks]
SELECT * FROM HumanResources.Employee
 

Now as we have seen what is a login without a user and how its useful from application perspective,lets look back what permissions MS_DataCollectorInternalUser is having.

This user without a login is a member of multiple databases roles within the MSDB database.

Deleting this user is not at all allowed[Deleting,modifying anything within system database is not a good idea for that matter] and it can even corrupt a Data Collector setup.

Members of the database role dc_admin can administer and use the data collector and members of the database role db_ssisoperator can administer and use SSIS. Members of SQLAgentUserRole have permissions on  local jobs and job schedules that they own.

Conclusion

Users without a login can help replacing application roles and it can be quite useful for application perspective goals.

We also reviewed the permissions for MS_DataCollectorInternalUser and came to a conclusion that altering anything would cause corruption for the Data Collector setup.

Thanks for reading.