Auto fix orphaned users

Question

Post reply

Auto fix orphaned users

gurthang99

SSC Veteran

Points: 234
More actions
November 11, 2011 at 7:32 am

#247672

Test on MS SQL Server 2008 R2
SET NOCOUNT ON
declare @GetListOfOrphanUser table
(
rowid smallint IDENTITY(1,1),
UserName sysname,
UserSID varbinary(85)
)
declare @UserName sysname
, @NoOfUsers smallint
-- To generate the orphaned users list.
INSERT @GetListOfOrphanUser (UserName, UserSID)
EXEC sp_change_users_login 'report'
SET @NoOfUsers = @@ROWCOUNT
WHILE @NoOfUsers > 0
BEGIN
SELECT @UserName = UserName
FROM @GetListOfOrphanUser
WHERE rowid = @NoOfUsers
SET @NoOfUsers = @NoOfUsers - 1
BEGIN TRY
EXEC sp_change_users_login 'Update_One', @UserName, @UserName
/*
In development our SQL environment, orphaned user needs to be fixed
if and only if the corresponding login exists.
*/
END TRY
BEGIN CATCH
/*
Nothing to do incase the logins for equivalent users does not exist.
Over here, it can customised to remove the orphaned user
in case equivalent login does not exist
*/
END CATCH
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

is there a question in there somewhere?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

Lowell (11/11/2011)
is there a question in there somewhere?

May be FYI (to SSC). 😀

gurthang99 SSC Veteran Points: 234 More actions · Answer 3

Yep, just an FYI. All the other fixes I saw use cursors and mine does not.

Lowell SSC Guru Points: 323518 More actions · Answer 4

in theory, we should start using the ALTER USER xxx WITH LOGIN =xxx syntax for fixing orphaned users now, right?

what do you typically do if a USER exists in a database, but not on the server? do you ignore it, or add a LOGIN for the user? or "it depends"?

--Login Exists but wrong sid: remap!

ALTER USER [HDS] WITH LOGIN = [HDS];

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 5

ok peers: poke holes in this: this seems to do what the oldstype autfox would do, right?

well...this just generates the statements...i'd have to execute them:

SELECT CASE

WHEN svloginz.name IS NOT NULL

AND dbloginz.sid <> svloginz.sid

THEN '--Login Exists but wrong sid: remap!

ALTER USER ' + QUOTENAME(dbloginz.name) + ' WITH LOGIN = ' + QUOTENAME(svloginz.name) + ';'

ELSE '--Matching login for ' + QUOTENAME(dbloginz.name) + ' Not found. further analysis/decisions required.'

END

FROM sys.database_principals dbloginz

LEFT OUTER JOIN sys.server_principals svloginz

ON dbloginz.name = svloginz.name

WHERE dbloginz.type IN ( 'S', 'U' )

AND dbloginz.principal_id > 4

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!