1

I have N applications using ORM, SQL-statements and stored procedures to access M tables from an MSSQL Server 2017. There are some shared tables that are used by various apps. Let's say I am forced to alter an existing attribute in one of these shared tables. Now I do not want to miss updating any application that uses this table / attribute.

What is the best way to keep track of things like that? Is there a best practice? My first thought was a documentation-related solution that has to be maintained manually. Is there a better approach?

exeC13
  • 39
  • 2
  • CASE tools used to help in this area since, e.g. IEF (changed names more than once). In short of this, you need to keep a manual log. If you have time on your hand you could write code to parse your code base and produce a report (not easy). – NoChance Jul 10 '19 at 07:13
  • Thanks @NoChance for the comment. I am new to this sub-stack. Anyone care to explain why my q was downvoted? Thanks. – exeC13 Jul 10 '19 at 07:25
  • I did not downvote your question. Don't worry too much about donw-voting, it is totally loose and site admins (most of the forums I joined) want it to remain loose for an unknown reason! – NoChance Jul 10 '19 at 08:54

2 Answers2

0

Each application should be connecting to the database using its own credentials. Each credential should be granted only the actions (read / write / execute) it needs only on the objects it needs. This is just good security hygiene.

If this is in place you can work backwards. If a credential has no access to an object that application cannot possibly be using it. There may be false positives if the app has stopped using an object but the permissions have not been revoked.

SQL Server has auditing and event capture functionality. These can show what login / IP address is using which objects within the DB. There can be problems, however. Too much logging is itself a burden on the server, for IO, memory and CPU. It is easy to go overboard and affect the production application. Also it can only see what has happened when it was turned on. Rare but important events such as year-end reporting and SEC filings may be missed. The same is true for looking in the query plan cache. Plans can be evicted from the cache due to memory pressure or on a service re-start. If there are many ad hoc queries their plans can flood the cache pushing out "real" workload. Monitoring the cache will show what's current but "current" will be defined differently for each system and will vary over time.

Michael Green
  • 903
  • 5
  • 16
  • from a security point of view actually no application should connect with it's own credentials and all should use trusted authentication and authorise windows groups to access a application database and or shared resources. – Walter Vehoeven Sep 11 '19 at 07:09
  • @PPann Sorry, I'm confused. Are you saying that the only legitimate authentication is an external service? – Michael Green Sep 11 '19 at 08:33
  • There is no way to enforce security when user names and passwords are stored in a application (config or code). Passwords can be miss-used and hard to change when expiring policy is followed. C3 audits are less valuable, Migrating servers becomes far more complicated than needed. Desaster recovery becomes far more complicated than needed. – Walter Vehoeven Sep 11 '19 at 09:04
  • Fair enough. The question was about column usage, though, not authentication management so that's what I addressed. – Michael Green Sep 11 '19 at 10:42
  • you can query the query plan cache and join them with the executed queries to see columns being used. Store these values in a table and link them to the credentials used to query the DDL – Walter Vehoeven Sep 11 '19 at 11:06
  • Our applications are run by AD users attached to IIS AppPools. On SQL Server side AD users are linked to database roles. So what @Michael Green is proposing here might work out for us. – exeC13 Sep 12 '19 at 16:16
-1

Have a look at the query plan cache, you can collect the column usage and match that with the credentials used to login.

I worked as a DBA and we rejected connections using a login trigger that did not have an application name and ITIL catalogue number in the connection string. When you have that you can map usage to application. This likely doesn't help you now but it could make sense to implement going foreward. Just start sending emails to the product owners and the application name is easily added as it's a non-breaking application change.

Have a look at the sample that you could use in your servers

enter image description here

You can collect and Aggregate this from a SP_WHO2 type of query that you trigger periodically or use a server scoped trigger. Please note that you can get quite a bit of information from the user using user_name(), suser_sname() and HOST_NAME() and the above mentioned APP_NAME() to get the name of the application connecting, this is free text specified in the connection string

To get the whole permission path (good for finding duplicate authorisations of a given user) use 

declare @user sysname =suser_sname()
EXEC xp_logininfo @user,'all';

You can also provide a user name using the 'domain\login' format.

Walter Vehoeven
  • 99
  • 5