Nested Cursors
-
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...
-
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)
'
-
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, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
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...
-
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?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply