SQL SERVER – Solution – User Not Able to See Any User Created Object in Tables – Security and Permissions Issue

There is an old quote “A Picture is Worth a Thousand Words”. I believe this quote immensely. Quite often I get phone calls that something is not working if I can help. My reaction is in most of the cases, I need to know more, send me exact error or a screenshot. Until and unless I see the error or reproduce the scenario myself I prefer not to comment. Yesterday I got a similar phone call from an old friend, where he was not sure what is going on. Here is what he said.

“When I try to connect to SQL Server, it lets me connect just fine as well let me open and explore the database. I noticed that I do not see any user created instances but when my colleague attempts to connect to the server, he is able to explore the database as well see all the user created tables and other objects. Can you help me fix it? “

My immediate reaction was he was facing security and permission issue. However, to make the same recommendation I suggested that he send me a screenshot of his own SSMS and his friend’s SSMS. After carefully looking at both the screenshots, I was very confident about the issue and we were able to resolve the issue. Let us reproduce the same scenario and many there is some learning for us.

Issue: User not able to see user created objects

First let us see the image of my friend’s SSMS screen. (Recreated on my machine)

Now let us see my friend’s colleague SSMS screen. (Recreated on my machine)

You can see that my friend could not see the user tables but his colleague was able to do the same for sure. Now I believed it was a permissions issue. Further to this I asked him to send me another image where I can see the various permissions of the user in the database.

My friends screen

My friends colleagues screen

This indeed proved that my friend did not have access to the AdventureWorks database and because of the same he was not able to access the database. He did have public access which means he will have similar rights as guest access. However, their SQL Server had followed my earlier advise on having limited access for guest access, which means he was not able to see any user created objects.

My next question was to validate what kind of access my friend’s colleague had. He replied that the colleague is the admin of the server. I suggested that if my friend was suppose to have admin access to the database, he should request of having admin access to his colleague. My friend promptly asked for the same to his colleague and on following screen he added him as an admin.

You can do the same using following T-SQL script as well.

USE [AdventureWorks2012]
GO
ALTER ROLE [db_owner] ADD MEMBER [testguest]
GO

Once my friend was admin he was able to access all the user objects just like he was expecting.

Please note, this complete exercise was done on a development server. One should not play around with security on live or production server. Security is such an issue, which should be left with only senior administrator of the server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database

I have written a few articles recently on the subject of guest account. Here’s a quick list of these articles:

One of the advices which I gave in all the three blog posts was: Disable the guest user in the user-created database. Additionally, I have mentioned that one should let the user account become enabled in MSDB database. I got many questions asking if there is any specific reason why this should be kept enabled, questions like, “What is the reason that MSDB database needs guest user?” Honestly, I did not know that the concept of the guest user will create so much interest in the readers. So now let’s turn this blog post into questions and answers format.

Q: What will happen if the guest user is disabled in MSDB database?
A:  Lots of bad things will happen. Error 916 – Logins can connect to this instance of SQL Server but they do not have specific permissions in a database to receive the permissions of the guest user.

Q: How can I determine if the guest user is enabled or disabled for any specific database?
A: There are many ways to do this. Make sure that you run each of these methods with the context of the database. For an example for msdb database, you can run the following code:

USE msdb;
SELECT name, permission_name, state_desc
FROM sys.database_principals dp
INNER JOIN sys.server_permissions sp
ON dp.principal_id = sp.grantee_principal_id
WHERE name = 'guest' AND permission_name = 'CONNECT'

There are many other methods to detect the guest user status. Read them here: Detecting guest User Permissions – guest User Access Status

Q: What is the default status of the guest user account in database?
A: Enabled in master, TempDb, and MSDB. Disabled in model database.

Q: Why is the default status of the guest user disabled in model database?
A: It is not recommended to enable the guest in user database as it can introduce serious security threat. It can seriously damage the database if configured incorrectly. Read more here: Disable Guest Account – Serious Security Issue

Q: How to disable guest user?
A: REVOKE CONNECT FROM guest

Q: How to enable guest user?
A: GRANT CONNECT TO guest

Did I miss any critical question in the list? Please leave your question as a comment and I will add it to this list.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Detecting guest User Permissions – guest User Access Status

Earlier I wrote the blog post SQL SERVER – Disable Guest Account – Serious Security Issue, and I got many comments asking questions related to the guest user.

Here are the comments of Manoj:

1) How do we know if the uest user is enabled or disabled?
2) What is the default for guest user in SQL Server?

Default settings for guest user

When SQL Server is installed by default, the guest user is disabled for security reasons. If the guest user is not properly configured, it can create a major security issue. You can read more about this here.

Identify guest user status

There are multiple ways to identify guest user status:

Using SQL Server Management Studio (SSMS)

You can expand the database node >> Security >> Users. If you see the RED arrow pointing downward, it means that the guest user is disabled.

