February 4, 2015 at 7:57 pm
About a year after this thread I wrote an article on the topic. It targeted 2005 and 2008 but the technique works on 2012 as well. If you read the comments in the discussion Wayne talks about a column that was added in SQL 2012 that makes it a bit simpler still.
http://www.sqlservercentral.com/articles/Security/98202/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 4, 2025 at 5:54 am
Find below script will help for fix the orphan users for all DB's.
Fix Orphan users in all databases
SET NOCOUNT ON
BEGIN
declare @UserName sysname
declare @NoOfUsers smallint
declare @dbname varchar(50)
declare @query varchar(2000)
declare @query_1 varchar(2000)
CREATE TABLE #orphanusers
(
rowid smallint IDENTITY(1,1),
UserName sysname,
UserSID varbinary(85)
)
CREATE TABLE #databases
(
dbname varchar(50)
)
INSERT INTO #databases SELECT name from sys.databases where database_id > 4 and state_desc = 'ONLINE'
WHILE EXISTS(SELECT 1 FROM #databases)
BEGIN
SET @query = ''
select TOP 1 @dbname = dbname from #databases order by dbname
SET @query = 'EXEC ' + @dbname + '.dbo.sp_change_users_login ''report'''
-- To generate the orphaned users list.
INSERT INTO #orphanusers EXEC(@Query)
WHILE EXISTS(SELECT 1 FROM #orphanusers)
BEGIN
SELECT TOP 1 @UserName = UserName
FROM #orphanusers
order by rowid
BEGIN TRY
SET @query_1 = 'EXEC ' + @dbname + '.dbo.sp_change_users_login ''Update_One'',' + @UserName+ ',' + @UserName
Print 'Solving ' + @username + ' in ' +@dbname
EXEC(@Query_1)
END TRY
BEGIN CATCH
/* We can Probably drop the user if it is not associated with any login */
PRINT 'No Existent Login for ' + @username
END CATCH
DELETE FROM #orphanusers where UserName = @UserName
END
DELETE FROM #databases where dbname = @dbname
END
DROP TABLE #orphanusers
DROP TABLE #databases
END
March 4, 2025 at 5:54 am
Find below script will help for fix the orphan users for all DB's.
Fix Orphan users in all databases
SET NOCOUNT ON
BEGIN
declare @UserName sysname
declare @NoOfUsers smallint
declare @dbname varchar(50)
declare @query varchar(2000)
declare @query_1 varchar(2000)
CREATE TABLE #orphanusers
(
rowid smallint IDENTITY(1,1),
UserName sysname,
UserSID varbinary(85)
)
CREATE TABLE #databases
(
dbname varchar(50)
)
INSERT INTO #databases SELECT name from sys.databases where database_id > 4 and state_desc = 'ONLINE'
WHILE EXISTS(SELECT 1 FROM #databases)
BEGIN
SET @query = ''
select TOP 1 @dbname = dbname from #databases order by dbname
SET @query = 'EXEC ' + @dbname + '.dbo.sp_change_users_login ''report'''
-- To generate the orphaned users list.
INSERT INTO #orphanusers EXEC(@Query)
WHILE EXISTS(SELECT 1 FROM #orphanusers)
BEGIN
SELECT TOP 1 @UserName = UserName
FROM #orphanusers
order by rowid
BEGIN TRY
SET @query_1 = 'EXEC ' + @dbname + '.dbo.sp_change_users_login ''Update_One'',' + @UserName+ ',' + @UserName
Print 'Solving ' + @username + ' in ' +@dbname
EXEC(@Query_1)
END TRY
BEGIN CATCH
/* We can Probably drop the user if it is not associated with any login */PRINT 'No Existent Login for ' + @username
END CATCH
DELETE FROM #orphanusers where UserName = @UserName
END
DELETE FROM #databases where dbname = @dbname
END
DROP TABLE #orphanusers
DROP TABLE #databases
END
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply