Capturing Extended Properties on Drop Table

TL;DR – Nope.

Extended Properties

Custom extended properties can be a useful tool when creating a self-documenting database. However, in an environment where tables are routinely dropped and recreated by software/systems not within your control, it may be desirable to have these stored so they can be repopulated when the table is recreated.

The obvious way to capture this data for this purpose would be to run a query in a DROP_TABLE trigger and store the output in a separate table for (re-)use later (eg on a create table trigger to repopulate the values as they were when the table is recreated).

USE Testing;

DROP TRIGGER IF EXISTS DropTableGetVars ON DATABASE;
IF OBJECT_ID ('TestDrop', 'U') IS NOT NULL EXEC ('DROP TABLE TestDrop');
GO

CREATE TABLE TestDrop (i INT);
GO

EXEC sp_addextendedproperty
    @level0type = 'SCHEMA',
    @level0name = 'dbo',
    @level1type = 'TABLE',
    @level1name = 'TestDrop',
    @name = 'Purpose',
    @value = 'Table for testing drop functionality';
GO

These first few lines to drop anything that we might be about to use, and to create a test table and add a custom extended property.

Now, a query to check the extended property is there by querying the sys.extended_properties view:

SELECT s.name, t.name, ep.name, ep.value
FROM
                sys.extended_properties AS ep
    INNER JOIN  sys.tables              AS t
                ON ep.major_id = t.object_id
    INNER JOIN  sys.schemas             AS s
                ON s.schema_id = t.schema_id;

And the results:

Drop Table Trigger

Now, a quick trigger to capture (well, show, in this case) the EPs when dropping the table:

CREATE TRIGGER DropTableGetVars
ON DATABASE
FOR DROP_TABLE
AS
    BEGIN
        PRINT 'Drop table trigger';
        SELECT s.name, t.name, ep.name, ep.value
        FROM
                        sys.extended_properties AS ep
            INNER JOIN  sys.tables              AS t
                        ON ep.major_id = t.object_id
            INNER JOIN  sys.schemas             AS s
                        ON s.schema_id = t.schema_id;
    END;
GO

A very simple trigger – print a message, and run that select statement. The idea being (later) to enhance that functionality by inserting the data into another table.

Anyway, let’s test it:

DROP TABLE TestDrop;
GO

The output:

Drop table trigger

(0 rows affected)

Completion time: 2021-03-06T12:18:39.2286808+00:00

And the results of the SELECT:

Nothing there. Bother. It’s already gone before we get here.

Conclusion

This is the wrong approach for what I need to do – the extended properties are apparently deleted before the drop table trigger is fired. Which is a bore, and means I have to do one (or more) of:

  • write triggers against the extended properties object itself (no)
  • rewrite the extended properties procedures (no)
  • wrap the extended properties maintenance SPs (depends on people using your maintenance SPs rather than the default / standard ones)
  • write a routine to periodically poll sys.extended_properties and store the results elsewhere (probably the most reliable, assuming these don’t change value regularly, but a faff.)

Posted in Uncategorized | Tagged , , , | 2 Comments

Shooting Yourself In The Foot While Testing Triggers, or The Importance of GO

No, not “Go” the game…

I was working on some functionality for a create table trigger. I had the whole creation / testing thing wrapped up in a single script for easy testing and possible deployment across multiple databases:

USE Testing

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE name = 'NewTableTrigger') 
EXEC ('CREATE TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE AS BEGIN SELECT 1 END')
GO

ALTER TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE
AS
BEGIN
	PRINT 'Create Table Trigger Fired'
END


IF EXISTS (SELECT * FROM sys.tables WHERE name = 'TestTable')
EXEC ('DROP TABLE TestTable');
GO

CREATE TABLE TestTable (i INT);

All well & good. (Obviously, the functionality in the trigger was a little more complicated than a print statement…)

Let’s test that script:

Create Table Trigger Fired

Completion time: 2021-02-27T14:17:09.4783931+00:00

Great. Refresh the tables list in the object explorer to go poking around, and:

Hmm. We have a problem.

Add a line to the script to see if anything’s going on there:

USE Testing

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE name = 'NewTableTrigger') 
EXEC ('CREATE TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE AS BEGIN SELECT 1 END')
GO

ALTER TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE
AS
BEGIN
	PRINT 'Create Table Trigger Fired'
END


IF EXISTS (SELECT * FROM sys.tables WHERE name = 'TestTable')
EXEC ('DROP TABLE TestTable');
GO

CREATE TABLE TestTable (i INT);

SELECT * FROM dbo.TestTable

And run…:

Create Table Trigger Fired
Msg 208, Level 16, State 1, Line 21
Invalid object name 'dbo.TestTable'.

Completion time: 2021-02-27T14:24:44.0301750+00:00

Not just me failing to see it, then.

After some step-by-step testing, I finally found the problem:

USE Testing

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE name = 'NewTableTrigger') 
EXEC ('CREATE TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE AS BEGIN SELECT 1 END')
GO

