Home » Miscellaneous » SQL Scripts

Category Archives: SQL Scripts

SQL Trigger: Disabled User Notification

Paste this code into a New Query window in SQL Management Studio, select your iManage database and click the Execute button.  The trigger will be created on the mhgroup.DOCUSERS table and then any time the Allow Login setting is changed to N (a user in the mhgroup.DOCUSERS table gets disabled), an email notification will be delivered to the email account specified in the code.

EMAIL

NOTE:  This trigger requires SQL Database Mail to be configured first so that e-mail can be sent from the SQL Server.  

Text in bold shown below needs to be customized for your environment.

USE [YOURDATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE trigger [MHGROUP].[DISABLED_USER_NOTIFICATION]
ON [MHGROUP].[DOCUSERS]
AFTER UPDATE
AS

IF UPDATE(LOGIN)

BEGIN

SET NOCOUNT ON;

DECLARE @OLDVALUE char(1)
DECLARE @NEWVALUE char(1)
DECLARE @USER nvarchar(64)
DECLARE @FULLNAME nvarchar(254)
DECLARE @ToEmail varchar(255)
DECLARE @body varchar(max)
DECLARE @DBNAME varchar(20)

SELECT @OLDVALUE = DELETED.LOGIN from deleted
SELECT @NEWVALUE = INSERTED.LOGIN from inserted
SELECT @USER = DELETED.USERID from deleted
SELECT @FULLNAME = DELETED.FULLNAME from deleted
SELECT @DBNAME = DB_NAME()
SELECT @ToEMail = ‘USER@TOBENOTIFIEDDOMAIN.COM
SELECT @body = ‘The following user in the ‘+ UPPER(@DBNAME)+ ‘ database has been disabled:’ + char(10)+char(13) + UPPER(@USER) + ‘ – ‘ + @FULLNAME

IF @OLDVALUE<>@NEWVALUE
BEGIN
IF @NEWVALUE=’N’
BEGIN
EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘YOURSQLDATABASEMAILPROFILE‘,
@recipients = @ToEmail,
@subject = ‘iManage User Account – DISABLED!’,
@body = @body

END
END
END

Restored database to a new server and can’t login to it

It’s most likely because the SQL User account is what is known as an “Orphaned User”

The account exists on the database, but not on the SQL Server.  Add the account to SQL first.  You will get an error trying to save the new account.  Then run this script on the iManage database.

SP_CHANGE_USERS_LOGIN UPDATE_ONE,’MHGROUP’, ‘MHGROUP’

How to search all columns of all tables in a database for a keyword?

This stored procedure accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database.

The output of this stored procedure contains two columns:

1) The table name and column name in which the search string was found

2) The actual content/value of the column (Only the first 3630 characters are displayed)

Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait.

Create this procedure in the required database:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

— Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
— Purpose: To search all columns of all tables for a given search string
— Written by: Narayana Vyas Kondreddi
— Site: http://vyaskn.tripod.com
— Tested on: SQL Server 7.0 and SQL Server 2000
— Date modified: 28th July 2002 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ”
SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ”
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))
FROM         INFORMATION_SCHEMA.TABLES
WHERE                 TABLE_TYPE = ‘BASE TABLE’
AND        QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName
AND        OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)
), ‘IsMSShipped’
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM         INFORMATION_SCHEMA.COLUMNS
WHERE                 TABLE_SCHEMA        = PARSENAME(@TableName, 2)
AND        TABLE_NAME        = PARSENAME(@TableName, 1)
AND        DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)
AND        QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
‘SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630)
FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

EXAMPLE:  To search all columns of all tables in Pubs database for the keyword “Computer”

EXEC SearchAllTables ‘Computer’

GO