Not Normalized Rotating Header Image

Enumerate Active Directory Group Membership w/ TSQL

A user called me up a couple weeks back wanting to know why their co-worker was unable to successfully import and query data in SQL Server.  I looked at the database server and the database in question to see what the users had been granted access to.  However, I did not find either of the users explicitly granted permissions.  I did find that there were a couple of Active Directory Groups with permissions.

I looked at the permissions of both groups and found that one of the groups had more permissions than the other.  I needed to see if the user that called me and their co-worker were in one or both of those groups.

I have access to our Active Directory so I was able to check out the groups directly.  However I may not always be that fortunate and wanted to know how I could get this information without having AD access and without asking the Windows admin directly.

I did some searches and found references to setting up a linked server pointing to Active Directory and/or using the ADSI (Active Directory Service Interface). I also located a powershell script that promised to return the members of AD Groups. These options are interesting – and are something I may try in the future – but at the moment I was looking for a way to find this information directly using TSQL.

Searching a little bit I found the SQL Server Server extended stored procedure “xp_logininfo”.  See the Microsoft Technet Reference for xp_logininfo here.

I started by using each user’s account to see what groups, if any, they were a member of.

1
EXECUTE xp_logininfo 'CORP\UserWhoCalled', 'all'

image

1
EXECUTE xp_logininfo 'CORP\TheirCoworker', 'all'

image

Interesting – the coworker was not a member of the “CORP\DataImportGroupUsersGrp_sec” AD Group.  Adding them to this group gave them the ability to import and query data as expected.

Going another step further I wanted to see if I could use the AD Groups with the xp_logininfo stored procedure to see other members of the groups.

1
EXECUTE xp_logininfo 'CORP\ApplicationUserGroup_sec', 'members'

This returned a large group (100+) accounts that were granted access to the database and application through this AD Group.

1
EXECUTE xp_logininfo 'CORP\DataImportGroupUsersGrp_sec', 'members'

This returned less accounts – but included both the user who called me and their co-worker.

*According to the Technet Reference the xp_logininfo procedure should only return information from AD Global Groups and not Universal Groups. This was not my experience. I received information about both groups even though the “CORP\ApplicationUserGroup_sec” group is global and the “CORP\DataImportGroupUsersGrp_sec” group is universal.

CREATE_DATABASE and DROP_DATABASE Server Trigger

Install and enable these two triggers* on a MSSQL Server instance to receive notification anytime a database is created or dropped.

Creating a new database or dropping an existing database has many implications including but not limited to storage, security, performance, backup and recovery.

Ideally the DBA should already be expecting the notification email as they should be aware that a database is being created or dropped but since interpersonal communication can fall apart at times the automated notification can be crucial.

* make sure to change the email address to a valid email address or distribution group in your organization; previous configuration of database mail is assumed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
USE [master]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dba_DatabaseCreated] ON ALL SERVER
      FOR CREATE_DATABASE
AS
 
BEGIN
 
      SET NOCOUNT ON ;
 
	  DECLARE @myNewDatabaseName VARCHAR(256) ;
	  SET @myNewDatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)') ;
 
	  DECLARE @myEmailSubject VARCHAR(255) ;
	  SET @myEmailSubject = @@SERVERNAME + ' ALERT - Database [' + @myNewDatabaseName + '] Created'
 
	  DECLARE @NewDatabase TABLE 
		( [EventType] [VARCHAR](50) NOT NULL
		, [PostTime] [DATETIME] NOT NULL
		, [SPID] [INT] NOT NULL
		, [ServerName] [VARCHAR](256) NOT NULL
		, [LoginName] [VARCHAR](256) NOT NULL
		, [DatabaseName] [VARCHAR](256) NOT NULL
		, [TSQLCommand] [VARCHAR](MAX) NOT NULL
		) ;
 
      INSERT INTO @NewDatabase
                ( [EventType]
                , [PostTime]
                , [SPID]
                , [ServerName]
                , [LoginName]
                , [DatabaseName]
                , [TSQLCommand]
                )
      VALUES    ( EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
                , EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
                , EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'int')
                , EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(256)')
                , EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
                , EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
                , EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(MAX)') 
                )
                ;
 
		DECLARE @TableHTML VARCHAR(MAX) ;
		SET @TableHTML = N'
		<h1>Database Created Event</h1>
		<table border="4"><tbody><tr><th>EventType</th><th>PostTime</th><th>SPID</th><th>LoginName</th><th>ServerName</th><th>DatabaseName</th><th>TSQLCommand</th></tr>' +
			CAST ( ( SELECT td = [EventType], ''
					,      td = [PostTime], ''
					,      td = [SPID], ''
					,      td = [LoginName], ''
					,      td = [ServerName], ''
					,      td = [DatabaseName], ''
					,      td = [TSQLCommand], ''
					FROM @NewDatabase
					FOR XML PATH('tr'), TYPE 
					) AS NVARCHAR(MAX) ) +  N'
		</tbody></table>' 
		;
 
		EXECUTE msdb.dbo.sp_send_dbmail @recipients = 'databaseteam@yourcompany.com'
		,                               @subject = @myEmailSubject
		,                               @body = @tableHTML
		,                               @body_format = 'HTML'
		,                               @importance = 'High'
		;            
 
END
 
 
GO
 
SET ANSI_NULLS OFF
GO
 
SET QUOTED_IDENTIFIER OFF
GO
 
ENABLE TRIGGER [dba_DatabaseCreated] ON ALL SERVER
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
USE [master]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dba_DatabaseDropped] ON ALL SERVER
      FOR DROP_DATABASE
AS
 
BEGIN
 
      SET NOCOUNT ON ;
 
	  DECLARE @myOldDatabaseName VARCHAR(256) ;
	  SET @myOldDatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)') ;
 
	  DECLARE @myEmailSubject VARCHAR(255) ;
	  SET @myEmailSubject = @@SERVERNAME + ' ALERT - Database [' + @myOldDatabaseName + '] Dropped'
 
	  DECLARE @OldDatabase TABLE 
		( [EventType] [VARCHAR](50) NOT NULL
		, [PostTime] [DATETIME] NOT NULL
		, [SPID] [INT] NOT NULL
		, [ServerName] [VARCHAR](256) NOT NULL
		, [LoginName] [VARCHAR](256) NOT NULL
		, [DatabaseName] [VARCHAR](256) NOT NULL
		, [TSQLCommand] [VARCHAR](MAX) NOT NULL
		) ;
 
      INSERT INTO @OldDatabase
                ( [EventType]
                , [PostTime]
                , [SPID]
                , [ServerName]
                , [LoginName]
                , [DatabaseName]
                , [TSQLCommand]
                )
      VALUES    ( EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
                , EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
                , EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'int')
                , EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(256)')
                , EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
                , EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
                , EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(MAX)') 
                )
                ;
 
		DECLARE @TableHTML VARCHAR(MAX) ;
		SET @TableHTML = N'
		<h1>Database Dropped Event</h1>
		<table border="4"><tbody><tr><th>EventType</th><th>PostTime</th><th>SPID</th><th>LoginName</th><th>ServerName</th><th>DatabaseName</th><th>TSQLCommand</th></tr>' +
			CAST ( ( SELECT td = [EventType], ''
					,      td = [PostTime], ''
					,      td = [SPID], ''
					,      td = [LoginName], ''
					,      td = [ServerName], ''
					,      td = [DatabaseName], ''
					,      td = [TSQLCommand], ''
					FROM @OldDatabase
					FOR XML PATH('tr'), TYPE 
					) AS NVARCHAR(MAX) ) +  N'
		</tbody></table>' 
		;
 
		EXECUTE msdb.dbo.sp_send_dbmail @recipients = 'databaseteam@yourcompany.com'
		,                               @subject = @myEmailSubject
		,                               @body = @tableHTML
		,                               @body_format = 'HTML'
		,                               @importance = 'High'
		;            
 
