T-SQL Tuesday #50: Database Role Membership

Posted on January 15, 2014 by Amit Banerjee

First of all, a Happy New Year to everyone!

This month’s T-SQL Tuesday topic on Automation provides a perfect opportunity for me to blog about an automation that can be achieved around database roles and it’s members.

A big thank you to Hemanth [T | B] for hosting this year’s first T-SQL Tuesday and to Adam Machanic [B|T] as his initiative just reached a milestone! 50 months filled with awesome SQL related blog posts on a Tuesday!

The ask was to create a database role matrix for each and every user database on the SQL Server instance. Now the catch here was to find out memberships in custom database roles as well.

So here is how to go about this.

First task would be to identify each and every fixed database role that a particular database user is part of. This is a fairly simple task as there are system catalogs to fetch this information.

The next task is to find out each and every custom database role that exists in each user database. This can be achieved using the T-SQL script below.

<span style="font-size: small;">
/*
# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
# Author: Amit Banerjee
# Purpose: T-SQL script to fetch all the custom roles in a database
*/
DECLARE @ctr int = 1, @maxctr int = 1, @dbname sysname, @rsql nvarchar(4000)

CREATE TABLE #tblCustomRoles (rolename varchar(255))

SELECT identity(int,1,1) as databaseid, name
INTO #dbs
FROM sys.databases
WHERE database_id NOT IN (1,2,3,4)
AND state_desc = 'ONLINE'

SELECT @maxctr = COUNT(*) FROM #dbs
WHILE @ctr <= @maxctr
BEGIN
SELECT @dbname = name FROM #dbs WHERE databaseid = @ctr
SET @rsql = 'SELECT name FROM ' + @dbname +'.sys.database_principals where type_desc = ''DATABASE_ROLE'' and is_fixed_role = 0 and name <> ''public'''
INSERT INTO #tblCustomRoles
EXEC sp_executesql @rsql
SET @ctr += 1
END

