Archive for October, 2014

How to drop subscriptions in Transactional Replication?

Scenario: You are working on refreshing the staging environment by using the database backups from the Production environment. Prior to the database restores, you had removed the subscriptions and publications and then performed the database restores. You had then re-created the publications and tried to subscribe to them. You are greeted by the following error message:

SQL Server could not create a subscription for Subscriber “Subscription_InstanceName”
Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information. Changed database context to ‘MCFIData’. (Microsoft SQL Server, Error: 14058)”

Replication Error

How can you resolve this issue? First of all, you already know that prior to the refresh, replication worked fine. Installing new patches or .NET Framework 4.5 or new or any other software will not solve your problem. Don’t mislead by others. Trust yourself. You will just need to find a way to remove the subscription that you thought you had removed prior to the refresh. Here’s the code that I had used to resolve this issue:

-- Run this on the Publisher
-- Replace the Publishing_DB, Publication_Name, Subscriber_InstanceName, Subscriber_DB accordingly
use Publishing_DB 
exec sp_dropsubscription @publication =N'Publication_Name', @subscriber = N'Subscriber_InstanceName', @article = N'all', @destination_db = N'Subscriber_DB'
GO

Make sure you monitor your publishing instance for blocking while this code to remove the subscription is running and make assessments and kill the blockers if needed or permitted.

How to List the Login’s Databases Access and Database Role(s) on One Row?

Challenge: A SQL Server login has access to 3 databases. The database users will be the same as the login. You have been requested to list the login’s database access on the instance and the role(s) the login’s database user belongs to in each of the databases like the screen-shot blow:
Logins and DB Access and Roles

Here’s the solution. It is based on the blog, http://www.handsonsqlserver.com/how-to-find-all-database-users-and-their-role-membership/ in addition to utilizing the correlated query plus the STUFF() function:

-- find all logins and their database access and role memberships in one row
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	create_date datetime,
	modify_date datetime
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME(), 
			dp.name,
			rp.name,
			dp.type_desc,
			dp.create_date,
			dp.modify_date
		FROM sys.database_role_members drm JOIN sys.database_principals rp
				ON drm.role_principal_id = rp.principal_id
			JOIN sys.database_principals dp
				ON drm.member_principal_id = dp.principal_id';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


--SELECT dsr.dbname AS [Database Name],
--	dsr.dbuser AS [Database User],
--	dsr.dbrole AS [Database Role],
--	dsr.account_type AS [Account Type],
--	dsr.create_date,
--	dsr.modify_date
--FROM #dbs_users_roles AS dsr;


IF OBJECT_ID('tempdb..#dbs_users') IS NOT NULL
	DROP TABLE #dbs_users;

SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.account_type AS [Account Type],
	dsr.create_date,
	dsr.modify_date,
	STUFF((SELECT '; ' + dr.dbrole 
			FROM #dbs_users_roles AS dr
			WHERE dr.dbname = dsr.dbname AND dr.dbuser = dsr.dbuser
			FOR XML PATH('')),1,1,''
	) AS [DB Roles]
INTO #dbs_users 
FROM #dbs_users_roles AS dsr
GROUP BY dsr.dbname, dsr.dbuser, dsr.account_type, dsr.create_date, dsr.modify_date


SELECT u.[Database User],
	u.[Account Type],
	STUFF((SELECT ' ' +'%[DB]: ' +urs.[Database Name]+ ' [DB Role(s)]:' +urs.[DB Roles]
		FROM #dbs_users urs
		WHERE urs.[Database User] = u.[Database User]
		FOR XML PATH('')),1,1,''
	) [DB Access and Roles]
FROM #dbs_users u
GROUP BY u.[Database User], u.[Account Type]

How to List the Server Roles for the Logins in a Row Separated by a Delimiter?

You have met the challenge to create the report to show all the logins and their corresponding servers roles. Now, some very wise people with pay grades much higher than yours have requested that you need to format the report so that the logins belonging to multiple server roles will be listed in one row rather than separate rows for server roles. For example, for demonstration purposes, I have added the TestLogin to both the sysadmin and the setupadmin server roles. Without added formatting, these two server roles will show in individual rows for the TestLogin. Then, I formatted the report so that the TestLogin will have both the sysadmin and setupadmin server roles listed in one row rather than the previous individual row for each. Here’s the screen-shot of the explanation and end result:
Formatting the Server Roles into a Row

The correlated subquery and the STUFF() function did all the magic. Here we go with the script:

-- find all logins and their server role memberships
IF OBJECT_ID('tempdb..#Logins_SrvRoles') IS NOT NULL
	DROP TABLE #Logins_SrvRoles;	

SELECT sp.name AS [Login Name],
	sp.type_desc [Account Type],
	sp.is_disabled,
	rp.name AS [Server Role],
    CONVERT(VARCHAR(10),sp.create_date ,101) AS [Date Created],
    CONVERT(VARCHAR(10),sp.modify_date , 101) AS [Date Last Modified],
	sp.default_database_name AS [Default Database]
INTO #Logins_SrvRoles
FROM sys.server_role_members rm JOIN sys.server_principals rp ON rm.role_principal_id = rp.principal_id
	JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
WHERE sp.type in ('S','U','G')

-- formatting the server roles for the logins into a row
SELECT L.[Login Name], L.[Account Type], L.is_disabled, L.[Date Created], L.[Date Last Modified], L.[Default Database],
	STUFF((SELECT '; ' + LR.[Server Role]
		FROM #Logins_SrvRoles LR
		WHERE LR.[Login Name] = L.[Login Name]
		FOR XML PATH('')),1,1,''
	) [SRV_Roles]
FROM #Logins_SrvRoles L
GROUP BY L.[Login Name], L.[Account Type], L.is_disabled, L.[Date Created], L.[Date Last Modified], L.[Default Database]

How to find all logins and their server role memberships?

Have you ever wished to know or have you ever been requested to create a report to show what logins belong to what server roles? While sys.server_role_members alone can produce the logins and server roles by supplementing it using the system function SUSER_NAME() as in:

SELECT SUSER_NAME(sr.role_principal_id) AS [Server Role]
		, SUSER_NAME(sr.member_principal_id) AS [Login Name]
	FROM sys.server_role_members sr

If you need to see other user data columns such as is_disabled, create_date, modify_date, and default_database_name of the logins, you will need to join sys.server_role_members to sys.server_principals. The following is one way that this can be done:

-- find all logins and their server role memberships
SELECT sp.name AS [Login Name],
	sp.type_desc [Account Type],
	sp.is_disabled,
	rp.name AS [Server Role],
    CONVERT(VARCHAR(10),sp.create_date ,101) AS [Date Created],
    CONVERT(VARCHAR(10),sp.modify_date , 101) AS [Date Last Modified],
	sp.default_database_name AS [Default Database]
FROM sys.server_role_members rm JOIN sys.server_principals rp ON rm.role_principal_id = rp.principal_id
	JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
WHERE sp.type in ('S','U','G')

How to find all database users and their role membership?

You need to generate security reports to retrieve the database users and their role memberships for SOX or other auditing reports requirements or for security troubleshooting purposes. SQL Server has a number of system tables when joined together can help you produce the security report needed. These tables are:

  • sys.sysusers
  • sys.sysmembers
  • master.dbo.syslogins

While these system table are considered deprecated, they are still being supported on SQL Server 2012 for backward compatibility. I will be working on an updated version when time permits. In case you have your version of the script using the updated versions of the system tables and views, please share. Updated version is now available using the sys.database_role_members and sys.database_principals system tables at the bottom of this blog.

Note that I have specifically not using the undocumented and unsupported stored procedure SP_MSFOREACHDB as it misses databases. A simple WHILE loop can accomplish the same.
Here we go with the script:

-- find all database users and their role memberships
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	login_name nvarchar(123),
	default_db nvarchar(123)
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME() AS [Database Name],
				su.name AS [Database User],
				CASE 
					WHEN (ug.uid is null) THEN ''public''
					ELSE ug.name
				END AS [Database Role],
				CASE 
					WHEN su.isntuser=1 then ''Windows Domain Account''
					WHEN su.isntgroup = 1 then ''Windows Group''
					WHEN su.issqluser = 1 then ''SQL Account''
					WHEN su.issqlrole = 1 then ''SQL Role''
				END AS [Account Type],
				l.loginname AS [Login],
				l.dbname AS [Default DB]
			FROM
			   sys.sysusers su LEFT OUTER JOIN
			   (sys.sysmembers sm INNER JOIN sys.sysusers ug ON sm.groupuid = ug.uid) ON su.uid = sm.memberuid
				LEFT OUTER JOIN master.dbo.syslogins l on su.sid = l.sid
			WHERE
			   (su.islogin = 1 and su.hasdbaccess = 1) and
			   (ug.issqlrole = 1 or ug.uid is null)';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.dbrole AS [Database Role],
	dsr.account_type AS [Account Type],
	dsr.login_name AS [Login Name],
	dsr.default_db AS [Deafult Database]
