May 282019
 

The other day I was asked why Management Studio limits you to only 200 rows when you right click on a table to edit the data.

The easy answer is because that is how Management Studio is configured. However, it’s also something that can easily be changed. From the menu just select Tools and then Options.

In the Options window, select SQL Server Object Explorer on the left side. On the right side you’ll see the the Table and View Options. Change the Value for Edit Top <n> Rows to something like 500 and then click OK.

Now go back and right click on your table. You will now see Edit Top 500 Rows.

An alternative to changing the configuration value is to click the SQL button on the toolbar.

This opens a query window which will then allow you to edit the TOP (n) value. Once you change the value, then click the Execute button on the tool bar.

You can also rewrite the query to filter out more of the rows before you edit them. Just follow the same steps above; edit the query and then click the Execute button on the toolbar.

Finally, in the same Options window, you can change the value for the Select Top <n> Rows.

This will change the same right click menu except for the the select.

 

May 142019
 

There is still no love for SQL Server Management Studio. SSMS v18 Beta included a Dark Theme, but somehow that did not make it into the final release of the product. Other apps have this capability (Visual Studio, Visual Studio Code, Chrome, Edge) but not SSMS. If you are like me, sometimes you want to switch your apps to a dark theme for added flair. A quick web search will reveal several ways to hack SSMS to display a dark theme, but that may not produce the best results. So, until Microsoft decides to include it, here is how I have been customizing SSMS for the last several years.

Before:

After:

These colors may not work for everyone, but it does demonstrate how you can customize SSMS to fit your tastes.

To get started, from the menu select Tools and then Options.

In the Options window we’ll need to select Environment and the Fonts and Colors.

For Show settings for, select Text Editor and change the font size to 12.

Under Display items, select Plain Text and then click the Custom button next Item foreground.

In the Color window, change the Red to 228, Green to 228, Blue to 228, and then click OK.

Under Item background, select Black from the dropdown list.

Follow the same steps to change each of these other items below.

Text Editor:

  1. Font size: 12

Display Items:

  1. Plain Text
    1. Foreground: 228,228,228
    2. Background: Black
  2. Selected Text
    1. Background: Yellow
  3. Line Number
    1. Foreground: 255, 255, 128
  4. Comment
    1. Foreground: 255, 175, 45
  5. Keyword
    1. Foreground: 127, 176, 228
  6. SQL Operator
    1. Foreground: 199, 199, 199
  7. SQL Stored Procedure
    1. Foreground: 128, 255, 128
  8. SQL String
    1. Foreground: Yellow
  9. SQL System Function
    1. Foreground: 220, 120, 255
  10. SQL System Table
    1. Foreground: 128, 255, 128
  11. String
    1. Foreground: Yellow
  12. URL Hyperlink
    1. Foreground: Cyan

Next, select Grid Results from the Show Settings For dropdown list. Change the font to Microsoft San Serif and the size to 12.

Then select Text Results from the Show Settings For dropdown list and change the font to Consolas and the font size to 12.

Changing the Grid or Text Results will require you to close and reopen SSMS for the changes to take effect. The last thing is to display the line numbers. On the left side of the Options window, select Text Editor, Transact-SQL, and then General. Click the check box next to Line numbers and click OK.

Once you have the colors you want, then go to Tools and click Import and Export Settings.

This wizard allows you to save all the customization settings to a settings file. The file can be used as a backup, or you can use that file to import to SSMS running on another workstation.

Happy customizing!

Jul 172018
 

This article assumes you already have a basic understanding of SQL Server Audit, but if not, use this link to catch up on all the details.

Are you required to have xp_cmdshell enabled on one of your servers? If so, then setup a SQL Audit now to track its use. Never mind the implications of enabling xp_cmdshell, as a DBA you are responsible for what happens on your servers and tracking the use of xp_cmdshell should be a priority.

The first step is to create a server audit to hold the events that we collect.

USE master;
GO
CREATE SERVER AUDIT Audit_xp_cmdshell
TO FILE (FILEPATH = 'E:\SQL2017\SQL_Audit')
WITH (ON_FAILURE = CONTINUE)
WHERE (object_name = 'xp_cmdshell');
GO

You’ll notice that we added a WHERE clause that instructs the audit to only collect events that reference the object xp_cmdshell. All other events will be ignored.

Next, we need to create a server audit specification using the
SCHEMA_OBJECT_ACCESS_GROUP. This server-level action group is triggered when a permission is used to access an object such as xp_cmdshell.

CREATE SERVER AUDIT SPECIFICATION AuditSpec_xp_cmdshell
FOR SERVER AUDIT Audit_xp_cmdshell
  ADD (SCHEMA_OBJECT_ACCESS_GROUP);
GO

Running the following commands will make sure both the audit and audit specification are enabled.

ALTER SERVER AUDIT Audit_xp_cmdshell WITH (STATE = ON);
GO
ALTER SERVER AUDIT SPECIFICATION AuditSpec_xp_cmdshell WITH (STATE = ON);
GO

