SQL Server: Float to Varchar Conversion

Recently, someone asked that, if we convert float value to varchar then result is truncated and what is the best way to get whole value converted into varchar without any truncation.

DECLARE @FloatVal FLOAT

SELECT @FloatVal = 421.765433

First lets check, how SQL Server implicitly converts float value to varchar

DECLARE @VarcharVal VARCHAR(50)

SELECT @VarcharVal = @FloatVal

SELECT @VarcharVal

OOPS. Implicit conversion from float to varchar is NOT according to our required output. Let convert it explicitly and then assign this value to varchar variable.

SELECT @VarcharVal = CAST(@FloatVal AS VARCHAR(50))

SELECT @VarcharVal

You will observe same truncated value as a result of above query. Finally,let’s try it with convert as convert supports some formatting too.

SELECT CONVERT (VARCHAR(50), @FloatVal,128)

 Yeah, that's what we were looking for.

SQL Server: How to Manage Error Log File on Production Server

In one of my early post “How to Avoid Big Single Error Log File on Production Servers”, I have tried to explain that how error log file growth can be controlled to avoid very large files on production servers.

Though the post was very short but response was really great. Out of  received comments, today I would like to share one of SQL Expert Martin C. suggestions on this topic.

“If you are monitoring your log on a daily basis for specific errors then the need to backup and keep old logs simply becomes dependent upon any compliance regulations. If you are not constrained by any compliance (e.g. SOX etc) then once you have examined the logs for any useful messages they become less useful for anything else.
 
You could make use of tools to parse the log entries and store anything significant in a database table so you retain these for later analysis, but messages that regularly appear (such as a backup completed) are really of no further use once you have confirmed the backup was successful especially as you have the information also stored in MSDB.
 
I'd suggest you ensure you have sufficient monitoring and checking of the error logs and perhaps even manually issue sp_cycle_errorlog after you have checked them.”

SQL Server: Working With Date but With Format of Your Own Choice

Today, a blog reader asked that, why he is facing error for following simple varchar to datetime conversion query

SELECT CAST('24/04/2011' AS DATETIME)

Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.      

But same query works fine, once he changes date and month position in given text.

SELECT CAST('04/24/2011' AS DATETIME)

By default SQL Server expects date in MDY or it works fine with MYD and YMD. And if your date format is other then above three formats, then you have to inform SQL Server about your date format by using SET statement.

SET DATEFORMAT dmy

SELECT CAST('24/04/2011' AS DATETIME)

SQL Server: How to Read Trace File to Detect Database Detachment

On of our production server contains more then 200 databases. Few of them are rarely used but still required. Few days back, someone from DBA’s  team accidentally detached on of less used database. But once we need it we got error as there was no required database on server.

To find out, that when and who detached this database we have quickly executed a simple script.

First get current trace file name from sys.traces table

SELECT * FROM sys.traces

GO

Then copy trace file name and assign it @trace_file parameter and execute following script.

DECLARE @trace_file NVARCHAR(500)

SELECT  @trace_file = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_88.trc'

SELECT  *

FROM    [fn_trace_gettable](@trace_file, DEFAULT)

WHERE   TextData LIKE '%DETACH%'

ORDER BY starttime DESC

SQL Server: NULL As Nothing vs NULL As String Value in SSMS

While loading data from Flat Files, I have found that few column values are inserted as ‘NULL’ and client’s application crashed. WHY ?.  Because ‘NULL’ as nothing and NULL as character value are two different things. I found one more blessing of SQL Server 2008. That in SQL Server Management Studio 2008, it shows NULL values in result set with different color. How it helps us in real life, let’s find it with a simple query which we will execute on both SQL Server 2008 and SQL Server 2005.

USE AdventureWorks
GO
SELECT  DocumentID,
Title,
DocumentSummary
FROM    Production.Document

Here is result set from SQL Server 2008

And following result set is from SQL Serve 2005.

In SQL Server 2005, from result set, you can’t say which rows contains NULL (as nothing) values and which contains ‘NULL’ (as character). Lets update one of row in above table on both versions.

