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.
” 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.
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.
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.