Security-Check-Script & Survey:
SQL Server Security – Database-Owners, critical Permissions and role membership
In this survey, I would like to explore in a greater radius which accounts are typically used as database owners. I will subsequently publish the cumulated results here to share them with the community together with some recommendations for hardening security.
In this instance, particular server-wide permissions both of the used account as well as, in case of membership of a (custom) Server Role, critical permissions of that role, are of interest.
Those who have followed my presentations on the topic “Security in SQL Server” in the last years, and especially the „SQL Server Attacked“ series launched in the summer of 2013, may remember how I had also demonstrated there how to break out of a database and be granted full system rights (“Elevation of privileges”), – based on a combination of database owner, configuration and impersonation.
Here, I am providing a T-SQL Script which
- Identifies the respective database owners of all databases
- Detects invalid/missing database owners
- Indicates whether the owner directly possesses security-critical system-wide rights
- Indicates membership in high privilege Server Roles – including the user defined Server Roles (possible since SQL 2012)
- Indicates the critical database configurations “trustworthy” and “database chaining”.
Here you can find the script:
- It should be working on all SQL Servers since the 2005 version (tested: 2008-2014).
For the purpose of the survey, I would like to ask you to send the anonymized results (preferably in excel-format) to the email survey@SarpedonQualityLab.com, or as well post them here in the comments.
For anonymization purposes, you can simply leave out the last three columns from the result set – these are not of use to a data collection and only meant for your internal usage :-).
Here, you can see a sample result:
The script does not permanently save any data in the system and removes its temporary intermediary results by itself.
I hope you find the report useful and I hope you can share the (anonymized) results with the community. I will not store or use any email but only use the anonymized Query-results.
Thank you in advance for your participation and support of the community! I promise to publish the results with according details on best & bad practices in the next months.