Tag: TSQL

Logon Triggers Do Not Prevent SSMS Connections

I recently saw a post on StackExchange where a user was having a problem with tempdb filling up, which was causing their logon trigger to fail.

The logon trigger was attempting to prevent users from connecting with SQL Server Management Studio (SSMS). It seems that the user was not aware that it is trivial to connect using SSMS to SQL bypassing the app level restriction in place.

Let’s look at an example.

First we’ll create the logon trigger that explicitly looks to see if the application attempting to logon is management studio:

CREATE TRIGGER [DenySSMSLogin] ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
SET NOCOUNT ON;
DECLARE @app SYSNAME = APP_NAME();
IF @app LIKE N'%Management Studio%'
BEGIN
THROW 51000, 'Connection not allowed.', 1;
END;
END;
GO

ENABLE TRIGGER [DenySSMSLogin] ON ALL SERVER;
GO

 

Now we can test this by opening a new window. We should see an error:

failed-to-logon

Looks like it’s working just fine.

So let’s circumvent this, try connecting again, this time we just need to edit the Additional Connection Parameters in the connection dialog and pass along a new application name.

additional-connection-parameters

And now the connection will work just fine, completely bypassing the trigger (if trying this, don’t forget to disable your trigger when done).

Formatting Number Output

I’ve been working with some large data load processes recently and have been dumping upwards of a billion records into tables. As these are not instantaneous loads I found myself wanting to keep an eye on how much data was loaded.

This is nice and easy to do using the sys.partitions system view. Here’s an example from AdventureWorks

SELECT t.name, rows

FROM sys.partitions p

   INNER JOIN sys.tables t

       ON p.OBJECT_ID = t.OBJECT_ID

WHERE p.index_id IN (0,1)

ORDER BY rows DESC;

top+10

This is all good and well, but when you start getting to large values on the tables it becomes a little difficult to tell the difference between 31 million and 312 million. That’s when I resort back to using commas. Unfortunately SQL Server does not make this easy, although it can be accomplished with a couple of converts.

SELECT t.name, substring(convert(varchar, convert(money, rows), 1), 1, len(convert(varchar, convert(money, rows), 1))-3)

FROM sys.partitions p

   INNER JOIN sys.tables t

       ON p.OBJECT_ID = t.OBJECT_ID

WHERE p.index_id IN (0,1)

ORDER BY rows DESC

top+10+2

This is still a little arduous to type, so I created a function to do the work. Now it’s just a case of calling that function whenever I want to have the numbers presented to me in a much more readable format.

CREATE FUNCTION dbo.fnNumberComma (@iVal bigint)

RETURNS VARCHAR(30)

AS

BEGIN

DECLARE @oVal VARCHAR(30)


SELECT @oVal = SUBSTRING(CONVERT(VARCHAR(30), CONVERT(money, @iVal),1),1,LEN(CONVERT(VARCHAR(30),CONVERT(money,@iVal),1))-3)

RETURN (@oVal)

END

GO



SELECT t.name, dbo.fnNumberComma(rows) as [RowCount]

FROM sys.partitions p

   INNER JOIN sys.tables t

       ON p.OBJECT_ID = t.OBJECT_ID

WHERE p.index_id IN (0,1)

ORDER BY rows DESC;

top+10+3

Extended Properties Are Your Friend

It’s nice to have friends, why aren’t you a friend of extended properties? They can make your like so much easier by helping to document your databases and objects.

Take a basic table create statement

CREATE TABLE [dbo].[EatSomeBoogers](

    [ID] [int] NOT NULL,

    [BgType] [varchar](20) NOT NULL,

    [Size] [smallint] NULL,

    [Viscosity] [tinyint] NULL

) ON [PRIMARY]

 

GO

 

Pretty simple and we can infer a lot of the information about the table, but we mention size, are we measuring in inches, feet, yards?

We can add extended properties that will help to provide that sort of guidance for someone to reference later on.

EXEC sys.sp_addextendedproperty @name = N'Details',

    @value = N'Size is measured in mm', @level0type = N'SCHEMA',

    @level0name = N'dbo', @level1type = N'TABLE',

    @level1name = N'EatSomeBoogers', @level2type = N'COLUMN',

    @level2name = N'Size'

GO

We can also add properties at the table level:

EXEC sys.sp_addextendedproperty @name = N'Purpose',

    @value = N'Holds information about all the gold digging’,

    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',

    @level1name = N'EatSomeBoogers'

GO

And at the database level:

EXEC [MyDB].sys.sp_addextendedproperty @name = N'Usage',

    @value = N'Will handle all information as relates to digging' 

GO

 

You can even add extended properties to other objects, like stored procedures:

EXEC sys.sp_addextendedproperty @name = N'ProcUsage',

    @value = N'Gets booger sizes and types', @level0type = N'SCHEMA',

    @level0name = N'dbo', @level1type = N'PROCEDURE',

    @level1name = N'GrabBoogers'

GO

 

What’s great is that you can then quickly and easily query the extended properties for your objects:

SELECT  

        OBJECT_NAME(ep.major_id) AS ObjectName ,

        CASE 

        WHEN c.name IS NOT NULL then 'COLUMN'

        else o.type_desc

        END AS ExtendedPropertyType,

        c.name AS 'ColumnName' ,

        ep.name AS ExtendedPropertyName ,

        ep.value AS ExtendedPropertyValue

FROM    sys.extended_properties ep

        LEFT JOIN sys.columns c ON ep.major_id = c.object_id

                                   AND ep.minor_id = c.column_id

        LEFT JOIN sys.objects o ON ep.major_id = o.object_id

ORDER BY ObjectName, ColumnName

image

 

 

This give you a really quick and easy way to document your objects. I highly recommend that during your next development project that you make life easy for yourself and add extended properties to your objects (and trust me, your DBA will thank you).

Speedy Log Shipping Setup

As a follow up to last weeks post on quickly restoring multiple databases to default locations I’ve another script which might help you with quick deployments.

Last week I covered restoring the databases, I also needed to get log shipping setup for multiple databases. If you’ve ever had to configure log shipping you know that there’s lots of clicking and typing of things and entering information. It’s a bit of a pain for a single database, when you have to set it up for multiple databases it’s a long, slow, time consuming nightmare.

I’d had enough of dealing with this so I put together a script that will do the complete configuration of the primary and secondary servers with a single execution. The key to doing this is using SQLCMD mode from within SSMS.

Never used SQLCMD mode before? I’m not surprised, it’s not that well known. To enter SQLCMD mode you either choose the option from the Query menu item or add the button to one of your tool bars

image

This allows you to do some special things, like parameter substitution and, key to what I’m doing, change connections as a part of the script.

Try the following, replacing PrimaryServer and SecondaryServer with something relevant to your environment.

image

As you can see from the script it connected to each server in turn and grabbed the @@SERVERNAME value. That’s pretty special.

My log shipping script does very much the same thing. You just provide the primary and secondary servers, the file locations for the transaction log backups and the name of the database. The script takes care of the rest.

It will perform a very basic check to ensure that the local backup directory exists and error if not. The script also does not encompass a log shipping monitor server and has a default of 15 minutes for the backup/copy/restore processes. All of that could be quickly adjusted and made additional parameters.

View the script below or download SetupLogShipping.sql

Of course this doesn’t answer the question of when or why you would use log shipping, that’s for another post.

:ON ERROR exit

 

:setvar PrimaryServer "ServerA"

:setvar PrimaryDatabase "MyDB"

:setvar PrimaryBackupDirectory "E:BackupDBTrxLogs"

:setvar PrimaryBackupShare "ServerAtrxlogs$"

:setvar SecondaryServer "ServerB"

:setvar SecondaryDatabase "MyDB"

:setvar SecondaryBackupDirectory "ServerBTrxlogs$TrxForRestore"

 

SET NOCOUNT ON

/* Begin: Script to be run at Primary */

 

 

:CONNECT $(PrimaryServer)

 

BEGIN

USE msdb;

 

/* Check to see if the backup folder exists */

RAISERROR('Checking to see if $(PrimaryBackupDirectory) exists...',0,1) WITH NOWAIT

DECLARE @CheckDir NVARCHAR(2000)

DECLARE @ErrorMsg NVARCHAR(200)

DECLARE @DirInfo TABLE

    (

      FileExists BIT ,

      FileIsADirectory BIT ,

      ParentDirectoryExists BIT

    )

                                

SET @CheckDir = '$(PrimaryBackupDirectory)'

 

INSERT  INTO @DirInfo

        ( FileExists ,

          FileIsADirectory ,

          ParentDirectoryExists

        )

        EXECUTE [master].dbo.xp_fileexist @CheckDir

 

 

 

IF NOT EXISTS ( SELECT  *

                FROM    @DirInfo

                WHERE   FileExists = 0

                        AND FileIsADirectory = 1

                        AND ParentDirectoryExists = 1 ) 

    BEGIN

        SET @ErrorMsg = 'The primary backup directory $(PrimaryBackupDirectory) does not exist...quitting' 

        RAISERROR(@ErrorMsg,16,1) WITH NOWAIT

        RETURN

    END