Using sys.sysusers

Here is a simple script. If you notice column dbaccess as 1, it means that the guest user is enabled and has access to the database.

SELECT name, hasdbaccess
FROM sys.sysusers
WHERE name = 'guest'

Using sys.database_principals and sys.server_permissions

This script is valid in SQL Server 2005 and a later version. This is my default method recently.

SELECT name, permission_name, state_desc
FROM sys.database_principals dp
INNER JOIN sys.server_permissions sp
ON dp.principal_id = sp.grantee_principal_id
WHERE name = 'guest' AND permission_name = 'CONNECT'

Using sp_helprotect

Just run the following stored procedure which will give you all the permissions associated with the user.

sp_helprotect @username = 'guest'

Disable Guest Account

REVOKE CONNECT FROM guest

Additionally, the guest account cannot be disabled in master and tempdb; it is always enabled. There is a special need for this. Let me ask a question back at you:

In which scenario do you think this will be useful to keep the guest, and what will the additional configuration go along with the scenario?

Note: Special mention to Imran Mohammed for being always there when users need help.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Force Removing User from Database – Fix: Error: Could not drop login ‘test’ as the user is currently logged in.

Yesterday I wrote a blog post discussing how guest user can become security threat. The script which was demonstrated for the example had a small T-SQL query which creates a new user. Later, I got an email from a user who had created this scenario on his production environment. It makes me sad that I had clearly talked multiple times about how to execute this as a trial on a development server or a test server, but NOT on a production server. Anyway, here is the email:

“Pinal,

I ran your test on our production server as our development server was busy. Now when I try to drop the test user, it gives me the following error:

Msg 15434, Level 16, State 1, Line 1
Could not drop login ‘test’ as the user is currently logged in.

Could you please help me drop this test user? I cannot restart the server as it is a production server, and I need to drop this user as fast as I can. First of all, I have closed all the connections but I do not know which connection is still open. I just need to identify which connection is open and kill it if required. In any case, I need to drop it before my supervisor catches me.”

This email I read really felt very awkward. Here is my answer. I hope you do not find it rude.

“Dear Friend,

First of all, I want to ask why you ran the demo on your production server. You must not execute it on a production server. It was just for the purpose of demonstration. This reminds me of an email I received from one of my friends that one should never post details which can harm a certain production server, as there are people who do not follow the advice no matter how many times you warn them.

Trust me – your supervisor will have configured audits and he will soon discover this test user, whether you tell him or not. I suggest that instead of hiding, you go and tell him everything in the most honest way you can.

Here is one more tip for you – killing any sessions without permission from a user or a senior admin will get you in trouble.

Once again I do not want your server to be exposed to potential security threats while your supervisor is away, so follow the steps mentioned below to drop the test user:

Find an active connection using the test user:

SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'test'

Kill the active connection using the test user:

KILL 52 -- Replace 52 with the your session ids received from earlier query

Drop the test user:

DROP LOGIN test

Hopefully you’d keep your production server away from executing scripts meant to run on a development server.”

Well, after writing the email I did not feel good as I did not like to sound rude. However, I kept telling myself that it was needed. What do you think about this whole conversation?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Disable Guest Account – Serious Security Issue

“No Guests PLEASE!”

“Doesn’t your Indian tradition suggest welcoming guests and treating them in the best way possible?”

“Yes, but I am talking about the Guest user in SQL Server.”

Oh!”

This was a real conversation that happened a couple of years ago. I welcome guests as much as any other Indian does; however, I am strongly opinionated about guest user in SQL Server. I like to keep it disabled unless there is a special need of it. If there is some persistent need of a guest user, I suggest to create separate account. Again, there are always special cases where there is a need of this guest account. But in other cases, a guest account is not necessary.

A Real World Story

Today I am in the mood to tell a real life story.

About 2 years ago, I got a call very late in the evening from one of my friends. He said that his database was compromised and so he needed to talk to me. He described the situation to me – that his database had a single user (himself), but without his knowledge, his database was often observing non-permitted activities. He was very scared.

When there is no real clue, most companies take the database down or turn it offline to prevent any further damage to the system.

Just like the same, he decided to disconnect the database from other applications, as well as to move th behind firewall of the system. After making sure that there is no other application connected even at behind the firewall, there was still this inappropriate activity going on. He became more worried, and finally he sought my help.

I decided to take up the challenge to help him. After a while, we discovered that he had the GUEST user enable on his database. Even though he had already removed all the other accounts, he missed to remove the guest user account. As the guest user was enabled, any user who had permission to server was able to get into his database. Well, after a careful review, we realize that one of his co-workers was pulling the prank on him. He had given additional permission to his guest user which leads any user logging into server got admin rights to my friend’s database.

Upon figuring out this issue, we disabled the guest account and the problem was resolved. To complete the story, the guy who played the prank on my friend was moved to another department (almost as good as fired) where he could not touch the production database.

Reproduction of the scenario

After remembering the story, I decided to recreate the scenario here. Please note: do not play with Guest Account without fully understanding its implications.

USE AdventureWorks
GO
-- Note: NEVER run this on production server
-- Enabling guest account
GRANT CONNECT TO guest
GO
ALTER ROLE [db_owner] ADD MEMBER [guest]
GO
-- create test login
USE MASTER
GO
CREATE LOGIN [test] WITH PASSWORD=N'Complex@1212';
GO

When you explore the logins of the AdventureWorks database, you will not see ‘test’ as a user. Now let us login into SQL Server using ‘test’ user. When user will login using ‘test’ user he will be logged in as guest user in AdventureWorks database. Every user who login to AdventureWorks as guest user it will have admin access to the database.

This was the precise situation my friend had and he was not able to understand where things went wrong. As I said earlier, unless there is special case, guest user should be disabled.

Run following script to drop ‘test’ account which we created.

USE MASTER
GO
DROP LOGIN [test]
GO

Script to disable the guest account

REVOKE CONNECT FROM guest
GO

Well, share your stories in comments area. Additionally, have you ever prank your friend, if yes, I want to know. Additionally, for master, msdb and tempdb database guest user is required for SQL Server. If you enable guest database on modeldb, all the subsequent created database will have it enable, which is serious security issue.

NOTE: Do not try any of the script on production server or live environment. These scripts are for better understanding of the subject.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Case Sensitive Database and Database User – Fix: Error: 15151 – Cannot find the user , because it does not exist or you do not have permission.

Jeff asked me another question!

If you do not know Jeff, you may read the following blog posts. You will get the idea of Jeff’s personality and who Jeff really is.

This time, he sent me a screenshot. He was facing a very strange error. As his screenshot had confidential details, I created my own images which exactly simulate his issue for demonstration’s sake. Here are the partial details of his email. Please note that I have replaced his username and database name to protect his private information.

“Hi Pinal,

I think I am having one of those moments when I am feeling stupid or SQL Server is having fun with me.

Here is the scenario. I have two databases: AdventureWorks and MyAdventureWorks. I have a user called SQLAuthority in both  databases. Now when I try to grand access the user in one database it works fine, but when I try to do the same thing in another database, it gives me error. I am stunned as both the users are same. As a matter of fact, I had just created those users recently, and now I am not able to gain necessary permission.

Here is the screenshot where everything works fine:

Here is the screenshot that shows where I am getting crazy and gives following error:

Msg 15151, Level 16, State 1, Line 2
Cannot find the user ‘sqlauthority’, because it does not exist or you do not have permission.

Help me again. Why is this happening? I do not get it. HELP!”

That’s a very interesting question from him. For a moment, I was speechless as it is difficult for me to solve the problem for him remotely. After a while, I noticed that in his example, the case of the username did not match. In SSMS the username was SQLAuthority and in his T-SQL script it was sqlauthority. I told him to change the case of the T-SQL to match with the case of SSMS, and it right away solved his problem.

I asked him to run the following script which validated that his database was throwing an error because of case sensitivity. Read here how you can identify collation of any database: SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') AdventureWorks;
SELECT DATABASEPROPERTYEX('MyAdventureWorks', 'Collation') MyAdventureWorks;

The above query returns the following results:

Learning: Case sensitivity of a database does not only matter in the data of the table, but also in objects like tablename, columnname, and even username. If you know any other situation like this, please share your knowledge in the comments area.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2012 Auditing Enhancement – On Audit Log Failure Options – Maximum Rollover Files

Recently I was exploring SQL Server Audit and found something very interesting. I found two enhancements in the SQL Server 2008 Create Audit Screen.

SQL Server 2012 Create Audit Screen

SQL Server 2008 Create Audit Screen

On Audit Log Failure Options

You can see that in SQL Server 2012 they have added two more options for audit log failure. In earlier version the only option was to shut down the server when there was audit log failure. Now you can fail the operation as well continue on log failure. This new options now give finer control on the behavior of the audit failure scenario.When target is not available due to any reason and audit cannot log the event, it can be now continued, in another word the audit continues to attempt to log events and will resume if the failure condition is resolved. This is very important feature because earlier when Audio was failing the option which we had was to shutdown the server. There were the cases when shutting down the server is not the good option but continuing the business operation is the priority, this option should be exercised. Additionally, note that the new default value is now CONTINUE. User has option to select Fail Operation where the Audit will not attempt when the target is available or continuing auditing is possible.

Maximum Rollover Files

Earlier, there were two options – have infinite number of log files or roll over the files after fixed number. Now in SQL Server 2012 there is option to keep the fixed number of the files along with no roll-over. This gives additional control to user when they want to save every single data and do not want to lose any critical information due to rollover.

Reference : Pinal Dave (http://blog.sqlauthority.com)