Security Is Everybody’s Responsibility

Recently, while diagnosing a permissions issue on a Production database, I realized that I didn’t really have any idea who had elevated permissions to any of databases on the instance. Particularly, I was interested in who the members of two specific database roles were, if any; database owner and security admin.

Database Roles

Let’s start with the Database Owner (db_owner). This role, basically, allows the user to do pretty much whatever they want to the database itself as well as all of the contents within it. Members of this role could drop tables, read data, add views, delete data or even drop the entire database to name just a few things. It is a definite security concern you should be aware of and make use of minimally.

Security Admin (db_securityadmin) is another database role that you should be cautious of.  Members of this role have the ability to elevate themselves or others to the database owner role.  Given that the database owner can do essentially anything to the database, you definitely want to verify who is listed as a Security Admin.

Use the below script to discover which accounts have these roles granted to it.

DISCLAIMER:  While I’ve been able to run it across multiple servers, over several hundred databases without an issue, your mileage will vary.  Please test accordingly.   NEVER run code that you find on the internet against your Production systems without testing first.  You have been warned!

/***************************************************************
-- Author: John Morehouse
-- Date: April 2015
-- T: @SQLRUS
-- E: john@jmorehouse.com
-- B: http://sqlrus.com
--THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
--IN OTHER WORDS: USE AT YOUR OWN RISK.
--AUTHOR ASSUMES ZERO LIABILITY OR RESPONSIBILITY.
--You may alter this code for your own purposes.
--You may republish altered code as long as you give due credit.
***************************************************************/DECLARE @dbs AS TABLE ( name sysname, processed BIT)
DECLARE @x INT = 1
DECLARE @sql VARCHAR (2000)
DECLARE @dbName VARCHAR (50)
IF object_id ('tempdb..#results') IS NOT NULL
        BEGIN
               DROP TABLE #results
        END
CREATE TABLE #results ( dbName sysname
                        , principalName VARCHAR (100)
                        , principalType VARCHAR (100)
                        , RoleMembership varchar(100)
                        , defaultSchema VARCHAR (100)
                        , principalSID varbinary(85)
                       )
INSERT INTO @dbs ( name, processed )
        SELECT name, 0 FROM sys.databases
               WHERE database_id > 6
                      AND [state] = 0 --online
WHILE @x <= (SELECT COUNT( 1) FROM @dbs WHERE processed = 0 )
        BEGIN
               SELECT TOP 1 @dbName = name FROM @dbs WHERE processed = 0
               SET @sql =
              'INSERT #results (dbName,principalName, principalType, RoleMembership, defaultSchema, principalSID)
                     SELECT '''+ @dbname +''',dp.name, dp.type_desc, dpr.name,dp.default_schema_name,dp.sid
                     FROM [' + @dbName + '].sys.database_role_members drm
                           INNER JOIN [' + @dbName + '].sys.database_principals dp on drm.member_principal_id = dp.principal_id
                           INNER JOIN [' + @dbName + '].sys.database_principals dpr on drm.role_principal_id = dpr.principal_id
                     WHERE dpr.name in (''db_owner'', ''db_securityadmin'')
                           AND dp.name <> ''dbo'''
        BEGIN TRY
             EXEC (@sql )
        END TRY
        BEGIN CATCH
               SELECT ERROR_LINE () AS 'Error Line'
                      , ERROR_MESSAGE () AS 'Error Message'
                      , ERROR_NUMBER () AS 'Error Number'
                      , @dbName AS 'Database'
        END CATCH
        UPDATE @dbs
        SET processed = 1
        WHERE name = @dbName
END
GO
SELECT * FROM #results
GO

Using this script in conjunction with our Central Management Server I was able to run it on all Production servers.  It quickly allowed me to see where I might have a security concern in each database on each server.

Once I was able to determine what permissions needed to be altered, we could address them accordingly.  Identifying these and making the appropriate changes helped to make our production environment that more secure.

Enjoy!