Just as a reminder they can be found in the drop down of the free scripts menu. Or here: sp_dbpermissions | sp_srvpermissions

New Features

• @UserLikeSearch to turn off the LIKE searches. It’s on by default.
• @IncludeMSShipped to exclude users/logins created by Microsoft. They are included by default.
• @DropTempTables to keep the temp tables (dropped by default) that the data is stored in. This way individuals and automated process can collected the data and process it. The temp tables are:
  • ##[DB/Srv]Principals
  • ##[DB/Srv]Roles
  • ##[DB/Srv]Permissions
• @Output allows for new output options.
  • None – No output at all. This is mostly meant to be used with when the plan is to collect data from the temp tables.
  • CreateOnly – Return only the create scripts.
  • DropOnly – Return only the drop scripts.
  • ScriptsOnly – Return both the create and drop scripts.
  • Default – Normal behavior.

Other changes

• A couple of bug fixes.
• Added IF EXISTS to the principal (user/logins) drop and create scripts.
• Moved SID towards the end of the list (leaving create & drop scripts at the end) since most of the time you don’t really need the SID.
• Remove the create/drop scripts for the users (sp_DBPermissions) guest, public, sys and INFORMATION_SCHEMA

Quick intro if you haven’t seen these SPs before. They are a couple of stored procedures I wrote to help me with security research. Each of the stored procedures returns three data sets.

1. A list of principals and some basic properties about them.
2. Role membership
3. Object/Database/Server level permissions

Each of the datasets has a set of do/undo scripts as well as various useful columns. For example the second data set contains information about which logins/users belong to which roles; and scripts to either add or remove the login/user from that role.

The stored procedures also have a number of parameters for restricting the result sets. For example principal name, role name, principal type, object name etc.

These sp’s can be run/stored anywhere and work just fine but if you run them in master then you can more easily call them from any database on the instance.

A few examples of times I’ve found them particularly handy:

• I need to know every database a user has access to and what access they have.
• I need to know all permissions for a given login across all databases.
• I need to copy a login from one server to another (with SID and password).
• I need to know everyone who has permissions to a specific object in the database.
• I need to know everyone who is a member of sysadmin.

Standard disclaimers: This code is as-is. You should be careful running code you haven’t reviewed on your systems. Feel free to use it and place it on your systems. Please do not post my code without giving proper credit and preferably back to the original page. And of course if you happen to notice a problem, or have a suggestion please post them here or email me and I’ll be glad to fix/add as appropriate.

One nice thing we can do with this is to create formatted column names. Say we have a query and we are planning on giving the output to our manager. It’s not worth doing anything special (ssis, ssrs) but we still want it to look nice.

SELECT name AS [Database Name],
	create_date AS [Date & Time Created]
FROM sys.databases

So updating a simple view is easy enough. Per BOL:

Updatable Views

• You can modify the data of an underlying base table through a view, as long as the following conditions are true:
• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
  • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
  • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

 
To put it very simply we can update a view if we update one table at a time and the view isn’t doing anything to the columns we are updating. But what do we do if we need to go beyond these restrictions? We can in fact use triggers. Specifically INSTEAD OF TRIGGERS. An INSTEAD OF TRIGGER basically replaces the command it is defined on. So for example an INSTEAD OF UPDATE trigger replaces the update command on the table or view it’s associated with.

For a simple example I’m going to try to update [AdventureWorks2014].[Sales].[vStoreWithContacts]. Note this particular view hits a number of different tables. I’m going to run an update that updates the name in the Store table and the first name of the Person table.

UPDATE [Sales].[vStoreWithContacts] 
SET Name = 'Next-Door Bike Store2',
	FirstName = 'Gustavo2'
WHERE BusinessEntityID = 292;

Which gives me the error:

Msg 4405, Level 16, State 1, Line 10
View or function ‘Sales.vStoreWithContacts’ is not updatable because the modification affects multiple base tables.

If I want this to work I can put in an INSTEAD OF trigger: (Note this is a VERY simple trigger that doesn’t do more than a fraction of what it should do.)

CREATE TRIGGER [Sales].[tr_vStoreWithContacts]
ON [Sales].[vStoreWithContacts] 
INSTEAD OF UPDATE
AS
UPDATE s
SET s.Name = i.Name
FROM [Sales].[Store] s
JOIN inserted i
	ON s.BusinessEntityID = i.BusinessEntityID;

