Policing the Use of SQL Server Management Studio

Microsoft's SQL Server Management Studio (SSMS) does suffer from a lack of dedicated developer talent assigned to update it, but it's the best tool we currently have for the full range of administrative duties database administrators face. SSMS also has the bonus of being free and easily-accessible from Microsoft's download site. This benefit is a blessing in disquise, though, since it also means anyone (and I do mean ANYONE) can also download Management Studio, and depending upon their rights, do as they please in any database environment in which they have access.

Problems with Liberal Use of db_owner Rights

I work for a healthcare organization and have done so for the past 15 years. While healthcare IT is being thrust to the front lines of media attention, it really is lagging behind many sectors of business. I've seen many third-party software vendors' products in my career and many—the extreme majority, unfortunately—take the easy way out when it comes to database security. They lock down features and functionality to a decent degree within their applications, but require the various pooled connections or individual logins to have (more often than not) db_owner permissions, or in some cases, server role permissions in SQL Server such as db_creator, security_admin, or even system_admin.

The response when I point out the fact that the least-intrusive, but most-pervasive of these scenarios, db_owner, allows for a user to do everything, often results in a virtual shrug via email and those most dreaded of phrases: "This is how we've always done it." I usually end up getting the vendor to acquiesce to using a combination of db_datareader, db_datawriter, and execute permissions for their stored procedures, but I still want to police the use of SSMS in our environment because of the red flags it throws.

Dynamic Management Objects to the Rescue

By using sys.dm_exec_sql_text (the DMF that returns executing sql commands based upon a sql_handle) that uniquely identifies a batch of SQL commands on a SQL instance, sys.dm_exec_sessions and sys.dm_exec_requests (which provides information on sessions and submitted requests, respectively, on a SQL instance) you can get metrics as to any actively launched cases of SSMS on a SQL Server:

SELECT SUBSTRING(ST.text, R.statement_start_offset / 2,          ( CASE WHEN R.statement_end_offset = -1             THEN DATALENGTH(ST.text)             ELSE R.statement_end_offset          END - R.statement_start_offset ) / 2) AS statement_executing , S.session_id, S.login_time, R.start_time, S.host_name, S.login_name, db_name(R.database_id) AS database__nameFROM sys.dm_exec_sessions S INNER JOIN sys.dm_exec_requests R ON S.session_id = R.session_idCROSS APPLY sys.dm_exec_sql_text(R.sql_handle) STWHERE S.program_name LIKE 'Microsoft SQL Server Management Studio%'AND R.session_id <> @@SPID

As you'll see, Ms. Jones has been a naughty analyst. She not only was using SSMS when she wasn't supposed to, but was also tracking down information she should not have been able to access from the system. Looks like both she and the DBA have some explaining to do.

More on the sys.exec_ Dynamic Management Objects Coming Soon

We'll take some time over the next few weeks looking at the various dm_exec... DMOs as we explore what sort of metadata gold can be mined from them. Stay tuned!

Related: Introduction to Using the Template Explorer in SQL Server Management Studio