RAISERROR('$(PrimaryBackupDirectory) exists, continuing...',0,1) WITH NOWAIT  

 

DECLARE @LS_BackupJobId    AS uniqueidentifier 

DECLARE @LS_PrimaryId    AS uniqueidentifier 

DECLARE @SP_Add_RetCode    As int 

 

RAISERROR('Enabling log shipping for $(PrimaryDatabase) on $(PrimaryServer)...',0,1) WITH NOWAIT

 

EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 

        @database = N'$(PrimaryDatabase)' 

        ,@backup_directory = N'$(PrimaryBackupDirectory)' 

        ,@backup_share = N'$(PrimaryBackupShare)' 

        ,@backup_job_name = N'LSBackup_$(PrimaryDatabase)' 

        ,@backup_retention_period = 4320

        ,@backup_compression = 1

        ,@backup_threshold = 60 

        ,@threshold_alert_enabled = 1

        ,@history_retention_period = 5760 

        ,@backup_job_id = @LS_BackupJobId OUTPUT 

        ,@primary_id = @LS_PrimaryId OUTPUT 

        ,@overwrite = 1 

 

 

IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 

BEGIN 

 

DECLARE @LS_BackUpScheduleUID    As uniqueidentifier 

DECLARE @LS_BackUpScheduleID    AS int 

 

 

EXEC msdb.dbo.sp_add_schedule 

        @schedule_name =N'LSBackupSchedule_$(PrimaryServer)' 

        ,@enabled = 1 

        ,@freq_type = 4 

        ,@freq_interval = 1 

        ,@freq_subday_type = 4 

        ,@freq_subday_interval = 15 

        ,@freq_recurrence_factor = 0 

        ,@active_start_date = 20120607 

        ,@active_end_date = 99991231 

        ,@active_start_time = 0 

        ,@active_end_time = 235900 

        ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 

        ,@schedule_id = @LS_BackUpScheduleID OUTPUT 

 

EXEC msdb.dbo.sp_attach_schedule 

        @job_id = @LS_BackupJobId 

        ,@schedule_id = @LS_BackUpScheduleID  

 

EXEC msdb.dbo.sp_update_job 

        @job_id = @LS_BackupJobId 

        ,@enabled = 1 

 

 

END 

 

 

EXEC master.dbo.sp_add_log_shipping_alert_job 

 

EXEC master.dbo.sp_add_log_shipping_primary_secondary 

        @primary_database = N'$(PrimaryDatabase)' 

        ,@secondary_server = N'$(PrimaryServer)' 

        ,@secondary_database = N'$(SecondaryDatabase)' 

        ,@overwrite = 1 

 

 

RAISERROR('Log shipping enabled for $(PrimaryDatabase) on $(PrimaryServer)...',0,1) WITH NOWAIT

END

GO

/* ENDS THE PRIMARY SCRIPT */

 

 

 

/* Script to be run on secondary */

:CONNECT $(SecondaryServer)

USE msdb;

 

DECLARE @LS_Secondary__CopyJobId    AS uniqueidentifier 

DECLARE @LS_Secondary__RestoreJobId    AS uniqueidentifier 

DECLARE @LS_Secondary__SecondaryId    AS uniqueidentifier 

DECLARE @LS_Add_RetCode    As int 

 

RAISERROR('Adding log shipping secondary to $(SecondaryServer) with database name $(SecondaryDatabase)...',0,1) WITH NOWAIT

 

EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 

        @primary_server = N'$(PrimaryServer)' 

        ,@primary_database = N'$(PrimaryDatabase)' 

        ,@backup_source_directory = N'$(PrimaryBackupShare)' 

        ,@backup_destination_directory = N'$(SecondaryBackupDirectory)' 

        ,@copy_job_name = N'LSCopy_$(PrimaryServer)_$(PrimaryDatabase)' 

        ,@restore_job_name = N'LSRestore_$(PrimaryServer)_$(PrimaryDatabase)' 

        ,@file_retention_period = 4320 

        ,@overwrite = 1 

        ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 

        ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 

        ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 

 

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 

BEGIN 

 

DECLARE @LS_SecondaryCopyJobScheduleUID    As uniqueidentifier 

DECLARE @LS_SecondaryCopyJobScheduleID    AS int 

 

RAISERROR('Creating log shipping secondary jobs...',0,1) WITH NOWAIT

 

