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.

Today I accidently came across the option called ‘Hide Instance’ flag for a SQL Instance.

This property looked pretty useful for me in case I don’t want anyone to browse for my SQL Instances easily.

There are many ways by which you can browse all the SQL Instances. One way is when using SSMS to connect to an Instance.

A look up on Network Servers will show you all the servers within the same domain. In this case we will concentrate in instance SQL2012A which runs SQL Server 2012 edition.

If you set the Hide Instance option as Yes for this instance, then that will prevent the SQL Server Browser service from exposing this instance of the Database Engine to client computers.

Process 

You will need to recycle the Engine services after setting this flag as True.

Once the service is recycled this takes into effect and the instance SQL2012A will no longer will be available for browse.

Conclusion

I have never seen this flag being set as a part of security best practices; however I would really focus on this point and will check with few SQL experts if they have similar experience.

Thanks for reading.

As we have a new version of Adventureworks database for SQL Server 2012 RC 0 I wanted to start my testing on the same.I downloaded the mdf file from the Codeplex site.

As this is only the MDF file without LDF I went ahead and tried a attach(Attach Databases option) by removing the LDF file which will ensure that we will get a new LDF created automatically.

Right away I was hit hard by a strange error which said

The physical file name path looked strange to me as I did not had such a path in my server.I tried multiple ways to attach the mdf without an ldf including

EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2008R2',
@physname = 'C:\SQL_Data\AdventureWorks2008R2_Data.mdf'

Yesterday in one of the SQL forums there was an issue which was reported by one of the user.The question was something like –

‘I accidentally deleted the only Login which SSMS uses to connect to Database Engine.The server doesn’t have SQL Authentication.How do I connect now? Do I need to do a Uninstall and redo the whole thing?’

Solution – The answer to this is you do not have to uninstall the whole instance and the steps to come out of this trouble is mentioned below :

Members of Windows Administrative group have sysadmin privilege in SQL Server if you start SQL Server in Single user mode.Try the below steps.

1.Add your domain login as Server Admin Group(OS level)

2. Stop SQL Server  using the command, NET STOP MSSQLSERVER if its default instance,and if its named instance,then check the below mentioned article for more syntax.

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

3.Start SQL Server in single user using the command   NET START MSSQLSERVER /m if its default instance,and if its named instance,then check the below mentioned article for more syntax.

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

4. Login using SSMS and your domain ID.

5. You will get an error after opening New Query as SQL will allow only single connection.

6.Disconnect and close object explorer(Important step) and click New Query again.

7. Now you are connected and you can create a login using TSQL scripts and assign sysadmin.

So next time when you find this problem,you have a proven solution available.

Do drop in a comment in-case any of the above mentioned steps is not working for you.

Thanks for reading.

Recently I was asked this interesting question – I need to revoke all privileges from a single table in the database,how can I do that?

Solution is simple – Using TSQL you can easily achieve this task.For Select permissions you can have

REVOKE SELECT ON dbo.tablename FROM user.

You can list the permissions granted to an object using sp_helprotect ‘dbo.table’

You can easily find that using

SELECT LOGINPROPERTY(‘TestUser’, ‘IsLocked’)

You can unlock the login using

ALTER LOGIN [XYZ] WITH PASSWORD = ‘****’ UNLOCK ;

GO

Today I came across an error

Property Owner is not available for Database ‘[DBName]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

Situation – You right-click on the database and choose properties. Now, instead of the database properties opening up, you get the message.

Cause – This issue can happen if the owner of the database is dropped from the Security Logins of the SQL Server.

Solution –

sp_changedbowner [ @loginame = ] ‘login’

Sample

use [YourDatabase]

GO

sp_changedbowner @loginame = ‘sa’

GO