ALTER TRIGGER NewTableTrigger ON DATABASE FOR CREATE_TABLE
AS
BEGIN
	PRINT 'Create Table Trigger Fired'
END
GO

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'TestTable')
EXEC ('DROP TABLE TestTable');
GO

CREATE TABLE TestTable (i INT);

SELECT * FROM dbo.TestTable

Yup. That one “GO” missing at the end of the ALTER TRIGGER statement was causing the next part of the script to be executed as part of the same batch, meaning it was part of the trigger code, and the trigger was deleting the table that I was attempting to create.

Posted in Uncategorized | Tagged , | 1 Comment

Advent of Code and BULK INSERT problems

Have I said how much I hate the new WordPress “add post” page? No? Well, I do. Particularly when editing an earlier post…

Anyway. Where were we? Oh yes. Advent Of Code. For those of you who don’t know, it’s a problem-setting advent calendar where you have to solve the problems in order to progress. One problem with two parts per day. And the problem is usually accompanied by several hundred lines of input data, so you can’t do the problems by hand unless you’ve got way too much spare time.

I’ve tried this a few times over the years, and never managed to stick at it for very long, probably because, as a musician, this tends to be a busy time of year. 2020, though, is a bit different. No, a *lot* different. So I have more time to think about these things.

Loading data with BULK INSERT

Step one in solving these problems in SQL Server is loading the data into a database so you can do something with it.

I chose to use BULK INSERT, because, according to the documentation, it:

Imports a data file into a database table or view in a user-specified format in SQL Server

Ideal, right?

Day 1’s input is a list of numbers. So, in theory…:

IF OBJECT_ID('Day01Input', 'U') IS NOT NULL
 BEGIN
     EXEC ('drop table Day01Input');
 END;
 CREATE TABLE Day01Input
 (
     LineItemInput BIGINT
 );
 BULK INSERT dbo.Day01Input
 FROM 'C:\.....\AdventOfCode2020\Dec01-1 - input.txt';
 GO

Right?

Msg 4864, Level 16, State 1, Line 13
 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (LineItemInput).

Wrong.

OK, so let’s try with a varchar(max), in case there’s something weird going on in the file.

DROP TABLE dbo.Day01Input;
GO
CREATE TABLE Day01Input
(
    LineItemInput VARCHAR(MAX)
);

BULK INSERT dbo.Day01Input
FROM 'C:\.....\AdventOfCode2020\Dec01-1 - input.txt';
GO

Right?

(1 row affected)

Wrong. There’s 200 lines in this input. So what’s going on?

SELECT * FROM dbo.Day01Input;
LineItemInput
-----------------------------
 1619
 1919
...
 1623
 14
 (1 row affected)

mm’kay – what seems to have happened, then, is that the whole text file has been read into a single varchar(max) field. Which is fine, but not quite what I was hoping for. Fortunately, there are a few options you can apply with the BULK INSERT command, so this would seem to be an appropriate thing to try:

DROP TABLE dbo.Day01Input;
GO
CREATE TABLE Day01Input
(
    LineItemInput BIGINT
);

BULK INSERT dbo.Day01Input
FROM 'C:\.....\AdventOfCode2020\Dec01-1 - input.txt'
WITH
(
    FORMAT = 'CSV',
    DATAFILETYPE = 'char'
);

And…

Msg 4864, Level 16, State 1, Line 34
 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (LineItemInput).

Oh.

So how about that ROWTERMINATOR parameter?

BULK INSERT dbo.Day01Input
FROM 'C:\.....\AdventOfCode2020\Dec01-1 - input.txt'
WITH
(
    FORMAT = 'CSV',
    DATAFILETYPE = 'char',
    ROWTERMINATOR = '
'
);

And…

Msg 4864, Level 16, State 1, Line 51
 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (LineItemInput).

(ignore the line numbers – that’s my script of error generators…)

OK, so it doesn’t like the carriage return / linefeed combo. How’s about using CHAR(10)?

BULK INSERT dbo.Day01Input
FROM 'C:\.....\AdventOfCode2020\Dec01-1 - input.txt'
WITH
(
    FORMAT = 'CSV',
    DATAFILETYPE = 'char',
    ROWTERMINATOR = CHAR(10)
);

And…

Msg 102, Level 15, State 1, Line 48
 Incorrect syntax near 'CHAR'.

Oh look. A different error. Still, I suppose this is progress of a sort…

How about we wrap that up in a variable, and pass that in?

DECLARE @RowTerm VARCHAR(1) = CHAR(10);

BULK INSERT dbo.Day01Input
FROM 'C:\.....\AdventOfCode2020\Dec01-1 - input.txt'
WITH
(
    FORMAT = 'CSV',
    DATAFILETYPE = 'char',
    ROWTERMINATOR = @RowTerm
);

And…

Msg 102, Level 15, State 1, Line 70
 Incorrect syntax near '@RowTerm'.

What?

