Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

Why Are There So Many DBAs?

How many SQL Server DBAs are there in your organization? No, not the ones that are DBAs according to their HR title. And not the ones that are DBAs by reputation. How many have the authority to perform any activity on your production SQL Server instances? If you don't already know the answer, there may be far more than you realize. Let's make a list, shall we? Start with this query: 


SELECT p.name, p.type, p.type_desc
FROM master.sys.server_principals p
WHERE p.type IN ('S', 'U', 'G')
AND IS_SRVROLEMEMBER('SYSADMIN', p.name) = 1
ORDER BY p.type



SQL Server SYSADMIN Fixed Server Role

Windows Logins

The windows logins (type-U) hold few surprises. The persons behind each login should be easy to identify. Whoever they may be, they're all DBAs. Add them to the list.


SQL Logins

The SQL logins (type-S) are trickier. The persons behind each login may be difficult (or impossible) to identify. Where are the passwords for those logins stored? In a password management app? In a spreadsheet? In a .Net config file? On a Post-it® note on someone's monitor? Whoever has access to any of those passwords goes on the list. They're all DBAs too.


Windows Groups

Everyone that's a member of any of the Windows user groups (type-G) is a DBA. Add them to the list. (Under "normal" circumstances, IS_SRVROLEMEMBER will identify a member of a fixed server role through membership in a Windows group. They should be listed in the query output as type-U Windows logins. Be sure to check out MSDN documentation -- and watch out for nested Windows groups.) What's that? You don't maintain Active Directory? That might be a headache for you. Anyone that gets added to any of those groups later on becomes a DBA. Wunderbar!


Ok, we're done. Let's review: everyone on your list holds the keys to your castle. Can you trust all of them?

Comments

Post comment