CODEWITHSUNDEEP
sqlsql-serverc#-4.0add-inssms
Console.WriteLine
Console.WriteLine

Reputation: 443

SSMS extensibility/addin - get current database and server

I am trying to write a SSMS addin for a custom auditing requirement (need to audit all queries ran by users in a production environment). I have the .addin file located in the appropriate folders and it hits the breakpoints in my Connect.Exec method and I am able to get the query statements under selection from the active document. However I am not sure if there is any property or method that I could look up to get the database name and the server the user was connected to ?

Upvotes: 3

Views: 501

Answers (2)

Console.WriteLine
Console.WriteLine

Reputation: 443

Some skimming through codeplex for 4 hours, downloading each project and analyzing the code gave me the answer I need. I hope this helps someone someday (although I agree with @Mitch if SQL Server Audit works for you, you should try that out first) ..

Add reference to Microsoft.SqlServer.RegSrvrEnum.dll and SqlWorkBench.Interfaces (located somewhere in your C:\ProgramFiles..\SQL Server.. -). Make sure you have installed the SDK for the tools. I have only tested this for SQL Server Management Studio 2014.

Then the below code should do the trick (your welcome!)

IScriptFactory scriptFactory = ServiceCache.ScriptFactory;
CurrentlyActiveWndConnectionInfo connectionIfno = scriptFactory.CurrentlyActiveWndConnectionInfo;
UIConnectionInfo conn = connectionIfno.UIConnectionInfo;
Debug.WriteLine("{0}::{1}", conn.ServerName, conn.AdvancedOptions["DATABASE"]);

Upvotes: 6

Mitch Wheat
Mitch Wheat

Reputation: 300519

...need to audit all queries ran by users in a production environment

That's what SQL Server Audit was designed for (SQL Server 2008 onwards):

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.

Any auditing solution must run at the Database Engine, not clients (for obvious reasons!).

Also, normal users should have (at most) read access to Production via SSMS (via login/role permissions), thereby ensuring they can't change anything. That seems preferable to logging the fact after it happened.

Upvotes: 1

Related Questions