Sql-server – Get list of databases accessed by a particular SQL Server login

Securitysql server

I'm a DBA who recently joined a new company. One of the first tasks I am given is to ensure that any SQL accounts (both logins and users) do not have elevated permissions in our production environment. They should only have access that is required by the app/jobs they support. Management is fine with filtering access at the database level (granting db_datareader, db_datawriter, ddladmin, etc.) I've reached out to the people in charge of the apps/etl but they themselves are unsure of what access is required by these service accounts.

The problem here is, we have dozens of logins on each SQL instance, and over 50 SQL instances. Whoever created these logins had just granted sysadmin access rather than ensuring only the required access was given. Now we have many custom jobs and 3rd party apps running under these logins.

It's hard to make any changes to the permissions for these accounts without the possibility of breaking production apps/jobs. We don't have a test environment where I can test the changes.

I am thinking that if I can get a list of databases accessed by these service accounts over, say, a 2 month period, it would give me a starting point. I will start by granting db_owner to the logins on the appropriate databases and narrow it down from there.

I know that you can trace the actual SQL code being run by sessions, but is there a way to filter out the "noise" and just get the names of the databases accessed?

Also, I am welcome to any suggestions on an alternative approach to dealing with this issue, or if any other DBA's have tackled on a similar project in the past to share how they did it. This environment is primarily SQL Server 2008 R2 / 2012

Best Answer

You coud use Extended Events for this and stream the events in memory first and then to a database table, only when needed. I have a blog post on that topic that you can find here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/ Skip to the "Extended Events to the rescue" section.

Basically, what you do is you set up a trace for locks acquired and then count the occurrences of the locks, first in memory and then dump to a table. The example in that blog post is for table objects, but you could narrow it down for databases:

CREATE EVENT SESSION [audit_database_usage] ON SERVER 
ADD EVENT sqlserver.lock_acquired (
    ACTION(sqlserver.is_system, sqlserver.server_principal_name) 
    WHERE (
            [package0].[greater_than_equal_uint64]([database_id], (5)) -- user databases only
        AND [package0].[equal_uint64]([associated_object_id], (0))     -- filter out objects
        AND [package0].[not_equal_uint64]([database_id], (32767))      -- filter out resourcedb
        AND [package0].[equal_boolean]([sqlserver].[is_system], (0))   -- only user sessions
        AND [resource_type] = (2)                                      -- DATABASE resources
        )
    )
    WITH (
            MAX_MEMORY = 4096 KB
            ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
            ,MAX_DISPATCH_LATENCY = 30 SECONDS
            ,MAX_EVENT_SIZE = 0 KB
            ,MEMORY_PARTITION_MODE = NONE
            ,TRACK_CAUSALITY = OFF
            ,STARTUP_STATE = OFF
            )
GO

From this session it's easy to capture database access, without inspecting specifically what is being accessed inside the database, which reduces enormously the number of events captured. If you want to dig deeper and go to the object level, you can use the session in my blog post, which will produce a far greater number of events.

Related Question