UPDATE p
SET p.FirstName = i.FirstName
FROM [Person].[Person] p
JOIN [Person].[BusinessEntityContact] bec 
	ON p.[BusinessEntityID] = bec.[PersonID]
JOIN inserted i
	ON bec.BusinessEntityID = i.BusinessEntityID;
GO

Now we try again.

UPDATE [Sales].[vStoreWithContacts] 
SET Name = 'Next-Door Bike Store2',
	FirstName = 'Gustavo2'
WHERE BusinessEntityID = 292;

And no error this time and the data changes exactly as expected.

SELECT *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle);

And gotten an error similar to:

Msg 321, Level 15, State 1, Line 9
“sql_handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

Then first of all, I’m sorry. You are not only working on a 2008 R2 (or earlier) instance but you are working with a compatibility level 80 (SQL 2000) database. SQL Server 2008 R2 is the last version that allows a compatibility level of 80 so after this point you won’t see this problem any more.

Specifically you get an error when using CROSS APPLY with a Table Valued Function (In my example above the system TVF sys.dm_exec_sql_text) then you get the error I mentioned if you are in the context of a compatibility level 80 database. Now this ONLY happens when you are in the context of of a compatibility level 80 database. If you switch your context to a different database, say master (USE master), which has a compatibility level of 90 or greater then the bug goes away. Even if you are using a custom TVF that you built in the compatibility level 80 database.

If you are convinced, you can stop here. If not here is a quick example using a 2008 R2 instance.

CREATE DATABASE Compat80;
GO
ALTER DATABASE Compat80 SET COMPATIBILITY_LEVEL = 80;
GO
USE Compat80;
GO
CREATE FUNCTION TVF (@Col1 int)
RETURNS TABLE 
AS
RETURN (SELECT @COl1 AS Col1);
GO

This one returns an error:

USE Compat80;
GO
SELECT * FROM sys.databases
CROSS APPLY Compat80.dbo.TVF(database_id);

This one does not:

USE master;
GO
SELECT * FROM sys.databases
CROSS APPLY Compat80.dbo.TVF(database_id);

TL;DR Store @@ROWCOUNT into a variable immediately after the command you are interested in to persist the value for later use.

So why is it tricky to use? Remember that it returns the number of rows affected by the previous statement. Every statement. Look at this piece of code:

BEGIN TRY
	SELECT TOP 10 * FROM sys.objects
END TRY
BEGIN CATCH
	SELECT TOP 5 * FROM sys.objects
END CATCH
SELECT @@ROWCOUNT

You might think that the result of @@ROWCOUNT would be 10 since that’s the previous command that was run. You might also think it would be 5 since that’s the last command in the code, right? However you would be wrong on both counts. The TRY/CATCH block is actually the last command, so the result of @@ROWCOUNT is 0. If you plan on using @@ROWCOUNT you need to do this:

DECLARE @RowCount int

BEGIN TRY
	SELECT TOP 10 * FROM sys.objects
	SET @RowCount = @@ROWCOUNT
	SELECT @@ROWCOUNT
END TRY
BEGIN CATCH
	SELECT TOP 5 * FROM sys.objects
	SET @RowCount = @@ROWCOUNT
END CATCH
SELECT @RowCount

The result of this code shows that even the act of storing @@ROWCOUNT into a variable changes it. If you are a fan of physics you can think of this as the observer effect of SQL Server. Even the act of looking at @@ROWCOUNT changes it. On the other hand we do now have that value stored into a user variable so we can actually use it without losing it.

This is the trick to using this system variable. Store it into a user variable as the very next command.

SELECT *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.objectid = object_id('Test.dbo.Sneezy');

There is a bit of a problem with this though. sys.dm_exec_sql_text is slow, particularly when running against a large cache. I have one system where this can take 5-10 minutes to run. So this time I decided to use sys.dm_exec_procedure_stats instead. It’s fast and has an object_id column.

SELECT * FROM sys.dm_exec_procedure_stats 
WHERE object_id = object_id('Test.dbo.Sneezy');

It doesn’t, however, break the code down. It’s just the stats for the procedure as a whole. So for my first pass I grabbed the sql_handle (or plan_handle) out and then ran this query:

SELECT * FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE sql_handle = 0x0300060016B804056B1D2101FCA4000001000000000000000000000000000000000000000000000000000000

And I’m good to go! Each of the queries in the SP broken down. I still need more information though.

Well to make a long story, well, not quite as long, here is what I ended up with.

