A Lack of Focus

Unfortunately we had to go into lockdown again this week, missing out on a few social events. Well, I’m thankful that it’s not too bad, though the constant construction going on outside my window is getting on my nerves a little.

This was my first week trying Microsoft’s suggestion for “Focus Time” where it automatically blocks out sections of your calendar to be undisturbed. I failed at following the suggestion, but it is nice to have scheduled do-not-disturb time.

I spent a bit of time working on this finance project to move some reinsurance processes out of a big Excel spreadsheet to a SQL database. It is confusing but it’s a good challenge. Other than that, it’s been fixing git merge issues in my team, deploying random changes to live, and trying to keep our nightly ETL humming along. Some of those processes do not belong there, but it’s hard for us to take them out now. Well, that’s what my manager says. I think we probably could push back more but our team is rather resigned to our fate at the moment. We look to the future when key marketing campaigns aren’t driven by the warehouse, but until then…

Since I haven’t shown actual code for a while, here’s something that has been useful for auditing permissions on a database, something I’ve been doing a bit recently. This snipped will find all of the times a permissions has been GRANT’d or DENY’d on a given database, and then which users are in all of the roles. The database name is part of a template - press CTRL+SHIFT+M and type in the database name you want to check:

USE <database_name, sysname, >
GO

--First, find all of the GRANT and DENY
SELECT		P.class_desc AS Permission_Target_Type,
			CASE P.class	WHEN 0	THEN DB_NAME ()
							WHEN 1	THEN OBJECT_NAME (P.major_id)
							WHEN 3	THEN SCHEMA_NAME (P.major_id)
			END AS Permission_Target_Name,
			O.type_desc AS Permission_Target_Object_Type,
			USER_NAME( P.grantee_principal_id ) AS [User_or_Role_with_Permission],
			PR.type_desc AS [User_or_Role],
			P.state_desc AS Grant_or_Deny,
			P.[permission_name] AS [Permission_Name]
FROM		sys.database_permissions AS P
				INNER JOIN	sys.database_principals AS PR
					ON			PR.principal_id = P.grantee_principal_id
				LEFT JOIN	sys.objects AS O
					ON			O.object_id = P.major_id
WHERE		USER_NAME( P.grantee_principal_id ) <> 'public'
ORDER BY	[User_or_Role],
			Permission_Target_Name

--Now find all users in each role
SELECT		R.name AS role_name,
			U.name AS role_member
FROM		SYS.database_role_members AS DRM
			INNER JOIN	SYS.database_principals AS R
				ON			R.principal_id = DRM.role_principal_id
			INNER JOIN	SYS.database_principals AS U
				ON			U.principal_id = DRM.member_principal_id
ORDER BY	role_name,
			role_member

This helps me see what’s already been done so I can neaten it up. I think it’s much better to have each user get permissions from their role, rather than adding a GRANT EXECUTE to that user. It’s a little too hidden for my taste.