Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Nested Cursors Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 5:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:57 AM
Points: 70, Visits: 274
Hi there - I am trying to use a nested cursor to execute a script against all usernames across all database. I have a cursor for Username and Database but I can't get my code to cursor through the Databases cursor. Can someone help me to find a solution to this problem;

-------------------------------------------------------
------------------------------------------------------
DECLARE @DATABASE VARCHAR (70)

DROP TABLE #DATABASELOV
CREATE TABLE #DATABASELOV (DATABASENAME VARCHAR (70))
INSERT INTO #DATABASELOV (DATABASENAME) SELECT NAME FROM SYS.DATABASES where name not in ('master','tempdb','model','msdb')


DECLARE DATABASENAMEC CURSOR
FOR
SELECT DATABASENAME
FROM #DATABASELOV

OPEN DATABASENAMEC
FETCH NEXT FROM DATABASENAMEC INTO @DATABASE

WHILE @@FETCH_STATUS = 0
BEGIN

declare @sql2 varchar (255)

DROP TABLE #orphaneduseraccounts
set @sql2 = 'exec sp_change_users_login ''Report'''

CREATE TABLE #orphaneduseraccounts
(
Username VARCHAR(255) ,
UserSID VARCHAR(255)
)
INSERT INTO #orphaneduseraccounts
( Username, UserSID )
exec ( 'USE' + ' ' + @DATABASE + ' ' + @sql2 )



DECLARE @Username VARCHAR(255)
DECLARE @sql VARCHAR(MAX)


DECLARE UsernameCursor CURSOR
FOR
SELECT Username
FROM #orphaneduseraccounts

OPEN UsernameCursor
FETCH NEXT FROM UsernameCursor INTO @Username

WHILE @@FETCH_STATUS = 0
BEGIN


SET @sql = 'EXEC sp_change_users_login ''Auto_Fix'','''

exec ( 'USE' + ' ' + @DATABASE + ' ' + @sql + @Username + '''' )

FETCH NEXT FROM UsernameCursor INTO @Username

END

CLOSE DATABASENAMEC
DEALLOCATE DATABASENAMEC

end

CLOSE UsernameCursor
DEALLOCATE UsernameCursor


---------------------------------------
---------------------------------------
Thanks, Russell


--------------------------------------------

Laughing in the face of contention...
Post #1522245
Posted Thursday, December 12, 2013 6:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 28, 2014 5:23 AM
Points: 283, Visits: 1,110
Rather than look at your code, I've supplied a script I use that does the the same thing (fix orphaned users across all databases).

Mine uses UPDATE_ONE, but you could change it to use AUTO_FIX if you prefer.

exec sp_msforeachdb 'use [?];
declare @sql varchar(max)
select @sql = s from (
select ''use ['' + db_name() + '']; exec sp_change_users_login ''''Update_One'''', '''''' + d.name + '''''', '''''' + d.name + '''''';''
from sys.database_principals d
join sys.server_principals p on d.name = p.name collate database_default
left join sys.server_principals o on d.sid = o.sid
where o.name is null
and d.name not in (''public'')
for xml path('''')) x(s);
if @sql is not null exec(@sql)
'




Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
Post #1522271
Posted Thursday, December 12, 2013 7:27 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 956, Visits: 1,273
You are closing your cursors in the wrong order. You need to close the UsernameCursor within the outer while loop (DATABASENAMEC).

Interesting side note, I've never had to fix orphan users on all of the databases on a SQL instance, only a single database. I can see the need to do this if you are moving all of the databases from one server to another, which is what I'm guessing you are doing.

I have a script that I use to fix orphans that I put in a database called DBA, which has all of my utilities. Here is mine:

USE [DBA]
GO
/****** Object: StoredProcedure [dbo].[FixOrphanUsers] Script Date: 12/12/2013 09:26:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*

Purpose : This procedure fixes orphan users. Simply pass in the Database and all users will be fixed.
Orphan users are created by moving databases from one server to another and SQL Server login users are carried
along. They will have different internal identities. This procedure will resync those users.
This procedure is based on the following call:

EXEC sp_change_users_login 'Auto_Fix', 'user'

Author : KWZimmerman - Lefrak Organization

Date : 11/18/2013

Requirements: None


Updates Date By Comment
------- ---------- --- ---------------
11/18/2013 KWZ Initial release

*/

ALTER procedure [dbo].[FixOrphanUsers] (
@Database varchar(max) = null
)
as
if @Database is null
begin
print 'Missing database name!'
print 'Call : ex ' + OBJECT_NAME(@@PROCID) + ' @Database = ''<database name>'''
end
else

begin
declare @ExcludeUser table (ID int primary key identity (1,1)
,User_Name varchar(50)
)

declare @User_Name varchar(50)
declare @SQL varchar(max)
declare @CRLF char(2) = char(13) + char(10)

insert into @ExcludeUser (User_Name) values ('public')
insert into @ExcludeUser (User_Name) values ('dbo')
insert into @ExcludeUser (User_Name) values ('guest')

declare @Users table (ID int primary key identity (1,1)
,User_Name varchar(100)
)
select @SQL = 'use ' + @Database + '; select distinct USER_NAME(sdp.grantee_principal_id) as User_Name' + @CRLF
select @SQL += 'from sys.database_permissions sdp;' + @CRLF

insert into @Users
exec (@SQL)

delete from @Users
from @Users u left join
@ExcludeUser EU on u.User_Name = eu.User_Name
where eu.ID is not null

declare FixUser_Cursor cursor for
select distinct User_Name
from @Users

open FixUser_Cursor
fetch next from FixUser_Cursor into @User_Name
while @@FETCH_STATUS = 0
begin
set @SQL = 'use ' + @Database + '; exec sp_change_users_login ''Auto_Fix'',''' + @User_Name + ''''
print @SQL
exec (@SQL)
fetch next from FixUser_Cursor into @User_Name
end
close FixUser_Cursor
deallocate FixUser_Cursor
end


Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1522308
Posted Thursday, December 12, 2013 8:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:57 AM
Points: 70, Visits: 274
Thanks Chaps.

Yes, Kurt. This is to manage multiple databases, where majority of the time I don't know what the database names are that are being restored. As the db's are in a test environment I've take the all or nothing approach to my code.

Thanks for SP Richard. I wasn't aware this existed. I have decided to use it as its a much cleaner approach to what i'm trying to achieve.

Thanks, Russell.


--------------------------------------------

Laughing in the face of contention...
Post #1522344
Posted Thursday, December 12, 2013 11:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:04 PM
Points: 7,081, Visits: 14,670
While the order of closing is wrong, I might also point out that you aren't FETCHING the next DB inside of the loop, so you're pulling one DB, cycling through the users and then closing the DB cursor.

If you flipped the order of the cursors being closed AND fetched the next DB, your code would work I thinnk.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1522426
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse