March 2, 2010 at 2:58 am
USE [master]
GO
CREATE PROC [dbo].[ap_adm_OrphanUsersFix]
AS
SET NOCOUNT ON
DECLARE @cmd varchar(4000)
BEGIN TRY
Create table #Windows_Auth_Orphan_User
(
[Str] nvarchar(300)
)
DECLARE @DBCount INT,@MaxCount INT, @Qry nvarchar(4000), @DBName sysname
DECLARE @db_list table (dbname nvarchar(100),ID int identity)
SET @Qry = ''
SET @DBCount = 1
INSERT INTO @db_list(dbname )
SELECT name FROM sys.sysdatabases
WHERE dbid > 4
SELECT @MaxCount = MAX(ID) FROM @db_list
----Fixing Windows autheticated user-----------------
SET @DBCount = 1
WHILE(@DBCount < = @MaxCount )
BEGIN
SELECT @DBName = ''+ dbname + '' FROM @db_list WHERE id = @DBCount
SET @Qry = 'SELECT '' USE [' + @DBName + ' ];
ALTER USER ['' + NAME + ''] WITH LOGIN = [ '' + NAME + '']''
FROM sys.database_principals
WHERE ( type_desc = ''WINDOWS_GROUP'' OR type_desc = ''WINDOWS_USER'' )
AND name NOT like ''%dbo%'' AND name NOT LIKE ''%#%'''
INSERT INTO #Windows_Auth_Orphan_User EXEC (@Qry)
SET @DBCount = @DBCount + 1
END
--SELECT * FROM #Windows_Auth_Orphan_User
DECLARE MC CURSOR READ_ONLY FOR
SELECT [Str]FROM #Windows_Auth_Orphan_User
OPEN MC
FETCH NEXT FROM MC INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
print @cmd
Execute (@cmd)
END
FETCH NEXT FROM MC INTO @cmd
END
CLOSE MC
DEALLOCATE MC
DROP Table #Orphan_User_Tbl
Drop table #Windows_Auth_Orphan_User
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
RETURN
END CATCH
Above SP, fix all the orphan windows_user or windows_group but if sometimes any user got failed, then it will be catched in catch block.
and it wil not resume further execution for remaining orphan users.
how can i make the code to work for remaining users, when it will be fell into catch block ( will "RETURN" work there?) ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 2, 2010 at 4:59 am
First I have to admit that I didn’t check the code, so there are maybe other issues that I didn’t notice. As for your question, you can use nested catch try blocks. If you’ll put only the line Execute (@cmd) in side its own try catch block and the dynamic SQL will fail, the code will continu to run the rest of the loop.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 2, 2010 at 7:38 am
DECLARE MC CURSOR READ_ONLY FOR
SELECT [Str]FROM #Windows_Auth_Orphan_User
OPEN MC
FETCH NEXT FROM MC INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
print @cmd
Execute (@cmd)
END
FETCH NEXT FROM MC INTO @cmd
END
CLOSE MC
DEALLOCATE MC
Above code is taken from my first post window. let says we have 5 records for above cursor
while executed by cursor, it got error in 2nd records;
it should send to catch box and then throw message by
SELECT ERROR_NUMBER()ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
(which is working fine)
and should go back to cursor and start working on remaining records( for 3, 4 and 5 records).
but my code got stopped after throwing error message:crying:.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 2, 2010 at 8:17 am
Adi already answered your question. Did you do what he said?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 2, 2010 at 8:29 am
And you know, this would all be a lot easier and simpler if you did this in a set-based way, instead of using a CURSOR and WHILE loops.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 2, 2010 at 10:12 pm
RBarryYoung (3/2/2010)[hrthis would all be a lot easier and simpler if you did this in a set-based way
I didnt get you here ? any example ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 3, 2010 at 8:17 am
RBarryYoung (3/2/2010)
And you know, this would all be a lot easier and simpler if you did this in a set-based way, instead of using a CURSOR and WHILE loops.
This is quite a tricky one to do set-based, and there's no performance advantage. Why bother?
I can write the dynamic SQL and cursor version in ten minutes - I'd have to think hard about a set-based version.
Just interested.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 3, 2010 at 11:02 pm
DECLARE MC CURSOR READ_ONLY FOR
SELECT [Str]FROM #Windows_Auth_Orphan_User
OPEN MC
FETCH NEXT FROM MC INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN TRY
PRINT @cmd
Execute (@cmd)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()ErrorNumber,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
FETCH NEXT FROM MC INTO @cmd
CONTINUE;
END CATCH
FETCH NEXT FROM MC INTO @cmd
END
CLOSE MC
DEALLOCATE MC
I made it and it worked for me. but not sure wether it is optimal or not ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 4, 2010 at 4:44 am
Paul White (3/3/2010)
RBarryYoung (3/2/2010)
And you know, this would all be a lot easier and simpler if you did this in a set-based way, instead of using a CURSOR and WHILE loops.This is quite a tricky one to do set-based, and there's no performance advantage. Why bother?
I can write the dynamic SQL and cursor version in ten minutes - I'd have to think hard about a set-based version.
It's really not that hard with the right tools. Here's one that I've been using for a while:
/*
EXECute a string of T-SQL commands over the contents
of a set, replacing token strings with column values as
indicated. The set is defined by the @FROM parameter.
Optionally allows for printing the final execution string,
switching databases, and user-specified TRY-CATCH handling.
Also allows the caller to switch quote characters, to ease
quote-doubling problems.
RBarryYoung Proactive Performance Solutions, Inc.
*/
ALTER PROC
OVER_SET (
@command AS NVARCHAR(MAX), -- Template SQL command
@from AS NVARCHAR(MAX), -- FROM..WHERE clause string
@subs1 AS NVARCHAR(MAX) = N'', -- Substitution parameters, these are
@subs2 AS NVARCHAR(MAX) = N'', -- of the form "<find>=<repl>" where:
@subs3 AS NVARCHAR(MAX) = N'', -- <find> will be searched for in @command, and
-- <repl> will replace it, if it was found
-- (typically, <repl> should be a column name
-- returned by the FROM clause)
@print AS BIT = 1, -- 0 = suppress PRINT of the SQL before executing
@catch AS VARCHAR(12) = 'continue',
-- TRY/CATCH option parameters. Choices are:
-- 'continue' on an error, print a message & continue
-- 'ignore' attempt to suppress all errors
-- 'raise' try to re-raise the error
-- 'none' no TRY/CATCH blocks
@use_db AS NVARCHAR(255) = N'', -- DB to switch to befor execution of the SQL text
@quote AS NVARCHAR(8) = N'' -- search for this character & replace with (').
)
AS
--
DECLARE @QT AS NVARCHAR(1), @cr AS NVARCHAR(1);
SELECT @QT = N'''', @cr = N'
';
DECLARE @find1 AS NVARCHAR(MAX), @prfx1 AS NVARCHAR(MAX), @sufx1 AS NVARCHAR(MAX)
DECLARE @find2 AS NVARCHAR(MAX), @prfx2 AS NVARCHAR(MAX), @sufx2 AS NVARCHAR(MAX)
DECLARE @find3 AS NVARCHAR(MAX), @prfx3 AS NVARCHAR(MAX), @sufx3 AS NVARCHAR(MAX)
DECLARE @prtst AS NVARCHAR(MAX), @prfxC AS NVARCHAR(MAX), @sufxC AS NVARCHAR(MAX)
DECLARE @newdb AS NVARCHAR(MAX), @declr AS NVARCHAR(MAX)
DECLARE @NewCmd AS NVARCHAR(MAX), @GenCmd AS NVARCHAR(MAX)
;
SELECT
@find1 = CASE WHEN @subs1 = N'' THEN N'' ELSE LEFT(@subs1,CHARINDEX(N'=',@subs1)-1) END,
@prfx1 = CASE WHEN @subs1 = N'' THEN N'' ELSE N'REPLACE(' END,
@sufx1 = CASE WHEN @subs1 = N'' THEN N'' ELSE N',@find1,'+RIGHT(@subs1,LEN(@subs1)-CHARINDEX(N'=',@subs1))+N')' END,
@find2 = CASE WHEN @subs2 = N'' THEN N'' ELSE LEFT(@subs2,CHARINDEX(N'=',@subs2)-1) END,
@prfx2 = CASE WHEN @subs2 = N'' THEN N'' ELSE N'REPLACE(' END,
@sufx2 = CASE WHEN @subs2 = N'' THEN N'' ELSE N',@find2,'+RIGHT(@subs2,LEN(@subs2)-CHARINDEX(N'=',@subs2))+N')' END,
@find3 = CASE WHEN @subs3 = N'' THEN N'' ELSE LEFT(@subs3,CHARINDEX(N'=',@subs3)-1) END,
@prfx3 = CASE WHEN @subs3 = N'' THEN N'' ELSE N'REPLACE(' END,
@sufx3 = CASE WHEN @subs3 = N'' THEN N'' ELSE N',@find3,'+RIGHT(@subs3,LEN(@subs3)-CHARINDEX(N'=',@subs3))+N')' END,
@newdb = CASE WHEN @use_db= N'' THEN N'' ELSE N'USE [' + @use_db + N'];' + @cr END,
@declr = N'DECLARE @_Num AS INT, @_Lin AS INT, @_Err AS NVARCHAR(MAX), @_Msg AS NVARCHAR(MAX);'+@cr
;
;WITH
[base] AS (SELECT cmd = @command),
[quot] AS (SELECT cmd = CASE @quote WHEN N'' THEN cmd ELSE REPLACE(cmd, @quote, @QT) END FROM [base]),
[dble] AS (SELECT cmd = N'N'+@qt+REPLACE(cmd, @QT, @QT+@qt)+@qt FROM [quot]),
[prnt] AS (SELECT cmd = CASE @print WHEN 1 THEN N' PRINT '+cmd+';'+@cr ELSE N'' END
+ N' EXEC('+cmd+N');' FROM [dble]),
[ctch] AS (SELECT cmd =
CASE @catch WHEN N'none' THEN cmd
ELSE N'BEGIN TRY'+@cr+cmd+@cr+N'END TRY'+@cr+N'BEGIN CATCH'+@cr
+ N' SELECT @_Num=ERROR_NUMBER(), @_Lin=ERROR_LINE(), @_Err=ERROR_MESSAGE()'+@cr
+ CASE @catch
WHEN N'continue' THEN
N' SELECT @_msg=''Continuing after Error(''+CAST(@_Num AS NVARCHAR)+'') at Line ''+CAST(@_Lin AS NVARCHAR)+'''
+@cr+' ''+@_Err;'+@cr
+N' PRINT @_msg; '+@cr
+N' PRINT '' ''; '+@cr
WHEN N'ignore' THEN N' -- ignore = do nothing'+@cr
WHEN N'fail' THEN
N' SELECT @_msg=''Failing after Error(''+CAST(@_Num AS NVARCHAR)+'') at Line ''+CAST(@_Lin AS NVARCHAR)+'''
+@cr+' ''+@_Err;'+@cr
+N' RAISERROR(@_Num, 16, 1);'+@cr
+N' PRINT '' ''; '+@cr
ELSE N' --BAD else branch, shouldnt get here' END
+ N'END CATCH;' END FROM [prnt])
SELECT
@NewCmd = @prfx1+@prfx2+@prfx3+ N'@command' +@sufx1+@sufx2+@sufx3,
@command = cmd + @cr
FROM [ctch]
;
SELECT @GenCmd = '
DECLARE @sql AS NVARCHAR(MAX); SET @sql = '''+@newdb+ +@declr+ '''
;WITH
[-@from] AS ( SELECT * FROM ' +@from+ ' )
, [-@subs] AS ( SELECT [-NewCmd] = ' +@NewCmd+ ' FROM [-@from] )
, [-@print] AS ( SELECT [-NewCmd] = [-NewCmd] FROM [-@subs] )
SELECT
@sql = @sql + ''
'' + [-NewCmd]
FROM [-@subs]
;
EXEC sp_executesql @sql;
'
;
EXEC sp_executesql @GenCmd
, N'@command NVARCHAR(MAX), @from NVARCHAR(MAX), @find1 NVARCHAR(MAX), @find2 NVARCHAR(MAX), @find3 NVARCHAR(MAX)'
, @command, @from, @find1, @find2, @find3
;
And I think this is how Bhuvnesh's problem would be done in it:
EXEC OVER_SET '
EXEC OVER_SET "
ALTER USER [{name}] WITH LOGIN = [{name}]; ",
@from = "sys.database_principals
WHERE ( type_desc = ""WINDOWS_GROUP"" OR type_desc = ""WINDOWS_USER"" )
AND name NOT like ""%dbo%"" AND name NOT LIKE ""%#%"" ",
@use_db = "{db}",
@subs1 = "{name}=name",
@catch = "continue",
@print = 1,
@quote = "`";
',
@from = 'sys.sysdatabases
WHERE dbid > 4',
@subs1 = '{db}=name',
@catch = 'continue',
@print = 0,
@quote = '"';
I haven't compared it for performance, but I've not had any complaints myself.
As for why to do it this way? Well, I hope it's obvious, it's a heck of a lot easier to write, and even easier to read. IMHO, anyway. 😉
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2010 at 5:48 am
Gosh 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 4, 2010 at 5:55 am
Paul White (3/4/2010)
Gosh 🙂
Heh, just because I never finished my "50 Ways to Leave Your Cursors" series doesn't mean that I haven't been working on it. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2010 at 6:03 am
There's some pretty cool tricks that you can play with it too. Besides the obvious nesting trick above, you should also see how it tears up an INSERT..EXEC.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2010 at 6:04 am
RBarryYoung (3/4/2010)
There's some pretty cool tricks that you can play with it too. Besides the obvious nesting trick above, you should also see how it tears up an INSERT..EXEC.
Yes it's very nice. Shows the power of having a library of reliable functions to turn to.
Thanks for posting it.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 4, 2010 at 6:27 am
Thanks, Paul. I did just try a simple test to compare it with sp_MSforeachdb, and I've been getting about the same numbers for both.
I suppose I should post it as a script...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2010 at 6:29 am
Yes, but your solution is documented and supported 😎
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply