Tag: T-SQL

Below is what I recommend to my clients to evaluate the security of their infrastructure quarterly, although more frequently is always better. Typically with this set of scripts, you’ll want to either compile the data into workbooks, flat files, or at least historic tables so you can track trends and changes.

We begin with two queries; one brings back the SQL Server Name and Instance so we know what we are evaluating and then we check the version of SQL Server which in itself has its own security repercussions based on service pack and year.

SELECT @@SERVERNAME AS 'SQL Server Name and Instance'
SELECT @@VERSION AS 'SQL Server Version'

The next script brings us a simple select of SQL Logins on the server. It’s a nice view of what logins exist, is our policy correctly enforced, do they have expired passwords, are the accounts locked out, and other helpful tidbits. Some additional reading on the returned records can be found here: https://msdn.microsoft.com/en-us/library/ms345412.aspx

SELECT  name ,
        Is_Policy_Checked ,
        Is_Expiration_Checked ,
        LOGINPROPERTY(name, 'IsMustChange') AS Is_Must_Change ,
        LOGINPROPERTY(name, 'IsLocked') AS [Account Locked] ,
        LOGINPROPERTY(name, 'LockoutTime') AS LockoutTime ,
        LOGINPROPERTY(name, 'PasswordLastSetTime') AS PasswordLastSetTime ,
        LOGINPROPERTY(name, 'IsExpired') AS IsExpired ,
        LOGINPROPERTY(name, 'BadPasswordCount') AS BadPasswordCount ,
        LOGINPROPERTY(name, 'BadPasswordTime') AS BadPasswordTime ,
        LOGINPROPERTY(name, 'HistoryLength') AS HistoryLength ,
        Modify_date
FROM    sys.sql_logins; 

This next query brings us a high level overview of what the logins have access to instance wide. It’s a great check for who is sysadmin or not.

SELECT  Name ,
        DenyLogin ,
        HasAccess ,
        SysAdmin ,
        SecurityAdmin ,
        ServerAdmin ,
        SetupAdmin ,
        ProcessAdmin ,
        DiskAdmin ,
        DBCreator ,
        BulkAdmin ,
        UpdateDate
FROM    syslogins
ORDER BY name;

We then break this down to how they have permissions. Is it a SQL or Windows account? What type of permissions are adjusted and what are our datestamps on these logins? Those questions are answered with this script, it’s a more granular approach to the query we previously looked at.

;WITH    ServerPermsAndRoles
          AS ( SELECT   spr.name AS login_name ,
                        spr.type_desc AS login_type ,
                        spm.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS permission_name ,
                      --  CONVERT(NVARCHAR(1), spr.is_disabled) AS is_disabled ,
                        'permission' AS permission_type ,
                        spr.create_date AS create_date ,
                        spr.modify_date AS modify_date
               FROM     sys.server_principals spr
                        INNER JOIN sys.server_permissions spm ON spr.principal_id = spm.grantee_principal_id
               WHERE    spr.type IN ( 's', 'u' )
               UNION ALL
               SELECT   sp.name AS login_name ,
                        sp.type_desc AS login_type ,
                        spr.name AS permission_name ,
                       -- CONVERT(NVARCHAR(1), spr.is_disabled) AS is_disabled ,
                        'role membership' AS permission_type ,
                        spr.create_date AS create_date ,
                        spr.modify_date AS modify_date
               FROM     sys.server_principals sp
                        INNER JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
                        INNER JOIN sys.server_principals spr ON srm.role_principal_id = spr.principal_id
               WHERE    sp.type IN ( 's', 'u' )
             ),
        MapDisabled
          AS ( SELECT
          DISTINCT      SP.NAME ,
                        SP.IS_DISABLED
               FROM     SYS.server_principals SP
                        FULL JOIN sys.server_role_members RM ON SP.PRINCIPAL_ID = RM.MEMBER_PRINCIPAL_ID
               WHERE    SP.type IN ( 's', 'u' )
             )
    SELECT  ServerPermsAndRoles.Login_Name ,
            ServerPermsAndRoles.Login_Type ,
            ServerPermsAndRoles.Permission_Name ,
            ServerPermsAndRoles.Permission_Type ,
            ServerPermsAndRoles.Create_Date ,
            ServerPermsAndRoles.Modify_Date ,
            MapDisabled.Is_Disabled
    FROM    ServerPermsAndRoles
            LEFT JOIN MapDisabled ON MapDisabled.name = ServerPermsAndRoles.login_name
    ORDER BY MapDisabled.is_disabled DESC ,
            ServerPermsAndRoles.login_name;

Now we see what those logins are mapped to and what permissions and what databases are impacted. You can see how many people have X, Y, or Z access to your databases and what’s mapped where.

DECLARE @DBRolePermissions TABLE(
 DatabaseName varchar(300), 
 Principal_Name sysname, 
 Login_Name sysname NULL, 
 DB_RoleMember varchar(300), 
 Permission_Type sysname);

INSERT INTO @DBRolePermissions
EXEC sp_MSforeachdb '
 SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name, 
 roles.Name AS Role_Member_Name, roles.type_desc
 FROM [?].sys.database_role_members r 
 LEFT OUTER JOIN [?].sys.database_principals users on r.member_principal_id = users.principal_id
 LEFT OUTER JOIN [?].sys.database_principals roles on r.role_principal_id = roles.principal_id
   --JOIN [?].sys.database_permissions Action on Action.grantee_principal_id = users.principal_id'

INSERT INTO @DBRolePermissions
EXEC sp_msforeachdb '
 SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name, 
 r.Permission_Name AS DB_RoleMember, r.class_desc
 FROM [?].sys.database_permissions r 
 LEFT OUTER JOIN [?].sys.database_principals users on r.grantee_principal_id = users.principal_id
 WHERE r.class_desc = ''DATABASE'''

SELECT DISTINCT Principal_Name, Login_Name, DatabaseName, DB_RoleMember AS Permission_Name
FROM @DBRolePermissions 
WHERE  Permission_Type <> 'DATABASE'
ORDER BY Principal_Name, DatabaseName, DB_RoleMember;

After evaluating who has access to what, I like to check and verify what databases are encrypted. This is a quick check to see what is and isn’t encrypted at rest and the strength setup.

USE master;
GO
SELECT db.name,
       db.is_encrypted,
       dm.encryption_state,
       dm.percent_complete,
       dm.key_algorithm,
       dm.key_length
FROM sys.databases db
     LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id;

The last check I do is seeing what the linked server and remote server permissions are like to keep tabs on our other endpoints.

SELECT ss.server_id 
          ,ss.name 
          ,'Server ' = Case ss.Server_id 
                            when 0 then 'Current Server' 
                            else 'Remote Server' 
                            end 
          ,ss.product 
          ,ss.provider 
          ,ss.catalog 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
                            end 
           ,'Remote Login Name' = sl.remote_name 
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled 
                                   when 1 then 'True' 
                                   else 'False' 
                                   end 
           ,'Data Access Enabled' = case ss.is_data_access_enabled 
                                    when 1 then 'True' 
                                    else 'False' 
                                    end 
           ,ss.modify_date 
      FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl 
        ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp 
        ON ssp.principal_id = sl.local_principal_id;

To bring it all together, it’s important to generate this data as a snapshot in time so you can compare it or write automated reporting to compare off this data so you can see how your environment is changing and who can access what. When in charge of a SQL Server, you need to keep track of who can do what and ensure the proper folks have proper permissions so you don’t run into compliance issues and minimize user impact on the databases.

Odds are that if you are reading this; someone, somewhere, somehow… has changed your data. Odds are also, you may be out of luck figuring out what changed and who did it. The good news is that the lesson has been learned and you can take action now to prevent this in the future.

SQL Server by default does not automatically track who does what to your tables. It does track the made to your database, but it won’t tell you “who done it?”. You can however use the transaction logs with extreme painstaking patience to reveal what changes were made.

There are features such as Change Data Tracking and Change Data Capture, this can potentially give you some better insight to the changes as they occur. Unfortunately, when it looks at who made the changes, it is only going to post the principal account running CDC/CDT and it will not reveal the culprit.

CDC / CDT

There is real-time tracing with SQL Profiler, but this creates extreme load on the server and it does not reveal the past. You can utilize SQL’s Dynamic Management Views and Extended Events to capture SQL in real time along with wait types, execution plans, and much more in depth information that doesn’t put as much load on the system. Sp_whoisactive does exactly this! Thank you Adam! So I run this every few minutes to capture data and do analysis on what my long running queries are and what my wait types look like, but more importantly on topic, it captures who ran what query and when. I run other scripts that I will eventually load to github and post for everyone, as well as a more in detailed view on how I performance tune my T-SQL.

The problem with real-time analysis, is that you have to catch it in real-time. There’s the chance the SQL ran too fast or sp_whoisactive was not running at the same time. This leads us to the only real solution, triggers.

I won’t dive into HOW to do this, because that is reinventing the wheel, but I will leave this resource that covers creating an audit trail to figure out what changed and by whom and when.