Two weeks in a row. What problem have I been trying to solve at Youi this week?

What I’ve been doing

I’ll try to focus on my profession this time. The week has been a little quiet. There are some big projects on the horizon, but many of them require us to wait until more IT development has been done. Until we know what we are needed for, we can only prepare theoretically.

A fair bit of my time has been on this staff organisation update in the Warehouse. The hierarchy in our core system is rather out of date, and after our big reshuffle twelve months ago, most areas were left as they were so we didn’t need to worry about breaking permissions people had and work allocations. So that at least our BI reports are accurate, I’ve been working with others to organise all the staff into the new areas in our main staff table in our SQL warehouse. That’s been fun. We’re mostly there, but some departments in support are very messy - I have a very large CASE WHEN statement in the nightly build of that staff table.

The other thing that’s kind of important I’ve been doing is a security audit - on our two main SQL servers, across all fifty plus databases, which users have db_owner permission? Many of them probably shouldn’t. Better to give the level that’s required and no more, such as ddl_admin or data_reader, but we’ve been lazy in the past. I’m hoping that I can get consent from the responsible party of each database so we all agree who should have what access. This is the dynamic SQL query I use to find all db_owner users (ignoring the dbo user):

SELECT  S.name,
        S.dbname
FROM    sys.syslogins AS S
WHERE   S.sysadmin = 1

DROP TABLE IF EXISTS #DB_OWNERS

CREATE TABLE #DB_OWNERS (
    [DB_NAME]    sysname NOT NULL,
    DB_USER_NAME sysname NOT NULL,
    CONSTRAINT PK_#DB_OWNERS PRIMARY KEY CLUSTERED( [DB_NAME], DB_USER_NAME )
)

DECLARE @SQLtoRun NVARCHAR(MAX) = N''

SELECT  @SQLtoRun = @SQLtoRun + N'
INSERT INTO #DB_OWNERS ( [DB_NAME], DB_USER_NAME )
SELECT '''          + name + N''',
U.name
FROM '              + name + N'.SYS.database_principals AS DP
INNER JOIN '        + name + N'.SYS.database_role_members AS DRM
ON DRM.role_principal_id = DP.principal_id
INNER JOIN '        + name
                    + N'.SYS.database_principals AS U
ON U.principal_id = DRM.member_principal_id
WHERE DP.name = ''db_owner''
AND U.name <> ''dbo''
'
FROM    master.sys.DATABASES
WHERE   database_id > 4
AND     name NOT LIKE '%_SHADOW'

EXEC sys.sp_executesql @SQLtoRun

SELECT  DO.DB_NAME,
        DO.DB_USER_NAME
FROM    #DB_OWNERS AS DO

One of my pet peeves is our extensive use of global temp tables to move data from one stored procedure to another. It’s fine in theory, but as soon as you have that procedure being called multiple times, it falls apart. We regularly get errors for “The table ##XXX does not exist or you do not have permission” because another session dropped it before the first session could read their version. After some reading of Erland Sommarskog, I thought it would be worth trying to replace the worst offenders with process-keyed tables. Basically, persistent table with the same schema as the old global temp table, plus a column for SPID and inserted datetime. The clustered index is on SPID first, so when a procedure is called, it can insert into that table, and the calling procedure can get that data via the SPID, and the lookup should be quick. Obviously, it’s a lot of work to replace all of them, but I can work on it slowly as I get those errors.

Other things that are basically stocking fillers:

  • Using some code I found on Google to try to find indexes that aren’t being used, or potential indexes that would help existing queries a lot.
  • We started getting some “memory grants outstanding” warnings from our second SQL server, so I’ve been examining some of the procedures there to try to find what’s causing it. One of them seemed to be because of a custom-build split-a-string-by-a-delimiter function, so hopefully replacing it with STRING_SPLIT solves that.
  • Actioning tickets from Cherwell to change the logic slightly for some nightly procedures, and adding new steps for new tables to build overnight.
  • Playing video games during my lunch break when I’m working at home - currently going through Metroid: Zero Mission
  • Thinking about future potential projects like Master Data Management and Data Governance. Trying to think of next steps.
  • One of our BI analysts is working on a new master “analytic data set” for “gold-standard” reports. He had some ideas about our suburbs table that we talked over. I’ll probably give him a view with the extra rows he wants so he can avoid NULL values.

So, nothing ground breaking, and not exactly career advancing either. Sometimes, work is a bit like that. Still, I need to focus more on the positive and looking for opportunities to grow.