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