SELECT CAST(qp.query_plan AS XML) AS XML_Plan,
	SUBSTRING(st.text,qs.statement_start_offset/2+1,
            ((CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(st.text)
                ELSE qs.statement_end_offset END) - qs.statement_start_offset)/2 + 1)  AS SqlText,
	qs.*
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_procedure_stats ps
	ON qs.sql_handle = ps.sql_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, 
		statement_start_offset, statement_end_offset) qp
WHERE PS.object_id = object_id('Test.dbo.Sneezy');

I ended up with the query plan and text for each individual query within the SP and the associated stats. For example min/max/total duration/cpu time, number of executions, etc. All of which really helps you pin down the parts of the SP you really need to work on.

DECLARE @StartDate datetime2 = getdate()
PRINT @StartDate

Which returned:

2015-07-08 21:57:07.8670000

That’s an aweful lot of zeros at the end isn’t it? It didn’t take me long to realize that I needed something other than getdate(). After a bit of research I found sysdatetime().

DECLARE @StartDate datetime2 = sysdatetime()
PRINT @StartDate

Which returned:

2015-07-08 22:09:28.6553667

So the moral of the story is that when using one of the high precision date/time datatypes (time, datetime2, datetimeoffset) you need to use one of the high precision date/time functions (SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME, TIME).

For a quick review of the date time datatypes look here and for the date time functions here.

Well have you ever needed to match two values? Specifically where you need to match the values even if they are NULLs? Something like this.

CREATE TABLE Lookup1 (Code char(2), Description varchar(500));
INSERT INTO Lookup1 VALUES (NULL, 'Unknown'), ('AA','Division1'),
						('BB','Division2'), ('CC','Division3');
GO
CREATE TABLE SourceTable (
	Id_SourceTable int NOT NULL IDENTITY(1,1) CONSTRAINT pk_SourceTable PRIMARY KEY,
	Code char(2),
	MoreColumns varchar(50),
	MoreColumns2 varchar(50)
	);
GO
CREATE INDEX ix_SourceTable_Code ON SourceTable(Code);
GO
INSERT INTO SourceTable VALUES
	(NULL,'One','One'), ('AA','Two','One'), ('BB',NULL,'One'), ('CC','Four','One'),
	(NULL,'One','Two'), ('AA','Two',NULL), ('BB','Three','Two'), ('CC','Four','Two'),
	(NULL,'One','Three'), ('AA','Two','Three'), ('BB','Three',NULL), ('CC','Four','Three'),
	(NULL,'One','Four'), ('AA',NULL,NULL), ('BB','Three','Four'), ('CC','Four','Four');
GO

SELECT SourceTable.*, Lookup1.Description
FROM SourceTable
JOIN Lookup1
	ON SourceTable.Code = Lookup1.Code
	OR (SourceTable.Code IS NULL AND Lookup1.Code IS NULL)
GO

This isn’t the best data model in the world since it’s using a NULL as an actual value but it will do as an example.

Some people try code like this:

SELECT SourceTable.*, Lookup1.Description
FROM SourceTable
JOIN Lookup1
	ON ISNULL(SourceTable.Code,'NULL') = ISNULL(Lookup1.Code,'NULL')
GO

But I wouldn’t. SQL won’t use any indexes and that’s going to slow everything down.

The new interesting version looks like this:

SELECT SourceTable.*, Lookup1.Description
FROM SourceTable
JOIN Lookup1
	ON EXISTS (SELECT SourceTable.Code INTERSECT SELECT Lookup1.Code)
GO

In terms of speed/query plan it’s basically the same as the first query. It just looks better (IMHO). It get’s even better when you start dealing with multiple values.

DECLARE @Code char(2) = 'AA';
DECLARE @MoreColumns varchar(50) = NULL;

SELECT *
FROM SourceTable
WHERE EXISTS 
	(SELECT Code, MoreColumns INTERSECT
	SELECT @Code, @MoreColumns);

-- VS

SELECT *
FROM SourceTable
WHERE (Code = @Code OR
		(Code IS NULL AND @Code IS NULL))
	AND (MoreColumns = @MoreColumns OR
		(MoreColumns IS NULL AND @MoreColumns IS NULL));
GO

Still the same query plan but much easier to read and maintain. Pretty cool hu? I’ll be curious to see if this improves/worsens performance under more complex circumstances. It’s probably not a huge game changer but still rather interesting. And still matches the current best pattern so it’s worth keeping in your toolbox.

Note: You can use EXCEPT to to do not equals.

