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/%5B/url%5D
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 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy