Archive for the ‘Tips and Tricks’ Category

How to resolve the users “disabled” issue?

First of all, let’s clarify this up front! There’s no such thing as disabling and enabling users in SQL Server. If you keep on communicating to other professional SQL Server DBAs that you wish to disable or enable a user, we will put you in the “ESPN C’MON MAN!” segment because you had fumbled in the 1 yard line without anyone tackling you. Make no mistake about it. There’s disabling and enabling logins. But, there’s no such thing as disabling and enabling users! What if you see the user has the “Red Down” arrow? Do not Google too much on this topic or else some of the forums will tell you to remove the user and then create it again! Don’t do it. The following will describe the symptom and the remediation.

If you see the user with the “Red Down” arrow like the following screen-shot, the most likely cause is that someone had revoked the “CONNECT” privilege from the user or someone had just performed the following steps: 1) created a login from Windows, 2) created the user in the corresponding database, 3) then added the user to a database role. In both cases, the “CONNECT” privilege is missing.
User CONNECT Permission Revoked

To reproduce this, you can use the following steps:
1. Create the test login.

-- Create Login
USE [master];
CREATE LOGIN [Billy] WITH PASSWORD=N'Bob', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; -- this is for demo only

2. Create the test user.

USE [AdventureWorks2012];
CREATE USER [Billy];

3. Add the user to a database role. Note: for SQL Server Logins, you will not experience the “CONNECT” not granted issue as in the case for Windows Logins.

USE [AdventureWorks2012];
EXEC SP_ADDROLEMEMBER 'db_datareader', 'Billy'

4. Revoke the “CONNECT” privilege from the user. Note: you need to use [] around the user as in [Billy]. You will then see the user “disabled” with the “Red Down” arrow next to it.

USE [AdventureWorks2012];
REVOKE CONNECT FROM [Billy];

Solution:
To resolve this issue, you will just need to grant the “CONNECT” privilege to the user. Yes. That’s all she wrote!

USE [AdventureWorks2012];
GRANT CONNECT TO [Billy];

To confirm the user has access:

-- To confirm the user has access to the database
USE [AdventureWorks2012];
SELECT name, 
	hasdbaccess
FROM sys.sysusers 
WHERE name = 'Billy'

How to Display Line Numbers in the SQL Server Management Studio Query Window?

If it hasn’t already happened to you as a SQL Server DBA, you can bet on it that it will at some point in the near future. You are writing a script that has more than two pages, after you pat yourself on the back for a job well-done, you hit the execute button. To your horror, the script has an error. The error message references to a particular line number within your script. It is 3 am in the morning, you need to have this script ready and then head to bed for 3 hours of sleep before you have to get up to race to catch the railroad or the bus into the city. You are frustrated. You need to find the line number where the syntax error has occurred so that you can fix it or have a good starting point to dig a little deeper into what is the possible problem. Let’s “Keep Calm and Carry On.” SQL Server Management Studio (SSMS) has the option for you to enable the line numbers and here’s how it can be done:
1. Open SSMS. The on the menu bar, click on Tools > Options…
Tools and Options
2. On the “Options” dialog box, expand the “Text Editor” node then expand the “Transact-SQL” node. Click on “General” on the subtree and then on the “Display” section click on the “Line numbers” checkbox to enable or disable line numbering.
Line Number
3. Open a new query window and key in some T-SQL code. You will see the line number on the left hand side:
Query Window With Line Numbering

How to Change the Color in the SQL Server Management Studio Status Bar?

Have you ever executed a script that’s intended for testing purposes in the Production SQL Server environment rather than in the Development environment? If you have had this regrettable experience, don’t you wish SQL Server can give you color code warnings of the different types of environment? Well, actually, SQL Server does have this option and it is relatively easy to configure it. Similar to the traffic lights, you can configure red for Production, yellow for Staging, and green for Development. Just like traffic light, it can avert disasters or save you many hours to undo or attempt to undo the avoidable mistake.
Here’s how it is done:
1. Open SSMS.
2. On the “Connect to Server” dialog box, click the “Options >>” button.
Connect to Server
3. Click the “Connection Properties” tab, click on the “Select…” button to pick a color, and then click on the “Connect” button to connect to the designated SQL Server instance.
Connection Properties
Color Picker
4. Open a new query window. You will then see the color that you had picked as the background color on the status bar.
SSMS Red Status Bar