I’m going to do an example of tracking jobs that haven’t run yet even though they are scheduled to. First thing we need is to build our own schedule table. This would be fairly tricky if we were actually scheduling our jobs on this table but our task is somewhat simpler than that. So what we are going to do is create a table with the last scheduled run date, next scheduled run date, last run start and stop datetimes, and frequency. Each time a job runs it will update it’s LastRunStart and LastRunEnd values as the first and last step. Then when our monitor job runs it can check if the NextSchRunDate is both greater than the LastRunStart (hasn’t run yet) and less than the current date/time (should have run by now). Next it will update the Last & NextSchRunDate for those jobs where the NextSchRunDate is past. Unfortunately this is only going to handle simple schedules. Once a week, once a day, every other day, every four hours etc. It will not be able to easily handle a schedule like “Mondays and Thursdays”. For something like that you would need two entries and more complicated code in your job to update the correct row in the schedule table.

CREATE TABLE MySchedule (
	JobId INT NOT NULL IDENTITY (1,1),
	JobName VARCHAR(255),
	IsActive BIT,
	FrequencyType CHAR(2), 
	FrequencyAmt INT,
	LastSchRunDate DATETIME,
	NextSchRunDate DATETIME,
	LastRunStart DATETIME,
	LastRunEnd DATETIME
	)

-- Sample data
INSERT INTO MySchedule VALUES 
	('Job runs every Monday', 1, 'wk', 1, '4/20/2015',
	'4/27/2015', NULL, NULL)

INSERT INTO MySchedule VALUES 
	('Job runs the 3rd of each month', 1, 'mm', 1, '4/03/2015', 
	'5/03/2015', NULL, NULL)

INSERT INTO MySchedule VALUES 
	('Job runs every 4 hours', 1, 'hh', 4, '4/25/2015', 
	'4/25/2015 04:00:00', NULL, NULL)

CREATE PROCEDURE MonitorJobs AS

	-- Report on late jobs
	-- Maybe generate a table and use DBMail to send it out.
	SELECT JobName, NextSchRunDate, LastRunStart
	FROM MySchedule
	WHERE IsActive = 1
	  AND NextSchRunDate < GETDATE()
	  AND (LastRunStart IS NULL
		OR NextSchRunDate > LastRunStart
		  )

	-- Update NextSchRunDate if the current report has been run.
	--
	-- Unfortunately you can't have a frequency type stored in a 
	-- column or variable so this has to be a bit of a hack.  I 
	-- could either do this as a cursor & dynamic SQL updating
	-- each row one at a time or a big case statement for each 
	-- frequency type.
	--
	-- The next difficulty is if both the last run and last schedule
	-- run were more than one frequency type * frequency amount ago.
	-- In order to get the "next" scheduled time we divide then multiply
	-- by the FrequencyAmt to remove the remainder.  This only works
	-- because all of the values are integers.
	--  
	UPDATE MySchedule 
	SET LastSchRunDate = NextSchRunDate,
		NextSchRunDate = 
			CASE WHEN UPPER(FrequencyType) IN ('YEAR','YY','YYYY') THEN
				DATEADD(YEAR, FrequencyAmt + DATEDIFF(YEAR, NextSchRunDate, GETDATE())
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)
				WHEN UPPER(FrequencyType) IN ('QUARTER','QQ','Q') THEN
				DATEADD(QUARTER, FrequencyAmt + DATEDIFF(QUARTER, NextSchRunDate, GETDATE()) 
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)	
				WHEN UPPER(FrequencyType) IN ('MONTH','MM','M') THEN
				DATEADD(MONTH, FrequencyAmt + DATEDIFF(MONTH, NextSchRunDate, GETDATE()) 
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)	
				WHEN UPPER(FrequencyType) IN ('DAYOFYEAR','DY','Y') THEN
				DATEADD(DAYOFYEAR, FrequencyAmt + DATEDIFF(DAYOFYEAR, NextSchRunDate, GETDATE()) 
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)	
				WHEN UPPER(FrequencyType) IN ('DAY','DD','D') THEN
				DATEADD(DAY, FrequencyAmt + DATEDIFF(DAY, NextSchRunDate, GETDATE()) 
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)	
				WHEN UPPER(FrequencyType) IN ('WEEK','WK','WW') THEN
				DATEADD(WEEK, FrequencyAmt + DATEDIFF(WEEK, NextSchRunDate, GETDATE()) 
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)	
				WHEN UPPER(FrequencyType) IN ('WEEKDAY','DW','W') THEN
				DATEADD(WEEKDAY, FrequencyAmt + DATEDIFF(WEEKDAY, NextSchRunDate, GETDATE()) 
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)	
				WHEN UPPER(FrequencyType) IN ('HOUR','HH') THEN
				DATEADD(HOUR, FrequencyAmt + DATEDIFF(HOUR, NextSchRunDate, GETDATE()) 
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)	
				WHEN UPPER(FrequencyType) IN ('MINUTE','MI','N') THEN
				DATEADD(MINUTE, FrequencyAmt + DATEDIFF(MINUTE, NextSchRunDate, GETDATE()) 
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)	
				WHEN UPPER(FrequencyType) IN ('SECOND','SS','S') THEN
				DATEADD(SECOND, FrequencyAmt + DATEDIFF(SECOND, NextSchRunDate, GETDATE()) 
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)	
				WHEN UPPER(FrequencyType) IN ('MILLISECOND','MS') THEN
				DATEADD(MILLISECOND, FrequencyAmt + DATEDIFF(MILLISECOND, NextSchRunDate, GETDATE()) 
					/ FrequencyAmt * FrequencyAmt, 
					NextSchRunDate)	
			END
	WHERE IsActive = 1
	  AND NextSchRunDate < GETDATE()
	  AND NextSchRunDate < LastRunStart