SELECT DISTINCT ', CASE IS_MEMBER ('''+rolename+''') WHEN 1 THEN ''YES'' ELSE ''NO'' END as ' + quotename(rolename) as sqlstring
FROM #tblCustomRoles

DROP TABLE #tblCustomRoles
DROP TABLE #dbs

Now that you have list of custom database roles, you need to create a T-SQL query with the custom database role list and the fixed database role list. This query when executed against each user database will give you the database role matrix that you are looking for.

And this is where an automation scripter’s best friend, Powershell steps in. The script below:

1. Fetches the custom database roles from each database

2. Creates the T-SQL query for fetching the database role members

3. Executes the query against each database except for master, msdb, model and tempdb

4. Appends the output from each database into a CSV file


# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
# Author: Amit Banerjee
# Purpose: Powershell script to fetch role membership for each database user in all databases in a single SQL Server instance
# Usage: .\GetPerms.ps1 -vFileName "<Output file name with fully qualified path>" -vServer "<SQL Server Instance Name>" -vScriptFile "<Location of GetCustomRoles.sql>"

Param ([string] $vFileName, [string] $vServer, [string] $vScriptFile)

# Declaring variables
$VCustomRoles = ""

# Fetch the names of the databases
$vDatabases = Invoke-Sqlcmd -ServerInstance $vServer -Database "master" -Query "select name from sys.databases where database_id not in (1,2,3,4) and state_desc = 'ONLINE'"

# Find non-system database roles
$vRoles = Invoke-Sqlcmd -ServerInstance $vServer -Database "master" -InputFile $vScriptFile

# Construct the SQL query to be executed
$vQuery = "SELECT
db_name() as DatabaseName
, prin.name as DBUserName
, srvprin.name as LoginName
, srvprin.create_date
, CASE IS_MEMBER ('db_owner') WHEN 1 THEN 'YES' ELSE 'NO' END as [db_owner]
, CASE IS_MEMBER ('db_accessadmin') WHEN 1 THEN 'YES' ELSE 'NO' END as [db_accessadmin]
, CASE IS_MEMBER ('db_securityadmin')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_securityadmin]
, CASE IS_MEMBER ('db_ddladmin')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_ddladmin]
, CASE IS_MEMBER ('db_backupoperator')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_backupoperator]
, CASE IS_MEMBER ('db_datareader')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_datareader]
, CASE IS_MEMBER ('db_datawriter')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_datawriter]
, CASE IS_MEMBER ('db_denydatareader')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_denydatareader]
, CASE IS_MEMBER ('db_denydatawriter')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_denydatawriter]
, CASE IS_MEMBER ('public')  WHEN 1 THEN 'YES' ELSE 'NO' END as [public]
"

# Foreach loop to identify each custom role in all the databases on the instance
foreach ($vRole in $vRoles)
{
$vQuery = $vQuery + $vRole.sqlstring + "`n"
}

# Complete the T-SQL query to be executed
$vQuery = $vQuery + "FROM sys.database_role_members mem
INNER JOIN sys.database_principals prin on mem.member_principal_id = prin.principal_id
INNER JOIN sys.database_principals rol on rol.principal_id = mem.role_principal_id
LEFT OUTER JOIN sys.server_principals srvprin on srvprin.sid = prin.sid
WHERE prin.is_fixed_role = 0 and srvprin.type in ('U','S')"

# Foreach loop to fetch the role membership of each user and append to a CSV file
foreach ($db in $vDatabases)
{
"Fetching database role matrix for database: " + $db.name
# NoTypeInformation prevents the #TYPE System.Data.DataRow from being the first row in the CSV file
# Use Invoke-SQLCMD to get the database role matrix for each database and append it to an output CSV file
Invoke-Sqlcmd -ServerInstance $vServer -Database $db.name -Query $vQuery | Export-Csv -Path $vFilename -Append -NoClobber -Encoding UTF8 -ErrorAction Stop -NoTypeInformation
}

The CSV output file shows a neat matrix as seen in the screenshot below:

Example command:

.\GetPerms.ps1 -vFilename G:\Perms.CSV -vServer MYSERVER -vScriptFile “G:\GetCustomRoles.sql”

Happy Automation!

T-SQL Tuesday #49: Why was SQL waiting?

Posted on December 10, 2013 by Amit Banerjee

A lot of my customer engagements involve identifying waits experienced by SQL Server queries on a production environment. Since this month’s topic is WAITS… I see this as a good opportunity to talk about how I go about the analysis of the collected data to identify the blocking chains.

Before I start rambling off, a note of thanks to my friend Robert Davis [B|T] for hosting this month’s T-SQL Tuesday! He couldn’t have chosen a better topic! 🙂

In this post, I will be talking about how to slice and dice the diagnostic data collected by SQL Perf Stats script that is imported into a SQL Nexus database.

The tables of interest in the SQL Nexus database are:

1. tbl_Requests – This table holds the combined output of the snapshots collected by the Perf Stats script from the sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_connections and sys.dm_os_tasks

2. tbl_NotableActiveQueries – This table holds the output sys.dm_exec_sql_text and sys.dm_exec_query_stats snapshots collected by the Perf Stats script.

The file that needs to be present when SQL Nexus is imported the collected diagnostic data is <server name>__SQL_2008_Perf_Stats_Startup.OUT. The plethora of information available in this file can be mind boggling at times. So pushing all this information into a database makes a lot of sense.

The first order of the day is to determine if there was actually blocking experienced during the data collection. The output of the query will give you collected snapshots where blocking was detected.

<span style="font-size: small;">select runtime,count (*) as blocked_processes
from tbl_requests
where blocking_session_id > 0 and session_id <> blocking_session_id
group by runtime
having count(*) > 1
order by runtime

The next order of the day is to find out which sessions were blocked. The SQL Nexus reports do a good job of drilling down into each blocking session observed. But if you want a collated view of all the blocking along with the list of head blockers, then the following T-SQL script could prove useful especially when the .OUT file being imported is a few hundred MBs in size! This would help you slice and dice the MBs or GBs of data that was imported into the SQL Nexus database tables and get a consolidated view of the blocking chains.

/*
This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

Author: Amit Banerjee

Modified: December 10, 2013

Description:

This T-SQL script extracts information about the blocking chains from the SQL Nexus database

*/

set nocount on
print '================== Blocking Analysis ====================='
print ''
print 'Runtime Information'
select min(runtime) as [Start Time], max(runtime) as [End Time] from tbl_Requests

if ((select count(*) from tbl_requests where blocking_session_id <> 0) > 0)
begin
print 'Blocking found'
print ''
print '============= Blocking Chains (Runtimes considered where blocking_session_id processes > 5) ==================='
-- Show a summary of the # of blocked sessions for every snapshot collected by the PerfStats script
select runtime,count (*) as blocked_processes
from tbl_requests
where blocking_session_id > 0 and session_id <> blocking_session_id
group by runtime
having count(*) > 1 -- The threshold can be changed as appropriate
order by runtime

print ''
print ''
print '================= Head Blocker Information ==================='
if (object_id('tmp_runtimes') <> NULL)
begin
drop table tmp_runtimes
end

-- Create a table to store the runtimes
CREATE TABLE tmp_runtimes(
[row_num] [bigint] IDENTITY(1,1) NOT NULL,
[runtime] datetime
) ON [PRIMARY]

-- Create a list of the distinct runtimes
insert into tmp_runtimes
select distinct (runtime)
from tbl_requests
where blocking_session_id <> 0
order by runtime

-- Start a while loop to fetch the data for the head blocker and blocked sessions
declare @count int,@loop int,@runtime datetime
select @count = count(*) from tmp_runtimes
set @loop = 1
while (@loop <> @count)
begin
select @runtime = runtime from tmp_runtimes where row_num = @loop

print '~~~~~~~~~~~~~~~ Runtime: '
select @runtime
print ''
print '~~~ Head Blocker'

select a.session_id,a.ecid,a.blocking_ecid,wait_type,wait_duration_ms,command,b.dbname,b.procname,b.stmt_text
from tbl_requests a
left outer join tbl_notableactivequeries b
on a.runtime = b.runtime and a.session_id = b.session_id
where blocking_session_id = 0 and a.runtime = @runtime and a.session_id in
(select blocking_session_id
from tbl_requests where blocking_session_id <> 0 and runtime = @runtime)

print ''
print '~~~ Blocked Session Information'
select *
from tbl_requests a
left outer join tbl_notableactivequeries b
on a.runtime = b.runtime and a.session_id = b.session_id
where a.blocking_session_id <> 0
and a.runtime = @runtime
order by a.session_id,a.blocking_session_id

set @loop = @loop + 1

end

drop table tmp_runtimes

end

else
begin
print 'No Blocking found'
end

set nocount off

With the help of the above output, you can get a collated view of all the blocking chains encountered during the data collection period. CSS engineers are Microsoft use SQL Nexus extensively for analyzing diagnostic data collected for SQL Server performance issues. I hope you find this script useful in analyzing blocking chains and do keep the feedback coming in. The above script is available for download on SkyDrive as well.

Please note that the PerfStats script collects data at 10 second intervals by default! If your blocking chains are for shorter durations then you will need to change the waitfor delay interval. If your blocking duration is lesser than a second, then data collection using the DMVs would not be of much help and you would need to go down the path of Extended Events tracing or other invasive forms of data collection (like XPerf utility).

SQLBangalore Annual Day Event

Posted on December 5, 2013 by Amit Banerjee

It never ceases to surprise me the enthusiasm shown by the SQL community for attending SQL Server community events that are organized by SQLBangalore. This time around SQLBangalore organized a FULL day event dedicated to SQL Server topics on 30th November, 2013 at Bangalore. I had the opportunity to present at this event on Troubleshooting SQL Server with Power Pivot and Power View. At this event, you had the usual suspects Balmukund Lakhani [T: @blakhani | B: sqlserver-help], Vinod Kumar [B|T], Sourabh Agarwal [B|T] and many others speaking. Quite a few of the familiar faces were seen in the crowd of attendees and then there were some new faces. It is always a good to connect with the community members. This session was an extension of my last session on Power View and Power Pivot. The Excel files are available on SkyDrive. The download links are available in each of the posts mentioned below.

Once again a big THANK YOU to everyone who attended the event! Feel free to post your questions here about my session in case you have any. The presentation used at the session is available below and on SlideShare as well.

Blog posts on this topic:

PowerView and System Health Session–CPU health

PowerView and System Health Session–Scheduler Health

PowerView and System Health Session–SQL Memory Health

PowerView and System Health Session– IO Health

PowerView and System Health Session– System

WOOT: Schema Changes History Report on Power View

WOOT: Default Trace and Power View

WOOT: Schema Changes History Report on Power View

Posted on October 19, 2013 by Amit Banerjee

The last post in this series talked about using Power View to analyze the data stored in the SQL Server’s default trace. I decided to take this a step further by creating the Schema Changes History report with the help of the data that I retrieved from the Default Traces. The advantage of a report created in Power View is that the interactivity which is missing in the standard report is available.

The way I created this report was to filter the data in the Power Pivot table using EventClass ID 46, 47 and 164 for only looking at the create, drop and alter commands which the default trace tracks. After that I created a table with a tile on the Database Name and a 100% Stacked Bar Chart to show the activity at a database level.

I also had to create linked tables for getting the Object Type and the Event Class Name that you see in the table below.

I will provide a final version of the Excel sheet once I have completed the other dashboards and sanitized the information available in the Power Pivot table.

Previous Post in the Series:

Default Trace Dashboard
https://troubleshootingsql.com/2013/09/26/woot-default-trace-and-power-view/

WOOT: Default Trace and Power View

Posted on September 26, 2013 by Amit Banerjee

I have been working on building visualizations for various kinds of analysis that I perform for my customers. One such useful visualization was the use of Power View for analyzing the data available in the SQL Server Default Trace. The query below lets you retrieve all the information in the default traces. This same query is used to populate the Power Pivot table in the Excel file.


declare @enable int

-- Check to find out if Default Server Side traces are running
select top 1 @enable = convert(int,value_in_use) from sys.configurations where name = 'default trace enabled'

if @enable = 1 --default trace is enabled
begin

declare @d1 datetime;
declare @diff int;
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int ;

select @curr_tracefilename = path from sys.traces where is_default = 1 ;

set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = PATINDEX('%\%', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

select EventCat.name as Category, EventID.name as EventName, Events.*
from ::fn_trace_gettable( @base_tracefilename, default ) Events
inner join sys.trace_events EventID
on Events.EventClass = EventID.trace_event_id
inner join sys.trace_categories EventCat
on EventID.category_id = EventCat.category_id

end

Once I have all the trace data available in my Power Pivot table, I created calculated columns for Day, Hour and Minute. Now that I have all the data readily available for me, I went about creating the main dashboard which provides a view of all the events that occurred along with a time line view. All this took me less than 5 minutes after I had finished writing the query! Pretty quick. Now I have an interactive report that I can use for performing various kinds of analysis.

The screenshot below will show that there was only one event raised for the Server event category and the actual time of occurrence is shown in the line graph. A simple mouse over on the point will give you the exact details. Now isn’t that a simple way to track down events! Smile

I will provide a final version of the Excel sheet once I have completed the other dashboards and sanitized the information available in the Power Pivot table.