It turns out that every parameter in a BULK INSERT statement has to be a literal, not a variable, so the way to do this is way more complicated than first thought. The good news, though, is that we can ditch the rest of the WITH stuff, and just pass in the ROWTERMINATOR.

DECLARE @Bulk_cmd VARCHAR(MAX);
SET @Bulk_cmd
    = 'BULK INSERT AdventOfCode2020.dbo.Day01Input
FROM ''C:\.....\AdventOfCode2020\Dec01-1 - input.txt''
WITH (ROWTERMINATOR=''' + CHAR(10) + ''')';
EXEC (@Bulk_cmd);

And…

(200 rows affected)

Hurrah!

Maintaining Data Order

For the some of the AOC challenges, though, maintaining file / data order is important – so we’ll need to force that somehow. Obviously, SQL Server doesn’t do that in an easy way; if you put the data into a staging table like this one, and then insert it into a table with an IDENTITY (or SEQUENCE) field, you can’t necessarily guarantee that the data will make it in the right order.

The only way to guarantee order is to apply it as the data is loaded. I came across a tip somewhere to create a table with an identity field and a data field, and then a view on that table that just selects the data field, and then BULK INSERT into the view… So, for day three of AOC, I ended up with this:

IF OBJECT_ID('TobogganTreesInput', 'V') IS NOT NULL
    BEGIN
        EXEC ('DROP VIEW TobogganTreesInput');
    END;

IF OBJECT_ID('TobogganTrees', 'U') IS NOT NULL
    BEGIN
        EXEC ('DROP TABLE TobogganTrees');
    END;

CREATE TABLE TobogganTrees (RowID INT IDENTITY(1, 1), InputString VARCHAR(MAX));
GO


CREATE VIEW TobogganTreesInput
AS
SELECT  InputString FROM TobogganTrees;
GO


DECLARE @Bulk_cmd VARCHAR(MAX);

SET @Bulk_cmd
    = 'BULK INSERT AdventOfCode2020.dbo.TobogganTreesInput 
FROM ''C:\.....\Dec03-1 - input.txt''
WITH (ROWTERMINATOR=''' + CHAR(10) + ''')';

EXEC (@Bulk_cmd);
GO

You get the idea.

Posted in Uncategorized | Tagged , , | Leave a comment

CmdExec vs Powershell – error? What error?

Just a quickie Batch file scripts, called by SQL Agent, are not propagating their errors back up to the SQL Server job system because…. they’re being called from a Powershell script?

To replicate the problem, create a .bat file containing the following, being a quick way of generating an error response:

exit /b 1

Then create a SQLAgent job which calls this script as an “Operating System (CmdExec)” job step, and a second step of doing something dull, such as

select 1

Set the first job step to go to the second job step in the event of a failure. I’ve set up two steps, one is just the exit command above, the second calls that script instead using cmd /C

cmd /C "exittest.bat"

Launch the job. The job succeeds, but shows in history with a warning:

both options give an error / warning combo

So, how do we fix this? My suspicion is that we can’t, as powershell would be looking at the error generated / returned by cmd.exe, as that is (I’m guessing) what’s invoked when you launch a .bat file inside a powershell script. If you know otherwise, please let me know!

Posted in Uncategorized | Tagged , , , | Leave a comment

Testing SQL Agent Job Step Output During The Job

(This follows on from my previous script about emailing SQL Job ouput.)

There are times when a SQL server scheduled task needs to look at the output from the previous step, and make decisions based on that – particularly when parsing output from noisy/chatty scripts.

I have, therefore, put together a quick and dirty script to check the output from a SQL Server job step, and pull that into a string, so that it can be examined and a script can then make decisions about what to do next.