Now obviously this is fairly limited. It does however give a good starting place. For example if you need a history it wouldn’t be hard to modify the code to do inserts rather than updates. Or if you want long running jobs add an expected run time column and if the job has a start time but not an end time and the difference between the start time and now is longer than the expected time you send an alert.

CREATE TABLE ChangeMe (
	ChangeMe_Id INT NOT NULL IDENTITY (1,1),
	FirstName varchar(50),
	LastName varchar(50),
	Phone1 char(10),
	Phone2 char(10),
	Phone3 char(10),
	Phone4 char(10),
	Salary money,
	LastBonus money,
	LastReview decimal(5,2),
	ManagerComments varchar(max)
	)

The changes that need to be made are the following:

Add
     Address1 varchar(255)
     Address2 varchar(255)
     City varchar(50)
     State char(2)
     Zip char(10)

Drop
     Salary
     LastBonus
     LastReview
     ManagerComments

Change
     Phone1 to HomePhone varchar(12)
     Phone2 to CellPhone varchar(12)
     Phone3 to WorkPhone varchar(12)
     Phone4 to PagerPhone varchar(12)

Here are the rules. No creating a new table and copying the data over. On the columns that need to be changed they actually have to be changed, not dropped and new ones created.

Give it a shot, then come back and check my answer. See if you can do it in less commands than I have. And try to keep the typing down. Remember copy and paste is your friend. If you do come up with something faster/easier please put it in the comments, I’d love to see it.
 
 
 
 
 

So here is my solution.

-- Rename 4 columns.  Four commands.
EXEC sp_rename 'ChangeMe.Phone1','HomePhone'
EXEC sp_rename 'ChangeMe.Phone2','CellPhone'
EXEC sp_rename 'ChangeMe.Phone3','WorkPhone'
EXEC sp_rename 'ChangeMe.Phone4','PagerPhone'

-- Alter 4 columns.  Four commands
ALTER TABLE ChangeMe ALTER COLUMN HomePhone varchar(12)
ALTER TABLE ChangeMe ALTER COLUMN CellPhone varchar(12)
ALTER TABLE ChangeMe ALTER COLUMN WorkPhone varchar(12)
ALTER TABLE ChangeMe ALTER COLUMN PagerPhone varchar(12)

-- Add 5 columns. One command.
ALTER TABLE ChangeMe ADD
	Address1 varchar(255),
	Address2 varchar(255),
	City varchar(50),
	State char(2),
	Zip char(10)

-- Drop 4 columns. One command
ALTER TABLE ChangeMe DROP COLUMN 
	Salary,
	LastBonus,
	LastReview,
	ManagerComments

-- Total 10 commands.

Renaming a column of course requires the sp_rename stored procedure. It only renames one column at a time so the best we can do is one command per column. Alter is the same way. ALTER TABLE ALTER COLUMN only works on one column at a time (at least as best I could find out). I did find it interesting that while you can only do one ALTER COLUMN per command you can ADD or DROP as many columns as you want in a single command. Only ADDs or DROPs in a single command though.

Personally I find this more “interesting T-SQL trivia” than something earth-shattering but it is handy to know at times. I’m a firm believer in knowing every bit of T-SQL I can because you never know when you will need it.