FROM #dbs_users_roles AS dsr;

Updated on October 05, 2014 to use the sys.database_role_members and sys.database_principals system tables:

-- find all database users and their role memberships
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	create_date datetime,
	modify_date datetime
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME(), 
			dp.name,
			rp.name,
			dp.type_desc,
			dp.create_date,
			dp.modify_date
		FROM sys.database_role_members drm JOIN sys.database_principals rp
				ON drm.role_principal_id = rp.principal_id
			JOIN sys.database_principals dp
				ON drm.member_principal_id = dp.principal_id';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.dbrole AS [Database Role],
	dsr.account_type AS [Account Type],
	dsr.create_date,
	dsr.modify_date
FROM #dbs_users_roles AS dsr;

How to Search All Stored Procedures for a Specific Keyword?

You have been assigned to support a SQL Server instance that your team has been given ownership of through recent re-organizations within your company. You need to search for the stored procedures that reference a specific table or keyword. There are no documentations and you do not have intimate or domain knowledge about the databases as they were created by developers that are no longer with the company or third-party software vendors that are no longer in business. Yes. It is a very tough job. Let’s get it done.

To search all the stored procedures in a database for a specific keyword:

-- search all stored procs in a database for a specific keyword
SELECT DB_NAME() AS [Database Name],
	OBJECT_NAME(object_id) AS [Stored Proc Name], 
	OBJECT_DEFINITION(object_id) as [Stored Proc Definition]
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%Keyword_Search%'

What if you need to search all the stored procedures in all databases in a SQL Server instance for a specific keyword? Use the following script instead:

-- search all stored procs in all databases for a specific keyword
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdSearch nvarchar(max)

IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
	DROP TABLE #db_list

CREATE TABLE #db_list
(
	id int identity (1,1)
	, dbname nvarchar(123)
);

IF OBJECT_ID('tempdb..#dbs_storedprocs') IS NOT NULL
	DROP TABLE #dbs_storedprocs

CREATE TABLE #dbs_storedprocs
(
	dbname nvarchar(123),
	storedproc_name nvarchar(250),
	storeproc_definition nvarchar(max)
);


INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

SELECT @id = 1, @max = max(id)
FROM #db_list

WHILE (@id <= @max)
BEGIN
	SELECT @dbname = dbname
	FROM #db_list
	WHERE id = @id;

	SET @cmdSearch = 'USE ' +@dbname+
		' SELECT DB_NAME(), 
				OBJECT_NAME(object_id), 
				OBJECT_DEFINITION(object_id) 
		 FROM sys.procedures
		 WHERE OBJECT_DEFINITION(object_id) LIKE ''%Keyword_Search%''';

	INSERT INTO #dbs_storedprocs
	EXEC (@cmdSearch);

	SET @id = @id + 1;
END

SELECT dbname  AS [Database Name],
	storedproc_name  AS [Stored Proc Name],
	storeproc_definition AS [Stored Proc Definition]
FROM #dbs_storedprocs 

How to Search SQL Server Agent Jobs for a Specific Keyword?

Similar to being dropped off in the middle of a forest that you had never visited, you had been assigned to troubleshoot SQL Server Agent job failures on a new SQL Server environment that you had never worked on. You had asked for documentations and the server contacts. You have gotten laugther replies – the answer is no and the contacts are no longer with the employer. That shouldn’t be a surprise of the century for you. The requester acknowledges that while there are a high number of job failures throughout the network, the top priority is to find the job that execute the stored procedure that runs the “Flash Report” and it’s conveniently named “usp_RunDailyFlashReport” as the requester had learned from the former SQL Server DBAs. How can you find all the SQL Server Agent jobs that may run this stored proc in a New York minute or less? Let’s try the following script:

-- search the jobs for a specific text 
SELECT SERVERPROPERTY('SERVERNAME') as [InstanceName],
	j.job_id,
	j.name,
	js.step_id,
	js.command,
	j.enabled 
FROM	msdb.dbo.sysjobs j
JOIN	msdb.dbo.sysjobsteps js
	ON	js.job_id = j.job_id 
WHERE	js.command LIKE '%KEYWORD%' -- replace keyword with the word or stored proc that you are searching for
GO