USE AdventureWorks
GO
UPDATE Production.Document
SET DocumentSummary = 'NULL'
WHERE DocumentID = 9

Execute select query on both sides and have a look on result-set. In SQL Server 2005, you can’t differentiate which NULL indicates nothing and which NULL are character values, but in SQL Server 2008 you can easily identify such data.

 

I have learned a lesson from this incident and now,to avoid such discrepancies, I like to execute following script to avoid both, NULL as character value problem and extra spaces problem, once I have loaded data in my target tables.

DECLARE @SqlText VARCHAR(1000)
DECLARE MY_CURSOR Cursor FAST_FORWARD
FOR SELECT  '[' + sc.name + '].[' + tb.name + ']' AS TableName,
cl.name AS ColumnName
FROM    sys.columns cl
INNER JOIN sys.tables tb ON cl.object_id = tb.object_id
INNER JOIN sys.schemas sc ON tb.schema_id = sc.schema_id
WHERE   tb.type = 'U'
AND cl.system_type_id IN ( 167, 175, 231, 239 )
AND cl.is_computed = 0
AND sc.principal_id = 1
ORDER BY TableName
/*
varchar = 167
nvarchar= 231
char = 175
nchar = 239
*/
OPEN My_Cursor
DECLARE @TableName VARCHAR(500),
@ColumnName VARCHAR(500)
Fetch NEXT FROM MY_Cursor INTO @TableName, @ColumnName
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT  @SqlText = 'UPDATE ' + @TableName + ' SET [' + @ColumnName
+ '] = CASE LTRIM(RTRIM([' + @ColumnName
+ ']))
WHEN ''NULL'' THEN NULL ELSE LTRIM(RTRIM(['
+ @ColumnName + '])) END'
EXEC ( @SqlText )                                                                      
FETCH NEXT FROM MY_CURSOR INTO @TableName, @ColumnName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

SQL Server: Sorting Data in Ascending Order but NULL at the End

Sometime a very simple task becomes challenging for a developer. A friend of mine, who is a talented database developer, asked me that “He needs to sort his query output rows (as we normally do) but he need to push NULL values at the end”.  To be very frank, first I laughed at his question as I thought it’s a simple ORDER BY clause case, but when I tried it, I failed in first attempt ;).

Anyhow, here is a simple solution of sorting any column in ascending order but with NULL at the end.

-- Create temporary
CREATE TABLE #TestSort (RecID INT, RecDesc VARCHAR(50),RecDate DATETIME)
-- Insert rows
INSERT INTO #TestSort
SELECT 1,'FirstRec','2001-07-01 00:00:00'
UNION ALL
SELECT 2,NULL,'2001-08-01 00:00:00'
UNION ALL
SELECT NULL,'ThirdRec',NULL
UNION ALL
SELECT 0,'ForthRec',NULL
UNION ALL
SELECT 5,NULL,'2001-11-01 00:00:00'
GO
---- Sort by INT column "RecID"
SELECT * FROM #TestSort
ORDER BY CASE  WHEN RecID IS NULL  THEN 1 ELSE 0 END,RecID
--- Sort by VARCHAR column "RecDesc"
SELECT * FROM #TestSort
ORDER BY CASE  WHEN RecDesc IS NULL  THEN 1 ELSE 0 END,RecDesc
--- Sort by DATETIME column "RecDate"
SELECT * FROM #TestSort
ORDER BY CASE  WHEN RecDate IS NULL  THEN 1 ELSE 0 END,RecDate
 
-- Drop table when not required
DROP TABLE  #testsort

SQL Server Basics: TO_DATE() Function in SQL Server

Dealing with data conversion functions in SQL Server, database developers with Oracle or Java background usually ask one same question “is there any equivalent function TO_DATE() to convert  string into datetime or to get different formats of date ?” .

There is no TO_DATE() function in SQL Server.

Equivalent functions are CAST() and CONVERT().  Where CONVERT() is more rich function and mostly used to get different styles of datetime data.

Oracle:                SELECT to_date('01-JAN-2009') FROM dual

SQL Server:          SELECT CAST('01-JAN-2009' AS DATETIME)

Or you can use      SELECT CONVERT(DATETIME,'01/JAN/2009')

To get output in different formats you can use CONVERT() function as follow.

DECLARE @MyDate DATETIME

SELECT @MyDate = '01/JAN/09'

SELECT CONVERT(VARCHAR(20),@MyDate,106)

106 is style number. Get top 20 commonly used datetime formats here



************************************************************

Recommended Posts

• SQL Server Denali: Get Date/Time from Parts 

• SQL Server: 20 Most Commonly Used DateTime Formats 

• SQL Server: Data Type DATETIME or Only DATE

 

 

SQL Server: LEN() or DATALENGTH()

According to few readers of my Previous post “Misconceptions about CHAR datatype” Len() is not a perfect function to get data length  as it skips trailing spaces. Meaning if we have string like ‘ABC  ‘ (SELECT LEN('ABC  ')), LEN() function will return 3 instead of 5 as result because it excludes trailing blanks.

I remember, when one my of colleague was importing SSN (Social Security Numbers) from one table to other but with following query he verified that each SSN is in correct 9 digit formats

SELECT MAX(LEN(SSN)) FROM SSNTable

On executing above query, he got 9 as output (which was a green signal) but when he tried to import, query failed with error because targeted table had SSN column with only 9 character space allowed and there were rows which had more then 9 character of length.

On further investigation he found that few rows contains spaces at the end of actual data which were ignored by LEN() function.

(NOTE: spaces at start like ' ABC' are not ignored by LEN() function)

For such situations you can use, DATALENGTH() function but it never returns length of a string but number of bytes a data is taking. 
As according to BOL “Returns the number of bytes used to represent any expression”. That is why our previous query with DATALENGTH() function SELECT DATALENGTH('ABC  ') will return 5 as output.

But one keep in mind that if string is of NVARCHAR then  SELECT DATALENGTH(CAST('ABC  ' AS NVARCHAR(50)) WILL RETURN 8 AS 1 varchar character holds two bytes. 

SQL Server: A Query Slow in Application but Fast in SSMS

If someone is somehow related to SQL Server, he/she has must faced this common question that “I have query/stored procedure which is slow in the Application, but Fast in SSMS?” Most of us blame parameter sniffing for this problem but there are other factors, which can create this situation.

I think most authenticated and cleanly written article in response to this commonly asked question is Slow in the Application, Fast in SSMS? Understanding Performance Mysteries, written by Erland Sommarskog (MVP).

SQL Server: Two Misconceptions about CHAR Data type

Today I would like to share two very simple but interesting interview (DBA & Developer) questions, for which I found, most of the candidates confused. These questions are

• If I have a table with a column named ColA of data type CHAR(10), and each cell of this column contains same value that is ‘ABC’, what will the output of

SELECT MAX(LEN(ColA)) FROM mytable

• If above mentioned table have 10 rows, How many rows I will get as output of following query

                        SELECT * FROM mytable

                  WHERE ColA+'1' = 'ABC1'

First question is very simple but I found few candidates confused as they think that CHAR and NCHAR data type columns  takes same space, and length of actual data doesn’t matter (which is true). So if it takes same space, LEN() function will also return column length i.e. 10 in our case (which is false).

First query will return 3 as output. Because LEN() function returns length of actual data.

For second question I hardly get the correct answer and almost every candidate said it will return all ten rows, because ColA have same value for every row i.e. ‘ABC’ and concatenating it with ‘1’, result will be ‘ABC1’ and that is equal to our given condition in WHERE clause (Which is false).

 Second query will return 0 rows. Because when we concatenate a column which has datatype CHAR or NCHAR, it comes with all of its length (i.e. length of CHAR or NCHAR column). In our case it was CHAR(10) and every column contains same value i.e. ‘ABC’ so when it will be concatenated with ‘1’ it will become ‘ABC       1’ i.e. ABC with remaining 7 spaces (3+7=10) and then ‘1’.

Use following code for verifications.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'TestTable' AND type = 'U')

CREATE TABLE [dbo].TestTable(

      [ColA] [CHAR] (10))

GO

--- Insert records     

INSERT INTO dbo.TestTable

SELECT 'ABC'

GO 10

--First Query

SELECT MAX(LEN(ColA)) AS CharColumnLength FROM dbo.TestTable

GO

--Second Query

SELECT * FROM dbo.TestTable

      WHERE ColA+'1' = 'ABC1'

GO

DROP TABLE dbo.TestTable

SQL Server Fun: A DBA’s Joke

Few days back, I received a call from a DBA friend of mine, who asked for urgent help, as logical reads for one of their production database query became extra ordinary high. First I asked him to send me a screen shot for an overview. He sent me a screen shot (just like) following one.

Once I received screen shot, I was totally shocked and speechless. How can a query have such a huge value of logical reads? 92112403 page reads mean almost 700 GB of  data scanning for only 288 output rows. Thanks God I asked him to share his screen and re-executing same query myself, I found that it was a joke.

Actually message part of query output in SQL Server Management Studio is editable. Execute any query and in output area click on “Messages” tab, click any where and change any message ;). Like in given screen shot, actual logical read value for marked area was just 9 and edited intentionally just to celebrate 1^st April.

 

SQL Server: Script to Create Replica of Existing Database User

Today, I would like to share a script, which is helpful to create a clone of an existing database user, with all of its rights/permissions and securables. This script was originally written by GREG LARSEN for SQL Server 2000, I just made necessary changes so it can be used for SQL Server 2008.
(To Copy this script, first past in MS Word document file and then copy to SQL Server)

CREATE PROCEDURE USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN
@OLDLOGIN VARCHAR(200), -- EXISTING LOGIN NAME (COPY FROM)
@NEWLOGIN VARCHAR(200), -- NEW LOGIN NAME (COPY TO)
@NEWUSER VARCHAR(128), -- NEW USER NAME 
@PASSWORD VARCHAR(200) = '', -- PASSWORD FOR SQL SERVER AUTHENTICATED USERS
@CREATE_SCRIPT_ONLY BIT = 1 -- 1 IF TO GET ONLY TSQL SCRIPT, 0 IF WANT TO CREATE USER DIRECTLY 
AS -- 

DECLARE @INDX INT 
SET NOCOUNT ON 
DECLARE @TEXT CHAR(100) 
DECLARE @CNT INT
DECLARE @CMD NVARCHAR(200)
DECLARE @DB NVARCHAR(128) 
DECLARE @OLDUSER VARCHAR(100)
-- Temp Table to hold generated commands
CREATE TABLE #TMP_LOGIN_RIGHTS ( RIGHTS_TEXT VARCHAR(MAX) )
----------------------------------------------------------------------------- 
-- Check if given OldLogin exists
SELECT  @CNT = COUNT(*)
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @OLDLOGIN
IF @CNT = 0 
BEGIN
RAISERROR ( '@OLDLOGIN IS NOT A VALID USER OF SQL SERVER', 16, 1 )
RETURN
END
-- Check if given NewUser allready exists on server
SELECT  @CNT = COUNT(*)
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @NEWLOGIN
--
-- IF @NEWLOGIN EXIST ABORT
IF @CNT > 0 
BEGIN
RAISERROR ( '@NEWLOGIN ALREADY EXISTS ON SQL SERVER', 16, 1 ) 
RETURN
END
-- IF @NEWLOGIN CONTAINS '\' THEN NT LOGIN
SELECT  @INDX = CHARINDEX('\', @NEWLOGIN)
IF @INDX > 0 
-- GENERATE COMMANDS TO ADD NT USER
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [MASTER].[DBO].[SP_GRANTLOGIN] ''' + @NEWLOGIN
+ '''' + CHAR(13)
+ 'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] '''
+ @NEWLOGIN + ''',''' + RTRIM(DBNAME) + '''' AS RIGHTS_TEXT
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @OLDLOGIN
ELSE 
BEGIN
IF @PASSWORD = '' 
BEGIN
RAISERROR ( '@PASSWORD MUST BE SPECIFIED FOR SQL SERVER AUTHENTICATION',
16, 1 ) 
RETURN
END -- 
-- GENERATE COMMANDS TO ADD SQL SERVER AUTHENTICATION USER
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [MASTER].[DBO].[SP_ADDLOGIN] '''
+ @NEWLOGIN + ''',''' + @PASSWORD + '''' + CHAR(13)
+ 'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] '''
+ @NEWLOGIN + ''',''' + RTRIM(DBNAME) + '''' AS RIGHTS_TEXT
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @OLDLOGIN
END
-------------------------------------------------------------------------------
SET NOCOUNT ON 
SET @CMD = '[MASTER].[DBO].[SP_HELPUSER]'
-- GET THE NAME OF ALL DATABASES
DECLARE ALLDATABASES CURSOR
FOR SELECT  NAME
FROM    [MASTER].[DBO].[SYSDATABASES] 
OPEN ALLDATABASES
FETCH NEXT FROM ALLDATABASES INTO @DB
-- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE
CREATE TABLE #TMPUSERS
(
USERNAME VARCHAR(100),
GROUPNAME VARCHAR(100),
LOGINNAME VARCHAR(100),
DEFDBNAME VARCHAR(100),
USERID CHAR(10),
SCHEMANAME VARCHAR(100),
SUSERID SMALLINT
)
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- COMMAND TO RETURN ALL USERS IN DATABASE
SET @CMD = '[' + @DB + ']' + '.[DBO].[SP_HELPUSER]'
-- GET ALL USERS IN DATABASE INTO TEMPORARY TABLE
INSERT  INTO #TMPUSERS (USERNAME,GROUPNAME,LOGINNAME,DEFDBNAME,
USERID,SCHEMANAME,SUSERID)
EXEC ( @CMD
)
-- DETERMINE WHETHER OLD USER IS IN DATABASE
SELECT  @CNT = COUNT(*)
FROM    #TMPUSERS
WHERE   LOGINNAME = @OLDLOGIN
-- IF OLD USER IS IN DATABASE THEN ADD NEW USER TO DATABASE
IF @CNT > 0 
BEGIN
-- DETERMINE IF @NEWUSER ALREADY EXIST IN DATABASE
SELECT  @CNT = COUNT(*)
FROM    #TMPUSERS
WHERE   USERNAME = @NEWUSER
-- IF USER EXIST ABORT
IF @CNT > 0 
BEGIN
-- CLOSE AND DEALLOCATE CURSOR OF DATABASES SO NEXT TIME AROUND NO ERROR OCCURS
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
-- SET TEXT OF ERROR MESSAGE
SET @TEXT = '@NEWUSER ALREADY EXIST IN DATABASE '
+ @DB
-- RAISE ERROR AND RETURN
RAISERROR ( @TEXT, 16, 1 )
RETURN
END
-- GENERATE COMMAND TO ADD @NEWLOGIN TO CURRENT DATABASE
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [' + @DB
+ '].[DBO].[SP_GRANTDBACCESS] '''
+ @NEWLOGIN + ''',''' + RTRIM(@NEWUSER)
+ '''' AS RIGHTS_TEXT
FROM    ( SELECT DISTINCT
USERNAME,
LOGINNAME
FROM      #TMPUSERS
WHERE     LOGINNAME = @OLDLOGIN
) A
END
-- TRUNCATE TABLE FOR NEXT DATABASE
TRUNCATE TABLE #TMPUSERS
-- GET NEXT DATABASE
FETCH NEXT FROM ALLDATABASES INTO @DB
END -- WHILE (@@FETCH_STATUS = 0)
-- CLOSE CURSOR OF DATABASES
CLOSE ALLDATABASES
--------------------------------------------------------------------------------
OPEN ALLDATABASES
FETCH NEXT FROM ALLDATABASES INTO @DB
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- SET COMMAND TO FIND USER PERMISSIONS HAS IN CURRENT DATABASE
SET @CMD = '[' + @DB + '].[DBO].[SP_HELPUSER]'
-- EMPTY TEMPORARY TABLE #TMPUSERS
TRUNCATE TABLE #TMPUSERS
-- GET USER PERMISSIONS FOR ALL USERS IN CURRENT DATABASE
INSERT  INTO #TMPUSERS (USERNAME,GROUPNAME,LOGINNAME,DEFDBNAME,USERID,
SCHEMANAME,SUSERID)
EXEC ( @CMD
)
-- DETERMINE WHETHER THE OLD USER IS IN A ROLE
SELECT  @CNT = COUNT(*)
FROM    #TMPUSERS
WHERE   LOGINNAME = @OLDLOGIN
AND GROUPNAME <> 'PUBLIC'
-- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT ROLE
IF @CNT > 0
-- GENERATE COMMANDS TO ADD @NEWUSER TO APPROPRIATE ROLES IN CURRENT DATABASE
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT DISTINCT
'EXECUTE [' + @DB
+ '].[DBO].[SP_ADDROLEMEMBER] '''
+ RTRIM(A.GROUPNAME) + ''',''' + RTRIM(@NEWUSER)
+ '''' AS RIGHTS_TEXT
FROM    #TMPUSERS A
WHERE   A.LOGINNAME = @OLDLOGIN
AND A.GROUPNAME <> 'PUBLIC' 
-- GET NEXT DATABASE
FETCH NEXT FROM ALLDATABASES INTO @DB
END -- WHILE (@@FETCH_STATUS = 0)
CLOSE ALLDATABASES
DROP TABLE #TMPUSERS
-----------------------------------------------------------------------------
-- CREATE TABLE TO HOLD SERVER ROLES
CREATE TABLE #TMPSRVROLES
(
SERVERROLE VARCHAR(100),
MEMBERNAME VARCHAR(100),
MEMBERSID VARBINARY(85)
)
-- COMMAND TO GET SERVER ROLES
SET @CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'
-- GET SERVER ROLES INTO TEMPORARY TABLE
INSERT  INTO #TMPSRVROLES
EXEC ( @CMD
)
-- DETERMINE WHETHER THE OLD USER IS IN A SERVER ROLE
SELECT  @CNT = COUNT(*)
FROM    #TMPSRVROLES
WHERE   MEMBERNAME = @OLDLOGIN
-- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT SERVER ROLE
IF @CNT > 0
-- GENERATE COMMANDS TO ADD @NEWLOGIN INTO THE APPROPRIATE SERVER ROLES
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [MASTER].[DBO].[SP_ADDSRVROLEMEMBER] ' + ''''
+ RTRIM(@NEWLOGIN) + '''' + ',[' + RTRIM(A.SERVERROLE)
+ ']' AS RIGHTS_TEXT
FROM    #TMPSRVROLES A
WHERE   A.MEMBERNAME = @OLDLOGIN 
-- DROP SERVER ROLE TABLE
DROP TABLE #TMPSRVROLES
-- GRANT USER PERMISSIONS TO OBJECTS AND STATEMENTS
-------------------------------------------------------------------------------
-- CREATE TEMPORARY TABLE TO HOLD INFORMATION ABOUT OBJECTS PERMISSIONS
CREATE TABLE #TMPPROTECT
(
OWNER VARCHAR(100),
OBJECT VARCHAR(100),
GRANTEE VARCHAR(100),
GRANTOR VARCHAR(100),
PROTECTTYPE CHAR(10),
ACTION VARCHAR(20),
COLUMNX VARCHAR(100)
)
OPEN ALLDATABASES
SET @CMD = 'SELECT @OLDUSER=NAME FROM [' + @DB
+ '].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM [MASTER].[DBO].[SYSLOGINS] 
WHERE LOGINNAME = '
+ CHAR(39) + @OLDLOGIN + CHAR(39) + ')' 
FETCH NEXT FROM ALLDATABASES INTO @DB
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- INITIALIZE @OLDUSER VARIABLE 
SET @OLDUSER = '' 
--GENERATE COMMAND TO SET @OLDUSER TO THE USER NAME IN DATABASE 
-- IF @OLDLOGIN HAS ACCESS TO CURRENT DATABASE
SET @CMD = 'SELECT @OLDUSER=NAME FROM [' + @DB
+ '].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM [MASTER].[DBO].[SYSLOGINS] 
WHERE LOGINNAME = '
+ CHAR(39) + @OLDLOGIN + CHAR(39) + ')' 
-- EXECUTE COMMAND TO SET @OLDUSER TO THE USER NAME IN DATABASE 
-- IF @OLDLOGIN HAS ACCESS TO CURRENT DATABASE
EXEC [MASTER].[DBO].[SP_EXECUTESQL] @CMD,
N'@OLDUSER CHAR(200) OUTPUT', @OLDUSER OUT
-- IF @OLDUSER IS NOT BLANK THEN @OLDLOGIN HAS ACCESS TO CURRENT DATABASE
IF @OLDUSER <> '' 
BEGIN
-- GENERATE COMMAND TO GET OBJECT PERMISSIONS FOR CURRENT DATABASE
SET @CMD = '[' + @DB + '].[DBO].[SP_HELPROTECT]'
-- GET OBJECT PERMISSIONS INTO TEMPORARY TABLE
INSERT  INTO #TMPPROTECT
EXEC ( @CMD
)
-- DETERMINE IF THERE ARE ANY OBJECT PERMISSIONS FOR @OLDUSER
SELECT  @CNT = COUNT(*)
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER 
IF @CNT > 0 
-- SWITCH TO THE APPROPRIATE DATABASE
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'USE [' + @DB + ']'
-- GENERATE COMMANDS TO GRANT OBJECTS PERMISSIONS FOR REFERENCES, SELECT, UPDATE TO @NEWUSER
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
+ ' WITH GRANT OPTION'
ELSE 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT <> '.'
AND COLUMNX = '(ALL+NEW)'
-- GRANT COLUMN PERMISSION ON OBJECTS
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + ']([' + COLUMNX
+ '])' + ' TO ['
+ RTRIM(@NEWUSER) + ']'
+ ' WITH GRANT OPTION'
ELSE 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + ']([' + COLUMNX
+ '])' + ' TO ['
+ RTRIM(@NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT <> '.'
AND COLUMNX <> '(ALL+NEW)'
AND COLUMNX <> '.'
-- GRANT INSERT, DELETE, AND EXECUTE PERMISSION ON OBJECTS
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
+ ' WITH GRANT OPTION'
ELSE 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT <> '.'
AND ACTION IN ( 'INSERT', 'DELETE',
'EXECUTE' )
AND COLUMNX = '.'
-- GRANT STATEMENT PERMISSIONS
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'GRANT ' + ACTION + ' TO ['
+ RTRIM(@NEWUSER) + ']' AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT = '.'
-- REMOVE RECORDS FOR TEMPORARY TABLE IN PREPARATION FOR THE NEXT DATABASE TO BE PROCESSES
TRUNCATE TABLE #TMPPROTECT
END
-- GET NEXT DATABASE TO PROCESS
FETCH NEXT FROM ALLDATABASES INTO @DB
END -- WHILE (@@FETCH_STATUS = 0)
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
-- DROP TEMPORARY TABLE THAT HELD OBJECT PERMISSIONS
DROP TABLE #TMPPROTECT
---------------------------------------------------------------------------------
-- GET ALL THE GENERATED COMMANDS
DECLARE COMMANDS CURSOR
FOR SELECT  *
FROM    #TMP_LOGIN_RIGHTS
OPEN COMMANDS
FETCH NEXT FROM COMMANDS INTO @CMD
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF @CREATE_SCRIPT_ONLY = 1
PRINT @CMD
ELSE
EXEC (@CMD)
FETCH NEXT FROM COMMANDS INTO @CMD
END 
CLOSE COMMANDS
DEALLOCATE COMMANDS
--DROP TEMPORARY TABLES
DROP TABLE #TMP_LOGIN_RIGHTS