To test our audit, we need to make sure xp_cmdshell is enabled.

EXEC sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1;
GO
RECONFIGURE;
GO

Then call xp_cmdshell to create some activity.

EXEC xp_cmdshell 'DIR E:\SQL2017\SQL_Tempdb*.* /b';
GO

Viewing the audit log, you can clearly see the command that was executed and the login that called it.

As we have seen, if you have a server that has xp_cmdshell enabled, then using SQL Audit can help you keep track of the commands that have been executed.

Everything we have covered here will work in all editions of SQL Server 2012 and above.

Jan 022018
 

Every now and again as a Microsoft PFE, you get a chance to make a big difference for a customer. One such occasion happened just recently when I was asked to help find a way to automate the daily checks the DBA had to perform every morning. The result was a PowerShell script that reduced that manual task down from an hour to less than a minute.

You can read the full article here on MSDN.
https://blogs.msdn.microsoft.com/samlester/2017/12/29/sql-server-dba-morning-health-checks/

The PowerShell script can be downloaded from here.
https://github.com/PatrickKeisler/SQLMorningHealthChecks

May 162017
 

A while back, I posted an article about creating a WhiteList for access to SQL Server. Since then I have received a bit of feedback that it was not working as designed. My apologies for taking so long, but I believe I have come up with a fix.

The main issue is the trigger will block some or even all access to the server after it’s created. As it turns out, the issues were really permission being denied. To see it in action, let’s create everything using the original code from here.

We’ll add 1 row to the WhiteList table should allow all users from the workstation, ECHOBASE1, access, regardless of its IP address.

USE master;
GO
INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress)
VALUES ('*','ECHOBASE1','*');
GO

Next, we’ll create a SQL login with only connect permission to the server and nothing else.

CREATE LOGIN LogonTriggerTest WITH PASSWORD = 'Password1';
GO

Finally, we’ll open a new query window using that login.

As you can see, we are denied access to the server because of the logon trigger. If we look in the Errorlog, we can see that we lack the VIEW SERVER STATE permission.

This was my first mistake. I did my initial testing using an administrative login. Most users are not admins; therefore, they will not have the permission required to view sys.dm_exec_connections. I was using this DMV to get the IP address of the client connection, and it requires the VIEW SERVER STATE permission. To get around this, I can use the CONNECTIONPROPERTY function, as it does not require any additional permissions.

SELECT CONNECTIONPROPERTY(‘client_net_address’);
GO

Now let’s try to connect again.

Again, we failed. This would be my second mistake. I failed to grant SELECT access to the WhiteList table. By default, a user is will have public permission to the master database, but no permission to the table. To solve this, we can grant permission to the public database role. This will allow any authenticated user to read from the WhiteList table.

USE master;
GO
GRANT SELECT ON dbo.WhiteList TO public;
GO

Finally, our connection to SQL Server is successful. Using the same code from the trigger, we can compare it what’s in the WhiteList table.

USE master;
GO
SELECT
   ORIGINAL_LOGIN() AS 'LoginName'
  ,HOST_NAME() AS 'HostName'
  ,CONNECTIONPROPERTY('client_net_address') AS 'HostIpAddress';
GO
SELECT * FROM dbo.WhiteList;
GO

 

If I had followed my own rules, I could have discovered most of these issues before posting the original article.

The fully updated code is below. Please let me know if you run into any other issues with this new version. I also added another column to the WhiteList table that can be used for hold comments. The idea is to provide some documentation about what the white-listed item is attempting to do.

USE master;
GO
IF OBJECT_ID('dbo.WhiteList') IS NOT NULL
  DROP TABLE dbo.WhiteList;
GO
CREATE TABLE dbo.WhiteList(
   Id INT IDENTITY(1,1) PRIMARY KEY
  ,LoginName VARCHAR(255)
  ,HostName VARCHAR(255)
  ,HostIpAddress VARCHAR(50)
  ,Comments VARCHAR(2000)
);
GO

GRANT SELECT ON dbo.WhiteList TO PUBLIC;
GO

INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress,Comments)
VALUES ('*','ECHOBASE1','*','Any user from the workstation "ECHOBASE1" is allowed to connect, regardless of IP address.')
,('WebSiteLogin','webserver1','192.168.100.55','Only the WebSiteLogin from webserver1 with an IP of 192.168.100.55 is allowed access.');
GO

CREATE TRIGGER WhiteListTrigger
ON ALL SERVER FOR LOGON
AS
BEGIN
  DECLARE
   @LoginName VARCHAR(255) = ORIGINAL_LOGIN()
  ,@HostName VARCHAR(255) = HOST_NAME()
  ,@HostIpAddress VARCHAR(50) = CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address'));

  IF(
    SELECT COUNT(*) FROM dbo.WhiteList
    WHERE ((LoginName = @LoginName) OR (LoginName = '*'))
    AND((HostName = @HostName) OR (HostName = '*'))
    AND((HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*'))
  ) = 0
  ROLLBACK;
END;
GO