2

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

  • I mean, sure, you could run a trace and only include database name and login info. There may still be a lot of rows, because you can't tell the trace to ignore a combo it has already seen. You also need to be sure you cover an entire business cycle and all of the obscure functionality and exceptions the apps may hit. – Aaron Bertrand Jul 18 '17 at 22:54
  • To expand on the Trace idea, you can set up a server-side trace (to minimize performance impact on the production server) and set it to log to a table on that instance. As Aaron said there is no way to tell a trace to ignore rows it has seen, but you can write a query to select distinct rows from the table instead. Be aware that depending on your instance's traffic this table could get very large very quickly so might not be a practical option. Alternatively you can output the trace to a file and import that file into a different server to process\query. – Libertheme Jul 19 '17 at 1:31
  • @Libertheme Server-side trace means the sp_trace_create usage, and it offers you to write to file(s) only. So there is no "table could get very large", it's up to you to produce the table from file(s) – sepupic Jul 19 '17 at 6:54
  • @sepupic So it does, I must have got mixed up with profiler functionality. Thanks for the clarification. – Libertheme Jul 20 '17 at 2:05
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.