And if you see SID, tell him...

Probably a bit of an edge case this one, but a strange set of events cumulating in a load of lost access, angry Application Support folks and head scratching for a while...

...and the solution turned out to be simple and easily avoidable...always the way eh?

OK, we know that Logins/Users in SQL server (and Active Directory Users and groups) have an associated Security Identifier (SID) hidden away in their back pocket to guarentee uniqueness. With the key words there being uniqueness, and its an identifier.... wait a minute, don't we have a datatype to handle such requirements?

Yarp, there it is..... uniqueidentifer.

The case I was looking into was following a deployment via a CI/CD pipeline for a specific SSIS project upon a shared server. In order to enforce the pipeline contents, permissions are reset to that what are defined for the project as a whole being deployed (so Proxy accounts logins, SSIS folders, Environments, the SSIS project itself and so on). Pipeline deployment complete, all good, no errors, what's all the fuss about?

Great, but then a lot of other App Support teams started reporting that their jobs (that used Proxies) suddently started failing.

Queue the German map operator lady in Indiana Jones and the Last Crusade....

ALARM!!!!

So, what in Lord James' name happened?

As part of the security tidy up, the proxy permission is removed as well as the projects etc... And SSISDB can be a little picky on such things, I don't know if some of you have ever seen the below when attempting to remove users from SSISDB.

The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped.
The transaction ended in the trigger. The batch has been aborted. (Microsoft SQL Server, Error: 27226)

This is often due to data remaining in one of the below SSISDB tables , and the general workaround is to have to poke in and remove them. Ouch.

• internal.folder_permissions

• internal.project_permissions

• internal.environment_permissions

• internal.operation_permissions

So the deployment scripts involved were looking for such entries to handle this particular issue. The above tables only contain the sid of the principals that had been granted, not the name itself. No problem, quick skip over to sys.database_principals to get what we need and all is good.

declare @sid uniqueidentifier = (select sid from sys.database_principals where name = @user_name)
 
                DELETE from internal.operation_permissions where sid = @sid or sid is null
                DELETE from internal.folder_permissions where sid = @sid or sid is null
                DELETE from internal.project_permissions where sid = @sid or sid is null
                DELETE from internal.environment_permissions where sid = @sid or sid is null
                EXECUTE ('DROP USER [' + @user_name + ']')

But it wasn't. Look closely at the above, and lets try something along those lines, with a taylored variable to use for our uniquely idenfied row.

declare @sidu uniqueidentifier = (select sid from sys.database_principals where name = 'SidneyJames') 
SELECT distinct(sid) FROM internal.operation_permissions where sid = @sidu or sid is null

This returns all of the sids below... Wait, wtf.....?

0x0105000000000005150000007A167E0375325F616552C62F24D70000
0x0105000000000005150000007A167E0375325F616552C62F786B0100
0x0105000000000005150000007A167E0375325F616552C62FA2D00100
0x0105000000000005150000007A167E0375325F616552C62F26D70000
0x0105000000000005150000007A167E0375325F616552C62FF5E20100
0x0105000000000005150000007A167E0375325F616552C62F2D170100
0x0105000000000005150000007A167E0375325F616552C62F86270100
0x0105000000000005150000007A167E0375325F616552C62F1C170100
0x0105000000000005150000007A167E0375325F616552C62FCEDD0100
0x0105000000000005150000007A167E0375325F616552C62F22D70000
0x0105000000000005150000007A167E0375325F616552C62F6DCC0100
0x0105000000000005150000007A167E0375325F616552C62F191B0100

So with our delete code above, it removes all of those permission lines for all of those other sids....there's our sniper.

Lets check sys.database_principals... just what is that sid column?

Oh dear.... its not our uniqueidentifer datatype.

So what does our script return if it does?

declare @sid varbinary(85) = (select sid from sys.database_principals where name = 'SidneyJames') 
SELECT distinct(sid) FROM internal.operation_permissions where sid = @sid or sid is null

Aha, just the one...magic!

0x0105000000000005150000007A167E0375325F616552C62FF5E20100

Alright, what's going on here then?

Essentially, the uniqueidenfier data has a length of 36 characters, and a varbinary(85), well doesn't. The first script is silently truncating the length of the sid value from sys.database_principals when populating the variable, and then Implicitly converting the values returned from sys.database_principals to a uniqueidentifier, truncating these in the process too, and finding all the other sids. Darn.

Looks like a sid, smells like a sid, put certainly isn't a sid. No matter how Visious it is.

The moral of the overlong story? Always double check your datatypes as we know what Assumption is the mother of. (If you don't know, Google it...just not at work eh?).

Carry on... *yak *yak *yak*

Rod