END
GO
 
SET ANSI_NULLS OFF
GO
 
SET QUOTED_IDENTIFIER OFF
GO
 
ENABLE TRIGGER [dba_DatabaseDropped] ON ALL SERVER
GO
Creative Commons Attribution 4.0 International License

Truncate Tables but Preserve Foreign Keys

During a Data Mart deployment there was a requirement to remove all data from the target database while leaving the schema intact.

The development team working on the issue made a couple of different attempts at solving the requirement.  Their approaches worked but had drawbacks in the areas of performance and maintainability.

The First Approach – DELETE *

The first attempt utilized DELETE, placing all of the tables in the correct order to avoid foreign key constraint errors.  Like mentioned before – this worked so long as the tables were in the correct order.; Otherwise they would receive this error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

The other problem with this approach is that DELETE statements delete rows one at a time and is fully logged.  It was also not very fast.

The Second Approach – TRUNCATE *

While attempting to implement the second approach utilizing TRUNCATE they team realized that they could not truncate data in a table that was referenced by a foreign key. No problem – they manually scripted out the drop of the foreign keys at the beginning of the command followed by the truncate commands followed by the creation of the foreign keys at the end of the command.

This worked great. Truncate takes the whole table at a time – and is minimally logged. It was fast. Life was good. Until the next deployment when there were a whole bunch of new tables in the Data Mart and it was time to rescript this procedure.

My Solution – Truncate all the Tables but Preserve Foreign Keys

Wait – that’s what they did in the second approach right ? Well Yes – and No. My solution is dynamic and automatically preserves all the FK constraints at the beginning of the script before performing the truncation and finally reapplying the FK constraints.

Without further ado – here is the script I generated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
USE [MyDatabase]
GO
SET NOCOUNT ON
GO
 
PRINT 'Preparing FK Drop / Create Commands.....'
	CREATE TABLE [##FKCommands_Drop]   ([RowId] [INT] IDENTITY(1,1) NOT NULL, [CmdText] VARCHAR(2000) NOT NULL) ON [PRIMARY] ;
	GO
	CREATE TABLE [##FKCommands_Create] ([RowId] [INT] IDENTITY(1,1) NOT NULL, [CmdText] VARCHAR(2000) NOT NULL) ON [PRIMARY] ;
	GO
 
	INSERT INTO [##FKCommands_Drop] ([CmdText]) 
	SELECT  'IF EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N''' + '[' + [f].[name] + ']'') AND parent_object_id = OBJECT_ID(N''' + '[' + OBJECT_NAME(f.parent_object_id) + ']''))' + ' ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id) + ']' + ' DROP CONSTRAINT ' + '[' + f.name + '] ;'
	FROM    [sys].[foreign_keys] AS [f]
	INNER JOIN [sys].[foreign_key_columns] AS [fc]
	ON      [f].OBJECT_ID = [fc].constraint_object_id ;
 
	INSERT INTO [##FKCommands_Create] ([CmdText]) 
	SELECT  'IF NOT EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N''' + '[' + [f].[name] + ']'') AND parent_object_id = OBJECT_ID(N''' + '[' + OBJECT_NAME(f.parent_object_id) + ']''))' + ' ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id) + ']' + ' ADD CONSTRAINT ' + '[' + f.name + ']'
	+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')' + ' REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,fc.referenced_column_id)+');'
	FROM    [sys].[foreign_keys] AS [f]
	INNER JOIN [sys].[foreign_key_columns] AS [fc]
	ON      [f].OBJECT_ID = [fc].constraint_object_id ;
PRINT 'Preparing FK Drop / Create Commands..... [Completed].'
 
PRINT 'Dropping FK Constraints.....'
	DECLARE @myCurId INT ;
	SET     @myCurId = 1 ;
	DECLARE @myMaxId INT ;
	SELECT  @myMaxId = MAX([RowId]) FROM [##FKCommands_Drop] ;
	DECLARE @myCmdText VARCHAR(2000) ;
	SET     @myCmdText = '' ;
 
	WHILE @myCurId <= @myMaxId
		BEGIN
			SELECT  @myCmdText = [CmdText] FROM [##FKCommands_Drop] WHERE [RowId] = @myCurId ;
			EXECUTE(@myCmdText) ;
			SET @myCurId = @myCurId + 1 ;
		END
PRINT 'Dropping FK Constraints..... [Completed].'
 
PRINT 'Truncating All Data.....'
	EXECUTE sp_msforeachtable 'TRUNCATE TABLE ? ;' ;
PRINT 'Truncating All Data..... [Completed].'
 
PRINT 'Making Needed Changes.....'
-- Insert logic here for DDL changes, etc.
PRINT 'Making Needed Changes..... [Completed].'
 
PRINT 'Creating FK Constraints.....'
	SET     @myCurId = 1 ;
	SELECT  @myMaxId = MAX([RowId]) FROM [##FKCommands_Create] ;
	SET     @myCmdText = '' ;
 
	WHILE @myCurId <= @myMaxId
		BEGIN
			SELECT  @myCmdText = [CmdText] FROM [##FKCommands_Create] WHERE [RowId] = @myCurId ;
			EXECUTE(@myCmdText) ;
			SET @myCurId = @myCurId + 1 ;
		END
PRINT 'Creating FK Constraints..... [Completed].'   
 
PRINT 'Cleaning Up.....' 
	DROP TABLE [##FKCommands_Drop] ;
	DROP TABLE [##FKCommands_Create] ;
PRINT 'Cleaning Up..... [Completed].'
Creative Commons Attribution 4.0 International License

Delete an ASP.Net Account from Membership Provider

The following is one method for deleting users accounts / information from an ASP.Net Membership Provider database.

Step 1 – Find the account.

1
SELECT * FROM [dbo].[aspnet_Users] WHERE [UserName] LIKE '%TestUser%' ;

FindAspNetUser

Step 2 – Delete the account.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Set the target Username.
DECLARE @myUserName NVARCHAR(256);
SELECT  @myUserName = 'TestUser';
 
-- Find the Application Name for the user.DECLARE @myApplicationName NVARCHAR(256);
SELECT  @myApplicationName = [a].[ApplicationName]
FROM    [dbo].[aspnet_Applications] [a]
WHERE   [a].[ApplicationId] = 
    ( SELECT [u].[ApplicationId]
      FROM   [dbo].[aspnet_Users] [u]
      WHERE  [u].[UserName] = @myUserName ) ;
 
-- Determine how much to delete.  Sum of all bits (15) deletes all user information.
--     1 = aspnet_membership
--     2 = aspnet_userinroles
--     4 = aspnet_profile
--     8 = aspnet_users
DECLARE @myTablesToDeleteFrom INT ;
SELECT  @myTablesToDeleteFrom = 15 ; 
 
-- Delete.  The output from the stored procedure will indicate how many tables information 
--    was removed from.
DECLARE @myNumTablesDeletedFrom INT ;
EXECUTE [dbo].[aspnet_Users_DeleteUser] @myApplicationName
,                                       @myUserName
,                                       @myTablesToDeleteFrom
,                                       @myNumTablesDeletedFrom OUTPUT
;
SELECT [NumberOfTablesDeletedFrom] = @myNumTablesDeletedFrom  ;

DeleteAspNetUser_Result

Creative Commons Attribution 4.0 International License

TFS 2010 Database + Automated Builds = Large Database

The project team was using TFS2010 and running nightly automated builds causing extra growth in their TFS SQL Server database. 

In researching why this database was so large I came across a MSDN blog by Buck Hodges discussing how to clean up test attachment data.

His blog post pointed me to the Test Attachment Cleaner of Visual Studio located here in the visual studio gallery.

The tool works great – and enabled us to reduce the size of the database in question by well over 90%.  The team isn’t doing automated builds at the current time – but if they were – or if they start up again – this tool could be run periodically to keep growth in check.

The Test Attachment Cleaner Tool installed includes some sample settings files (XML) that are placed in the install directory for reference.  Review those samples for to see ways you can use the tool to clean out your database(s).

Test Attachment Cleaner – Sample Command Lines

  1. Get Help
      1
      
      C:\Program Files (x86)\Microsoft\Test Attachment Cleaner\tcmpt.exe
  2. Preview Mode
      1
      
      c:\Program Files (x86)\Microsoft\Test Attachment Cleaner\tcmpt.exe attachmentcleanup /collection:&quot;http://MyTeamProjectCollectionURL&quot; /settingsFile:myScenarioFile.xml /teamProject:MyTeamProject /mode:Preview

After reviewing the results of the preview the command can be executed “for real” by changing the mode to “/mode:Delete”.

Creative Commons Attribution 4.0 International License

TSQL CTE For DNN SiteLog Statistics

DotNetNuke is an open source content management system / application development framework that leverages ASP.NET / Microsoft.NET. The DNN Database schema contains the SiteLog table where all page hits are logged. Every visit to the DNN site results in a row being inserted into the SiteLog table. Depending on how busy the site is this table can get quite large.

SiteLogTableProperties

The image above shows a sample SiteLog table with nearly 2 million rows and over 1 GB in size. In contrast the entire DNN database including the SiteLog table is about 1.4 GB.

Joining the data in the SiteLog table with the Tabs table or better yet the vw_Tabs view allows for the analysis of hit statistics per tab/page. Note that query performance could be greatly improved if appropriate indexes on the SiteLog table existed; particularly on the DateTime and TabId columns. However, since this is a write-heavy table, performance of the site could be affected negatively if additional indexes are added to the SiteLog table. (Remember that every insert to the table would need to update any indexes as well which could be a performance hit depending on how busy the site is.)

Since the SiteLog table is written to for every site hit directly querying the SiteLog table, especially during times where the site is busy, can degrade performance that will manifest in blocking of the query.  Use caution when querying the SiteLog table on a production system. With that background and associated cautions out of the way – let us look at a tsql script that queries the DNN SiteLog using a CTE. (MSDN CTE Documentation)

Three parameters need to be set at the beginning of the script: @BeginDate, @EndDate, and @TabId.  The script starts by creating a temporary table, #HitCount, containing each TabId, along with Hit Count, and Unique Hit Count from the SiteLog table where the DateTime column values are GTE @BeginDate and LT @EndDate.  The CTE that follows can then join on the temporary table instead of the live SiteLog table improving performance.  The CTE utilizes a third parameter, @TabId, to determine the starting point for recursion.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
USE [DotNetNuke]
GO
 
DECLARE @BeginDate DATETIME ;
DECLARE @EndDate DATETIME ;
DECLARE @TopTabId INT ;
 
SET @BeginDate = '01-Oct-2012' ; -- Change this to the desired Begin Date.
SET @EndDate   = '01-Nov-2012' ; -- Change this to the desired End Date.
SET @TopTabId  = 54 ; -- Change this to the desired starting / top Tab Id.
 
SELECT  [sitelog].[TabId] ,
        COUNT([sitelog].[SiteLogId]) AS [HitCount] ,
        COUNT(DISTINCT [sitelog].[UserHostAddress]) AS [UniqueHitCount]
INTO    [#HitCount]
FROM    [dbo].[sitelog] (NOLOCK)
WHERE   [sitelog].[DateTime] >= @BeginDate AND [sitelog].[DateTime] < @EndDate
GROUP BY [sitelog].[TabId]
ORDER BY [sitelog].[TabId] ASC ;
 
WITH    MenuTabs ( [TabId], [TabName], [ParentId], [MenuLevel], [Level], [Title], [TabPath], [HasChildren], [IsVisible], [HitCount], [UniqueHitCount] )
          AS ( SELECT   [VT1].[TabId] ,
                        [VT1].[TabName] ,
                        [VT1].[ParentId] ,
                        0 AS [MenuLevel] ,
                        [VT1].[Level] ,
                        [VT1].[Title] ,
                        [VT1].[TabPath] ,
                        [VT1].[HasChildren] ,
                        [VT1].[IsVisible] ,
                        [H1].[HitCount] ,
                        [H1].[UniqueHitCount]
               FROM     [dbo].[vw_Tabs] [VT1]
                        INNER JOIN [#HitCount] [H1] ON [VT1].[TabId] = [H1].[TabId]
               WHERE    [VT1].[TabId] = @TopTabId
                        AND [VT1].[IsVisible] = 1
               UNION ALL
               SELECT   [VT2].[TabId] ,
                        [VT2].[TabName] ,
                        [VT2].[ParentId] ,
                        [M].[MenuLevel] + 1 ,
                        [VT2].[Level] ,
                        [VT2].[Title] ,
                        [VT2].[TabPath] ,
                        [VT2].[HasChildren] ,
                        [VT2].[IsVisible] ,
                        [H2].[HitCount] ,
                        [H2].[UniqueHitCount]
               FROM     [dbo].[vw_Tabs] [VT2]
                        INNER JOIN [#HitCount] [H2] ON [VT2].[TabId] = [H2].[TabId]
                        INNER JOIN MenuTabs [M] ON [VT2].[ParentID] = [M].[TabId]
               WHERE    [VT2].[IsVisible] = 1
             )
    SELECT  [TabId] ,
            [TabName] ,
            [ParentId] ,
            [Level] ,
            [Title] ,
            [TabPath] ,
            [HasChildren] ,
            [IsVisible] ,
            [HitCount] ,
            [UniqueHitCount]
    FROM    [MenuTabs]
    ORDER BY [TABPATH] ;
 
DROP TABLE [#HitCount] ;

If repeated queries of the data are desired; a better idea may be to offload / transform the data from the SiteLog table periodically into another table where additional indexes can be added. A follow-up post will explore options along these lines.

* Note that my experiences with DNN and the corresponding data structures are related to version 5.x.  Version 6.x is in mainstream release and version 7.0 is in beta at the time of this writing.

“DNN is a leading Web Content Management Platform (or CMS) for Microsoft, powering over 700,000 production web sites worldwide. The flexible DNN open source CMS platform also functions as a web application development framework. Depending on your role within your organization, DNN provides powerful benefits to support your Web initiatives.” http://www.dotnetnuke.com/

Creative Commons Attribution 4.0 International License

Account Lockout Notification – Asp.Net Membership Provider

A while back I was working with an Asp.Net Application that leveraged the Asp.Net Membership Provider as the basis for its security.  The Asp.Net Membership provider has a security mechanism that locks out accounts after a specified / configurable amount of failed logins within a period of time.

While this is good for security – it can be confusing for support / helpdesk staff that take a call from an end user who is having trouble logging into the application – mainly because they are unaware that there is an account lockout situation involved.

In order to give the support / helpdesk staff direction an “after update” trigger on the aspnet_Membership table was written that emails the helpdesk any time an account becomes locked out.  This way when the user calls in they know that the solution starts with unlocking the account.

Here is the code for the trigger.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE TRIGGER [dbo].[trg_aspnet_MembershipAfterUpdate] ON [dbo].[aspnet_Membership]
    AFTER UPDATE
AS
  DECLARE @OldIsLockedOut    BIT ;
  DECLARE @NewIsLockedOut    BIT ;
  DECLARE @EmailRecipients   NVARCHAR(MAX) ;
  DECLARE @EmailCCRecipients NVARCHAR(MAX) ;
  DECLARE @EmailSubject      VARCHAR(100) ;
  DECLARE @EmailContent      VARCHAR(100) ;
  DECLARE @EmailFormat       VARCHAR(20) ;
  DECLARE @EmailImportance   VARCHAR(6) ;
  DECLARE @UserName          NVARCHAR(256) ;
 
  IF UPDATE(IsLockedOut)
    BEGIN
      SET @EmailRecipients   = '' ; -- Email Address Intentionally Removed 
      SET @EmailCCRecipients = '' ; -- Email Address Intentionally Removed 
      SET @EmailFormat       = 'HTML' ;
      SET @EmailImportance   = 'HIGH' ;
 
      SELECT @NewIsLockedOut =  i.IsLockedOut,
             @OldIsLockedOut =  d.IsLockedOut,
             @UserName       =  au.UserName
      FROM   inserted i
      INNER JOIN deleted d on i.UserId = d.UserId
      INNER JOIN dbo.aspnet_Users au on i.UserId = au.UserId ;
 
      IF (@NewIsLockedOut) = 1 AND (@OldIsLockedOut) = 0
        BEGIN
          SELECT @EmailSubject = @@servername + ' - Portal Application Account (' + @UserName + ') Lock Out' ;
          SELECT @EmailContent = 'Portal Application User ' + @username + ' has been locked out.' ;
          EXECUTE msdb.dbo.sp_send_dbmail @recipients      = @EmailRecipients,
                                          @copy_recipients = @EmailCCRecipients,
                                          @subject         = @EmailSubject,
                                          @body            = @EmailContent,
           	                          @body_format     = @EmailFormat,
                                          @importance      = @EmailImportance ;
        END
    END
RETURN
Creative Commons Attribution 4.0 International License

Linked Server Connectivity / Availability

A query that attempts to access data from a linked server intermittently fails. The Linked Server is configured properly and the query that utilizes the Linked Server has been tested and verified previously.

Try / Catch Logic can be used to trap the failure – but wouldn’t it be great if there was a way to be able to test the Linked Server in order to know that it was available before attempting to connect to it?

There Is !  Best of all, unlike other methods I have seen it does not require the use of xp_cmdshell.

Introduced in SQL 2005, sp_testlinkedserver tests the connection to a linked server.  Although the documentation shows that the return code values are 0 (success) or 1 (failure), at this time a failure will always be catastrophic and a return code will not be set.

According to this Microsoft Connect Item, the only update planned for this issue is to fix the documentation to note the behavior.  Ideally the return values would work as indicated – so feel free to go to the Microsoft Connect Site and make your opinion known if you agree.

The sample code below uses sp_testlinkedserver and should work with both the current implementation of the procedure and in the case that the procedure is updated to return the values as currently documented.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
BEGIN TRY
      DECLARE @LSIsAvail BIT ;
      EXECUTE @LSIsAvail = sp_testlinkedserver @servername = N'MyLinkedServer' ;
END TRY
BEGIN CATCH
      -- Logic to handle Linked Server being unavailable.
      PRINT 'Linked Server is Unavailable' ;
      RETURN ;
END CATCH
 
IF @LSIsAvail = 0 -- Successful/Linked Server Okay
   BEGIN
         BEGIN TRY
               -- Select Data from Linked Server
               SELECT *
               FROM   OPENQUERY(MyLinkedServer, 'SELECT [EmployeeId]
                                                 ,      [FirstName]
                                                 ,      [LastName]
                                                 FROM   [dbo].[Employee]
                                                 ;') ;
         END TRY
         BEGIN CATCH
               -- Logic to handle Linked Server query error.
               PRINT 'Linked Server Query Error' ;
               RETURN ;
         END CATCH ;
   END
      ELSE
   BEGIN
      -- Logic to handle Linked Server being unavailable.
      PRINT 'Linked Server is Unavailable' ;
      RETURN ;
   END
Creative Commons Attribution 4.0 International License