About these ads

Archive for the ‘T-SQL’ Tag

T-SQL Tuesday #50: Database Role Membership   Leave a comment


imageFirst 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:

image

Example command:

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

Happy Automation!

About these ads

Posted January 15, 2014 by Amit Banerjee in Powershell, Security

Tagged with , , , ,

Enabling Transactional Replication: A bit of help   Leave a comment


Over the past few months, I have discussed the feasibility of enabling transaction replication for customer databases on various occasions. Every time I end up writing queries to answer certain questions about the database… the most common one being if the tables that need to be replicated have primary keys.

So I finally decided to write a T-SQL script which will help me answer the most common questions asked about a database while deciding on the feasibility of enabling transaction replication.

The script doesn’t capture information like workload, performance metrics etc. to decide if the replication workload (snapshot and distribution agent) can be supported on the existing hardware and resources available in the environment.

My take on the matter is that this information is required only once we have figured out if transactional replication can be enabled on the database or not. Eg. If the main tables that need to be replicated do not have primary keys, then the question of resource availability and hardware capability is moot point!

The script below checks the following:

1. Existing of primary keys on the tables in the database. Objects (articles) without primary keys cannot be replicated as part of a transactional replication publication.
2. If the database has transparent database encryption enabled. The subscriber database is not automatically enabled for TDE in such a scenario.
3. Constraints, primary keys, triggers and identify columns which have  NOT FOR REPLICATION bit set and which objects do not. You might choose to replicate or not replicate some of these objects. However, you need to be aware of what you are replicating.
4. Tables having ntext, text and image columns as there are special considerations for handling DMLs on such columns.
5. XML schema collections present in the database. Modifications to the XML Schema collection are not replicated.
6. Tables with sparse column sets as they cannot be replicated.
7. Objects created using WITH ENCRYPTION option. Such objects cannot be replicated either.

As always, in case you think that there are additional checks that could be included in the script, then please leave a comment on my blog and I will add the same into the script.

Read the rest of this entry »

Posted February 7, 2013 by Amit Banerjee in Replication, Scripts, TSQL

Tagged with , , ,

SQL Feature Discovery Script   9 comments


As part of my work, I very frequently have to collect information about the various database engine features that are currently being used on a particular SQL Server instance. Sometimes, this requires me to write T-SQL scripts to fetch the required information. I had updated my initial data collection script some time back and this gave me the idea to write up another set of T-SQL queries to fetch the information for the database engine features in use.

The script collects a bunch of information which are categorized under the following headings:

1. General Server Configuration
        Server Info
        Non-default sp_configure settings
        Server Settings
        Active Trace Flags
2. Replication Configuration
        Replication Publishers
        Merge Replication Publishers
        Replication Subscribers
        Replication Distributors
3. Full-text enabled databases
4. Linked Servers
5. SQL Agent information
6. Databases
        Database information
        Database file information
7. Server Triggers
8. Policy Based Management
9. Resource Governor
10. Database Mail
11. Log Shipping
12. Database Mirroring
13. SQL CLR Assemblies
14. sp_OA* procedures

Usage

  1. Download the script using the link given at the bottom of the page and save it to a file named SQL_DISCOVERY.SQL. Open the script file in a SSMS Query Window.
  2. Press CTRL+SHIFT+F so that the output results are put into a file. Doing this will not produce a message or any other notification.
  3. Execute the script and specify SQL_DISCOVERY.html as the output file name so that we can get the output in the require HTML format.
  4. Once the script is completed, open the HTML file.

Script download: image

If you have any feedback about the script or feel any new additions to the existing data that is being captured, please feel free to leave a comment!

Technorati Tags: ,,

Posted June 27, 2012 by Amit Banerjee in Data Collection, Scripts, TSQL

Tagged with , ,

Follow

Get every new post delivered to your Inbox.

Join 1,287 other followers