SSMS / addin extension - get current database and server
I am trying to write an SSMS addin for a custom audit requirement (all requests made by users in a production environment need to be verified). I have a .addin file located in the appropriate folders and it hits breakpoints in my Connect.Exec method and I can get select request requests from the active document. However, I'm not sure if there is any property or method I could find to get the name of the database and the server the user was connected to?
source to share
Some slippage through the codeplex for 4 hours, loading each project and analyzing the code gave me the answer I needed. I hope this helps someone (although I agree with @Mitch, if SQL Server Audit works for you, you should try this first).
Add a link to Microsoft.SqlServer.RegSrvrEnum.dll and SqlWorkBench.Interfaces (located somewhere on your C: \ ProgramFiles .. \ SQL Server .. -). Make sure you have installed the tools SDK. I've only tested this for SQL Server Management Studio 2014.
Then the code below should do the trick (your trick!)
IScriptFactory scriptFactory = ServiceCache.ScriptFactory;
CurrentlyActiveWndConnectionInfo connectionIfno = scriptFactory.CurrentlyActiveWndConnectionInfo;
UIConnectionInfo conn = connectionIfno.UIConnectionInfo;
Debug.WriteLine("{0}::{1}", conn.ServerName, conn.AdvancedOptions["DATABASE"]);
source to share
... you need to audit all user queries in production
Something that SQL Server Audit was intended for (SQL Server 2008):
Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur to the database engine. SQL Server Auditing allows you to create server audits, which can contain server audit specifications for server-level events and database audit specifications for database-level events. Checked events can be written to event logs or to check files.
Any auditing solution should be done on the database, not on the clients (for obvious reasons!).
In addition, regular users should have (at most) read access to Production via SSMS (via login / role permissions), thereby ensuring that they cannot change anything. It seems preferable to record the fact after it has happened.
source to share