EXEC msdb.dbo.sp_add_schedule 

        @schedule_name =N'DefaultCopyJobSchedule' 

        ,@enabled = 1 

        ,@freq_type = 4 

        ,@freq_interval = 1 

        ,@freq_subday_type = 4 

        ,@freq_subday_interval = 15 

        ,@freq_recurrence_factor = 0 

        ,@active_start_date = 20120607 

        ,@active_end_date = 99991231 

        ,@active_start_time = 0 

        ,@active_end_time = 235900 

        ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT 

        ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT 

 

EXEC msdb.dbo.sp_attach_schedule 

        @job_id = @LS_Secondary__CopyJobId 

        ,@schedule_id = @LS_SecondaryCopyJobScheduleID  

 

DECLARE @LS_SecondaryRestoreJobScheduleUID    As uniqueidentifier 

DECLARE @LS_SecondaryRestoreJobScheduleID    AS int 

 

 

EXEC msdb.dbo.sp_add_schedule 

        @schedule_name =N'DefaultRestoreJobSchedule' 

        ,@enabled = 1 

        ,@freq_type = 4 

        ,@freq_interval = 1 

        ,@freq_subday_type = 4 

        ,@freq_subday_interval = 15 

        ,@freq_recurrence_factor = 0 

        ,@active_start_date = 20120607 

        ,@active_end_date = 99991231 

        ,@active_start_time = 0 

        ,@active_end_time = 235900 

        ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT 

        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT 

 

EXEC msdb.dbo.sp_attach_schedule 

        @job_id = @LS_Secondary__RestoreJobId 

        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID  

 

 

END 

 

 

DECLARE @LS_Add_RetCode2    As int 

 

 

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 

BEGIN 

 

EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 

        @secondary_database = N'$(SecondaryDatabase)' 

        ,@primary_server = N'$(PrimaryServer)' 

        ,@primary_database = N'$(PrimaryDatabase)' 

        ,@restore_delay = 0 

        ,@restore_mode = 0 

        ,@disconnect_users    = 0 

        ,@restore_threshold = 45   

        ,@threshold_alert_enabled = 1 

        ,@history_retention_period    = 5760 

        ,@overwrite = 1 

 

END 

 

 

IF (@@error = 0 AND @LS_Add_RetCode = 0) 

BEGIN 

 

EXEC msdb.dbo.sp_update_job 

        @job_id = @LS_Secondary__CopyJobId 

        ,@enabled = 1 

 

EXEC msdb.dbo.sp_update_job 

        @job_id = @LS_Secondary__RestoreJobId 

        ,@enabled = 1 

 

END 

 

RAISERROR('Setup of log shipping from database $(PrimaryDatabase) on server $(PrimaryServer) to database $(SecondaryDatabase) on $(SecondaryServer) complete!',0,1) WITH NOWAIT

 

 

 

Who Owns Your Databases And Jobs?

Quick question, who owns your databases? And how about your jobs? Don’t know? Go find out…now find out without using the GUI.

Querying sys.databases will give you the owner_sid. The trouble is that you have to relate that owner_sid back to a user. The same with sysjobs, that sid needs to relate to someone.

You could attempt joining against sys.server_principals, however if there’s a user that doesn’t exist or is a part of a domain group they wouldn’t show up and you would just get a NULL returned. Try the following example:

SELECT  d.NAME ,

        sp.NAME

FROM    sys.databases d

        LEFT JOIN sys.server_principals sp ON d.owner_sid = sp.sid;

        

SELECT  s.NAME ,

        sp.NAME

FROM    msdb.dbo.sysjobs s

        LEFT OUTER JOIN sys.server_principals sp ON s.owner_sid = sp.sid ;     

 

Now what, how can you quickly and easily get the owner? A little known system function will come to the rescue.

SUSER_SNAME (server_user_sid) will return the login name associated with a SID. Using this you can very quickly grab the information about who owns what without having to play with joins or get incomplete results:

SELECT  name ,

        SUSER_SNAME(owner_sid)

FROM    sys.databases;

 

SELECT  name ,

        SUSER_SNAME(owner_sid)

FROM    msdb.dbo.sysjobs;

 

There’s some great little hidden gems lying around in SQL Server like this. You just need to find them.

SQL2012 It’s The Small Things Pt3–End Of The Month Function

If you work with financial data or have to do regular reporting type functions then I’m sure you currently use some kind of funky function/case statement to figure out what the last day of the month is and something even more funky to find the last day of next month or the month after.

SQL Server 2012 fixes that for you with the introduction of the EOMONTH function.

It’s very simple to use:

SELECT EOMONTH('2012/03/16')

image

 

Even better, the EOMONTH function can accept an additional parameter where you can specify a number of months to add or remove to get relevant results:

SELECT EOMONTH('2012/03/16', -1)

image

 

It even handled the leap year correctly.

Simplicity itself. A great addition.