CREATE FUNCTION [dbo].[JobStepOutput]
(
    @StartDate INT,
    @StartTime INT,
    @job_name sysname,
    @step_id INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    /*
	FN: JobStepOutput
	Parameters:
			@StartDate int - msdb-formatted date at which the job starts
			@StartTime int - msdb-formatted time at which the job starts
			@job_name sysname
			@step_id int - step to look at.  If zero, then all.

	Usage:
			To be used in a SQL Agent job step:

			declare @StartDate int = CONVERT(int, $(ESCAPE_NONE(STRTDT)));
			declare @StartTime int = CONVERT(int, $(ESCAPE_NONE(STRTTM)));
			declare @job_name sysname = '$(ESCAPE_NONE(JOBNAME))';
			declare @StepID int = CONVERT(int, $(ESCAPE_NONE(STEPID))) - 1;

			select JobStepOutput(@StartDate, @StartTime, @job_name, @StepID);

*/

    DECLARE @StartDateTime DATETIME = msdb.dbo.agent_datetime(@StartDate, @StartTime);
    DECLARE @job_id UNIQUEIDENTIFIER =
            (
                SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @job_name
            );
    DECLARE @EndDateTime DATETIME =
            (
                SELECT ISNULL(MIN(msdb.dbo.agent_datetime(run_date, run_time)), GETDATE())
                FROM msdb.dbo.sysjobhistory
                WHERE msdb.dbo.agent_datetime(run_date, run_time) > @StartDateTime
                      AND job_id = @job_id
                      AND step_id = 0
            );

    DECLARE @results NVARCHAR(MAX) = N'';

    SELECT @results = STUFF(
    (
        SELECT message
        FROM msdb.dbo.sysjobs j
            INNER JOIN msdb.dbo.sysjobhistory jh
                ON j.job_id = jh.job_id
        WHERE 1 = 1
              AND msdb.dbo.agent_datetime(run_date, run_time) >= @StartDateTime
              AND msdb.dbo.agent_datetime(run_date, run_time) < @EndDateTime
              AND jh.job_id = @job_id
              AND
              (
                  (
                      jh.step_id = @step_id
                      AND @step_id >= 0
                  )
                  OR (@step_id = -1)
              )
        ORDER BY msdb.dbo.agent_datetime(run_date, run_time),
                 step_id
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'),
    1   ,
    0   ,
    ''
                           );

    RETURN (@results);

END;
GO

Some notes:

  • I’ve shown STEPID - 1 in the example; this gives the previous job step.
  • If you pass in -1, it’ll return all the job step output for this job
  • I’ve tweaked the core sysjobhistory query from the previous post; this now looks for the end date of the job so you can more easily refer to previous job runs as well. Probably not needed, but came in handy while testing.

In case WordPress does something weird, it’s over on my github repo as well.

Postscript

While putting this script together, I noticed (in SQL2016) a function called “fn_sqlagent_jobs”, and hoped it might be useful. It didn’t appear to do anything, so wasn’t. And so I ended up writing the above.

Posted in Uncategorized | Tagged , , , | 1 Comment

SQL Server Agent Job output to email

I came across a need for SQL Server Agent job output to be emailed out to the developer who created the code that the job was running. For various reasons, they don’t necessarily have access to the SQL Server itself – hurrah – but need to see what’s being generated by their scripts in case something goes wrong.

So, I ended up writing this extremely rough and ready SP:

USE master;
SET QUOTED_IDENTIFIER ON;
IF OBJECT_ID('SendJobOutput', 'P') IS NULL
    EXEC ('CREATE PROCEDURE SendJobOutput AS BEGIN SELECT 1 END');
GO
ALTER PROCEDURE SendJobOutput
    @StartDate INT,
    @StartTime INT,
    @job_name sysname,
    @recipients VARCHAR(MAX)
AS
BEGIN
    /*
    SP: SendJobOutput
    Parameters:
            @StartDate int - msdb-formatted date at which the job starts
            @StartTime int - msdb-formatted time at which the job starts
            @job_name sysname
            @recipients varchar(max) - string full of semi-colon-separated email addresses to get the output message
    Usage:
                To be used in a SQL Agent job step:
            declare @StartDate int = CONVERT(int, $(ESCAPE_NONE(STRTDT)));
            declare @StartTime int = CONVERT(int, $(ESCAPE_NONE(STRTTM)));
            declare @job_name sysname = '$(ESCAPE_NONE(JOBNAME))';
            declare @recipients varchar(max) = 'user@address.bar; someoneelse@foo.com';

            exec SendJobOutput @StartDate, @StartTime, @job_name, @recipients
*/
    DECLARE @StartDateTime DATETIME = msdb.dbo.agent_datetime(@StartDate, @StartTime);
    DECLARE @Subject sysname = N'Job output - ' + @job_name;
    DECLARE @job_id UNIQUEIDENTIFIER =
            (
                SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @job_name
            );
    DECLARE @message NVARCHAR(MAX)
        = N'<html><body><table><tr><th>JobName</th><th>DateTime</th><th>Step Name</th><th>Message</th></tr>';
    DECLARE @results NVARCHAR(MAX) = N'';
    SELECT @results = @results + STUFF(
                                 (
                                     SELECT j.name AS TD,
                                            '',
                                            msdb.dbo.agent_datetime(run_date, run_time) AS TD,
                                            '',
                                            jh.step_name AS TD,
                                            '',
                                            message AS TD
                                     FROM msdb.dbo.sysjobs j
                                         INNER JOIN msdb.dbo.sysjobhistory jh
                                             ON j.job_id = jh.job_id
                                     WHERE 1 = 1
                                           AND msdb.dbo.agent_datetime(run_date, run_time) >= @StartDateTime
                                           AND jh.job_id = @job_id
                                     ORDER BY msdb.dbo.agent_datetime(run_date, run_time),
                                              step_id
                                     FOR XML PATH('TR'), ELEMENTS
                                 ),
                                 1,
                                 0,
                                 ''
                                      );
    SELECT @message = @message + @results + N'</table></body></html>';
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DoNotReply - SQLMail', -- name of email profile already defined in msdb.dbo.sysmail_profile
                                 @recipients = @recipients,
                                 @subject = @Subject,
                                 @body_format = 'HTML',
                                 @body = @message;
END;

At some point, I’ll revisit it to smarten up the email message being generated, but this at least is usable.

Instructions for use are in the header block, but, spoiler alert, it involves adding an extra step at the end of a job, and pasting in a few lines of script which makes use of the SQLAgent job step macros/tokens.

Note the hack to get multiple “TD” elements – if those extra ''s aren’t selected, the whole things gets blended into a single which is not right.

You’ll need to configure the email profile being used.

And, in case WordPress corrupts the script above (as if…), it’s over on my github page too as “SendJobOutputByEmail.sql”

Posted in Uncategorized | Tagged , , , | 2 Comments

Waiting for a job to complete

You know how it is. You need to wait for a job to complete before moving onto the next one… And, just sometimes, you need to do the same with SQL Server.

MSDB

The msdb system database contains various tables for dealing with the SQL Agent and the job scheduler. The ones of particular interest for this problem are:

syssessions
The syssessions table contains a list of the SQLAgent sessions. Unfortunately, there’s no absolute guarantee that the session_id is in ascending order, so we need to pull the top value when sorted by agent_start_date in descending order.
sysjobs
The sysjobs table contains information about each scheduled (and unscheduled) job. We’re only interested in the job_id and job_name fields at the moment.
sysjobactivity
The sysjobactivity table contains data about what jobs are running in the current session and, indeed, can be used as a snapshot of what jobs were running when a previous SQLAgent instance was ended.

Final code

Here follows a quick & dirty query to watch for one job to finish, and then to start the next job:

USE msdb;

DECLARE @JobToWaitFor sysname = 'First Job';
DECLARE @JobToLaunchNext sysname = 'Second Job';

WHILE EXISTS
      (
          SELECT j.name,
                 ja.*
          FROM sysjobs j
              INNER JOIN sysjobactivity ja
                  ON ja.job_id = j.job_id
          WHERE ja.session_id =
          (
              -- can't use max session_id, as not guaranteed to be the most recent agent start date
              SELECT TOP 1 session_id FROM syssessions ORDER BY agent_start_date DESC
          )
                AND ja.start_execution_date IS NOT NULL
                AND ja.stop_execution_date IS NULL
                AND j.name = @JobToWaitFor
      )
BEGIN
    -- delay loop here
    WAITFOR DELAY '00:00:10'; -- tweak this as appropriate to your job length, or desired delay between job finish/start
END;
EXEC sp_start_job @job_name = @JobToLaunchNext;

Yes, there are more elegant / succinct ways to do this – you could achieve similar results by using sp_help_job for example – but using script snippets like this can allow a more complex job workflow than merely “start this job, run this script” – in the past, I’ve written similar code to launch several jobs to run in parallel and then wait for them all to finish before moving onto the next step.

Posted in Uncategorized | Tagged , , , | 3 Comments

Nested Virtual / Remote Desktops and CTRL+ALT+DELETE

Just a quickie…

You need to change your password, so you use Ctrl+ALT+Delete. That’s fine, as long as you’re physically at the machine.

But if you’re connected to a remote desktop session somewhere, that key combo doesn’t get through. But you can send Ctrl+ALT+Insert to do the same thing.

What if you’re on a remote server and connected through that to another remote server (with a different set of credentials) and you need to send Ctrl+ALT+Del to that? Well, Ctrl+Alt+Del will do your local machine, Ctrl+ALT+Insert will do the top level Virtual machine… Today I learned about Ctrl+ALT+End – that’ll do the one that you’re actually looking at. Hurrah!

Ctrl+Alt+End. I know all this VDI-inside-a-VDI is all a bit Inception-y, but it does the job. Now, if only I could figure out the password complexity rules…

Posted in Uncategorized | Tagged , , , , | Leave a comment

Login Script Generation For Migration or DR

I needed a script to create logins on a second server to match the primary. There are reasons why using, say, the powershell dba tool set isn’t an option (eg the server isn’t built, or the two can’t communicate, or your security team doesn’t allow for the use of this sort of thing), so let’s have a look at what we can do just in T-SQL.

There are three things to look at:

  1. Creating Logins from Windows accounts
  2. Creating logins that are authenticated by SQL Server
  3. Assigning membership of the appropriate server roles

Note that we’re just creating a snapshot of what’s there – we’re not aiming to keep these things in true synchronisation between servers. Note also that this script will only create accounts that don’t exist. If there’s an account already there with the same name, the script generated will not recreate it with new options.

sys.syslogins, sys.server_principals, sys.sql_logins

There are a couple of ways of getting at login information:

sys.syslogins
This is the old way. Try not to use it any more. The sys.syslogins documentation says it will be removed in a future version of Microsoft SQL Server.
sys.server_principals and sys.sql_logins
These are the new(ish) DMVs to use – sys.server_principals contains the bulk of the information needed, and sys.sql_logins one contains a few extra bits that are specific only to SQL Server authentication (passwords, policy flags, etc), but the bulk is inherited from sys.server_principals

Scripting Windows Login Accounts

This is probably the easiest script to write, as it just references sys.server_principals. I’ve written a simple “IF NOT EXISTS” wrapper around the whole thing, to make sure we don’t try to create an account that’s already there.

SELECT  N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(name, '''')
        + N') BEGIN CREATE LOGIN ' + QUOTENAME(name) + N' FROM WINDOWS WITH DEFAULT_LANGUAGE = '
        + default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) + N' END;' AS SQLQuery
FROM    sys.server_principals
WHERE
        type IN ('U', 'G')
        AND name NOT LIKE 'NT %';

Note that we’re excluding the NT service accounts / default accounts, as there’s no point in generating those, as they’ll already be there on the target anyway.

Scripting SQL Authenticated Accounts

Really? We still have these? Unfortunately, yes – and I can’t see them going away any time soon, unfortunately. So, here we are.

As above, I’m generating the “IF NOT EXISTS” wrapper. This time, we’re querying sys.sql_logins, as it contains only those server principals, and the extra (password / policy) information required by these accounts.

SELECT  N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(sl.name, '''')
        + N') BEGIN CREATE LOGIN ' + QUOTENAME(sl.name) + N' WITH PASSWORD = '
        + CAST(CONVERT(VARCHAR(256), CAST(LOGINPROPERTY(sl.name, 'PasswordHash') AS VARBINARY(256)), 1) AS NVARCHAR(MAX))
        + ' HASHED' + N', SID=' + CONVERT(VARCHAR(256), sl.sid, 1) + N', DEFAULT_LANGUAGE = '
        + sl.default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(sl.default_database_name)
        + N', CHECK_EXPIRATION = ' + CASE sl.is_expiration_checked
                                         WHEN 1 THEN
                                             N'ON'
                                     ELSE
                                         N'OFF'
                                     END
        + N', CHECK_POLICY = ' + CASE sl.is_policy_checked
                                     WHEN 1 THEN
                                         N'ON'
                                 ELSE
                                     N'OFF'
                                 END
        + N'; END;' AS SQLQuery
FROM    sys.sql_logins sl
WHERE
        sl.name NOT LIKE '##%'
        AND sl.name NOT LIKE 'sa';

Server Role Memberships

This is the final step – assigning role memberships. For this, we need to use the sys.server_role_members DMV as well as sys.server_principals (twice, once for the role name, once for the member name). That’s the big CTE before the main SELECT statement.

WITH
RoleMembers AS
(
    SELECT          srm.role_principal_id,
                    srm.member_principal_id,
                    r.name AS RoleName,
                    m.name AS MemberName
    FROM
                    sys.server_role_members srm
        INNER JOIN  sys.server_principals   r
                    ON srm.role_principal_id = r.principal_id
        INNER JOIN  sys.server_principals   m
                    ON srm.member_principal_id = m.principal_id
)
SELECT  N'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(MemberName, '''')
        + N') BEGIN ALTER SERVER ROLE ' + RoleName + N' ADD MEMBER ' + +QUOTENAME(MemberName) + N'; END;' AS SQLQuery
FROM    RoleMembers
WHERE
        RoleMembers.member_principal_id IN
        (
            SELECT  principal_id
            FROM    sys.server_principals
            WHERE
                    type IN ('U', 'S')
                    AND name NOT LIKE 'NT %'
                    AND name NOT LIKE 'sa'
                    AND name NOT LIKE '##%'
        );

Wrapping this up

I’ve put all this functionality into a single view that can be queried to return the statements in the order above – logins before roles – so that it can be easily used to generate everything you need. The View definition script is below. Note the extra field (“Ord”) which we won’t query later, but will use to sort the results.

USE [master];
GO

/****** Object:  View [dbo].[ExportLoginQueries]    Script Date: 24/03/2020 16:00:58 ******/
SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID('dbo.ExportLoginQueries', 'V') IS NULL
BEGIN
    EXEC ('CREATE VIEW dbo.ExportLoginQueries AS SELECT 1 AS [one]');
END;
GO

ALTER VIEW [dbo].[ExportLoginQueries]
AS
    WITH
    RoleMembers AS
    (
        SELECT          srm.*,
                        r.name AS RoleName,
                        m.name AS MemberName
        FROM
                        sys.server_role_members srm
            INNER JOIN  sys.server_principals   r
                        ON srm.role_principal_id = r.principal_id
            INNER JOIN  sys.server_principals   m
                        ON srm.member_principal_id = m.principal_id
    )
    SELECT  3                                                                                                 AS Ord,
            N'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(MemberName, '''')
            + N') BEGIN ALTER SERVER ROLE ' + RoleName + N' ADD MEMBER ' + +QUOTENAME(MemberName) + N'; END;' AS SQLQuery
    FROM    RoleMembers
    WHERE
            RoleMembers.member_principal_id IN
            (
                SELECT  principal_id
                FROM    sys.server_principals
                WHERE
                        type IN ('U', 'S')
                        AND name NOT LIKE 'NT %'
                        AND name NOT LIKE 'sa'
                        AND name NOT LIKE '##%'
            )
    UNION ALL
    SELECT  1                                                                                                AS Ord,
            N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(name, '''')
            + N') BEGIN CREATE LOGIN ' + QUOTENAME(name) + N' FROM WINDOWS WITH DEFAULT_LANGUAGE = '
            + default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) + N' END;' AS SQLQuery
    FROM    sys.server_principals
    WHERE
            type = 'U'
            AND name NOT LIKE 'NT %'
    UNION ALL
    SELECT          2           AS Ord,
                    N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(sl.name, '''')
                    + N') BEGIN CREATE LOGIN ' + QUOTENAME(sl.name) + N' WITH PASSWORD = '
                    + CAST(CONVERT(VARCHAR(256), CAST(LOGINPROPERTY(sl.name, 'PasswordHash') AS VARBINARY(256)), 1) AS NVARCHAR(MAX))
                    + ' HASHED' + N', SID=' + CONVERT(VARCHAR(256), sl.sid, 1) + N', DEFAULT_LANGUAGE = '
                    + sl.default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(sl.default_database_name)
                    + N', CHECK_EXPIRATION = ' + CASE sl.is_expiration_checked
                                                     WHEN 1 THEN
                                                         N'ON'
                                                 ELSE
                                                     N'OFF'
                                                 END 
                    + N', CHECK_POLICY = ' + CASE sl.is_policy_checked
                                                 WHEN 1 THEN
                                                     N'ON'
                                             ELSE
                                                 N'OFF'
                                             END 
                    + N'; END;' AS SQLQuery
    FROM
                    sys.sql_logins        sl
    WHERE
                    sl.name NOT LIKE '##%'
                    AND sl.name <> 'sa';
GO

All that needs to be done now is to write something to extract this as & when required. For example, using bcp:

bcp "SELECT SQLQuery FROM dbo.ExportLoginQueries ORDER BY Ord, SQLQuery" queryout "<<PathToBackupDirectory>>\ExportedSQLLogins.sql" -t -c -S <<servername>> -T
Posted in Uncategorized | Tagged , , | 4 Comments

Departure of a SQL Server Administrator – What to Check?

A SQL Server administrator has left. But what does that actually mean? What do we need to check to make sure that things will continue to work after their Active Directory account is disabled?

Some things to check are:

  • SQL Database items
    • Database ownership
    • SQL Scheduled Task ownership
    • Server role membership
    • Database role membership
    • object ownership
  • Windows items
    • Windows Scheduled Tasks
    • Ownership / login ID for Windows Services
    • Local Group Membership
    • File Share Ownership

A few things to note:

  • This collection of script merely identifies issues
  • Don’t blindly implement fixes without proper testing and without a rollback – you may run into trouble
  • This collection is not complete

SQL Server

Database ownership

Ideally, databases would be owned by some low-level non-sa account which is disabled, and solely exists to avoid these problems. This is not an ideal world, so we need to check for databases owned by our departing administrator:

DECLARE @LeaverPrincipalName sysname = 'YourCorp\Hugh.Splitter';
SELECT  name,
        state_desc
FROM    sys.databases
WHERE   owner_sid =
(
    SELECT  TOP (1) sid FROM sys.server_principals WHERE name = @LeaverPrincipalName ORDER BY   name
);

Transferring database ownership should be relatively straight-forward and risk-free. Except there are certain applications that depend on the database being owned by a sysadmin, or by a particular account (sa), or by their own service account, rather than checking for membership of the db_owner role… So make sure you test your changes. You’ll need to use the ALTER AUTHORIZATION statement, as follows:

ALTER AUTHORIZATION ON DATABASE::<<DBName>> TO <<NewUserName>>;

SQL Scheduled Task Ownership

When a SQL Server agent job runs a T-SQL job step, that step is executed as the owner, rather than as the SQL Agent account or the sa account. From the documentation linked just above:

Transact-SQL job steps use the EXECUTE AS command to set the security context to the owner of the Job.

So, we need to make sure that our leaver’s account isn’t the owner of any SQL Agent jobs:

SELECT  name
FROM    msdb.dbo.sysjobs
WHERE   owner_sid =
(
    SELECT  TOP (1) sid FROM sys.server_principals WHERE name = @LeaverPrincipalName ORDER BY   name
);

Which is all well and good, except there is the possibility for it to be set to use a proxy account, so we’ll need a more complex query:

SELECT              JobName   = j.name,
                    js.step_id,
                    js.step_name,
                    JobOwner  = SUSER_SNAME(owner_sid),
                    ProxyName = p.name
FROM
                    msdb.dbo.sysjobsteps js
    INNER JOIN      msdb.dbo.sysjobs     j
                    ON js.job_id = j.job_id
    LEFT OUTER JOIN msdb.dbo.sysproxies  AS p
                    ON js.proxy_id = p.proxy_id
WHERE
                    SUSER_SNAME(owner_sid) = @LeaverPrincipalName
                    OR  p.name = @LeaverPrincipalName
ORDER BY
                    j.name,
                    step_id;

There’s a handy-looking sp for migrating job ownership en masse – sp_manage_jobs_by_login.

Server Role Membership

What server roles did the user have? This might be helpful when replicating the user’s permissions when transferring job roles to another user – except this should all be done through active directory group membership, amirite?

Assuming, though, that you’re working somewhere where granting permissions to individuals is the done thing, then you’ll want to check the results of the following:

SELECT          sprole.name   AS [SQL Server Role],
                spmember.name AS [Member Name]
FROM
                sys.server_role_members srm
    INNER JOIN  sys.server_principals   sprole
                ON sprole.principal_id = srm.role_principal_id
    INNER JOIN  sys.server_principals   spmember
                ON spmember.principal_id = srm.member_principal_id
WHERE           spmember.name = @LeaverPrincipalName;

Database Role Membership

Similarly, you may need/want to check for membership of database roles across all databases on your server:

IF OBJECT_ID('tempdb.dbo.#DBRoleMembers', 'U') IS NOT NULL
BEGIN
    DROP TABLE #DBRoleMembers;
END;

CREATE TABLE #DBRoleMembers (DB sysname, DBRoleName sysname, MemberName sysname);

SELECT  @sql
    = N'
         USE [?];
         
         INSERT INTO #DBRoleMembers
         SELECT DB_NAME(),
                dprole.name,
                dpmember.name
         FROM sys.database_role_members drm
             inner join sys.database_principals dprole
                 ON dprole.principal_id = drm.role_principal_id
             INNER JOIN sys.database_principals dpmember
                 ON dpmember.principal_id = drm.member_principal_id
         WHERE dpmember.name = ' + QUOTENAME(@LeaverPrincipalName, '''');

DECLARE @sqlouter NVARCHAR(MAX) = N'master.sys.sp_msforeachdb @command1';
DECLARE @sqlparams NVARCHAR(MAX) = N'@command1 nvarchar(max)';

EXEC sp_executesql @sqlouter, @sqlparams, @sql;

SELECT  * FROM  #DBRoleMembers ORDER BY DB, DBRoleName, MemberName;

Object Ownership

It might be worth checking for ownership of individual objects within a database, as you won’t be able to delete the user from the database until ownership of objects has been transferred to another user.

SELECT  @sql
    = N'
CREATE TABLE #ownedobjects (DBName sysname, ObjName sysname, ObjType VARCHAR(60));
exec sp_msforeachdb ''INSERT INTO #ownedobjects SELECT ''''?'''', name, type_desc FROM [?].[sys].[objects] WHERE principal_id = (SELECT sid FROM sys.server_principals WHERE name = '''
      + QUOTENAME(@LeaverPrincipalName, '''') + N''')''
select * from #ownedobjects';

--SELECT  @sql;
EXEC (@sql);

As with databases, use the ALTER AUTHORIZATION statement to fix this.

Windows

For the Windows-based things, we’re going to drop into PowerShell

Windows Scheduled Tasks

Windows Scheduled tasks will fail when the owner account is disabled / deleted. Or, indeed, the password is changed or expired – but you don’t run jobs as a user account, do you? Oh, you do?

$username = 'YourCorp\Hugh.Splitter'
 
Write-Host "Checking ownership of Windows Scheduled Tasks"
get-scheduledtask | where-object {$_.Author -eq $username}

Oops. You might want to check those.

Windows Service Login

And, of course, you don’t use user accounts to run windows services, do you? Let’s check.

Write-Host "Checking Login for Windows Services" 
get-wmiobject win32_service  | where-object {$_.StartName -eq $username } 

Local Group Membership

It might also be worth checking to see which groups the user is in on a particular server (we’ll leave the active directory stuff to the AD team)

Write-Host "Checking Local Group Membership"
 
get-localgroup | foreach-object -process { 
if ( $(get-localgroupmember $_ | where-object {$_.name -eq $username} ) -ne $null )
{
write-host "User $username is a member of $_.name group"
}
}

File Share Ownership

What about file shares? Does your leaver own any of those? I’m not sure if it makes a difference, but it’s worth identifying these in case anyone needs to change the permissions later…

write-host "Checking File Share Ownership"
(get-smbshare | where-object{$_.Path -ne $null -and $_.Path -ne ""} | get-acl | where-object {$_.Owner -eq $username}).PSPath | Convert-Path

Just to unpick this one a little – the first filter is to remove items that don’t have a path, such as the IPC$ share; and the Convert-Path is there to transform the PSPath value of, say, Microsoft.PowerShell.Core\FileSystem::D:\Backups to a more normal-looking D:\Backups

Still to do

  • I should probably think about file ownership as well – in case anything depends on that.
  • And maybe running this all into a single script that can run against all servers / a list of servers

The big question, though – what else needs to be checked?

Finally

Do you have the passwords / know where the passwords are stored and have access to that system?

Posted in Uncategorized | Tagged , , , , , | 1 Comment