Orphaned users

Question

Orphaned users

sunny.tjk

SSCertifiable

Points: 5041
More actions
April 5, 2013 at 4:42 am

#273143

I've 2 sql server instances on my local system.
I backed up a database from one instance and restored that database on the other instance.
I copied over the logins from the 1st instance to the 2nd instance.
Then I ran the following statement on the 2nd instance to check if there are any orphaned users but I got 0 results.
sp_change_users_login @Action='Report'
GO
But I still cannot login into the newly restored database with any of the sql server logins. Am i missing something?

Viewing 3 posts - 1 through 2 (of 2 total)

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

jasona.work SSC Guru Points: 50117 More actions · Answer 1

You may still need to "re-associate" the SQL Login with the DB user.

I've found these two queries to do a bang-up job for finding and fixing orphaned users (and possibly what you're running into:)

--The "new" way. Script to find orphans was pulled from SQLServerCentral.com

--Alter User is the MS recommended method to fix

use [DBName];

SELECT dp.name AS DBUser,

dp.sid AS DBSid

FROM sys.database_principals dp

LEFT OUTER JOIN sys.server_principals sp

ON dp.sid = sp.sid

WHERE sp.sid IS NULL

AND dp.type = 'S' -- SQL_USER

AND dp.principal_id > 4

use [DBName];

alter user /*{User reported from above}*/ with

login = /*{SQL Login for user}*/;

I don't recall where on here I found the "find orphaned users" query, so to whoever posted it, the credit is yours.

What you could try doing to fix your issue is, run the second part of the script, the alter user {whatever} with...

It won't hurt (unless you typo) and it may help.

Jason

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's a very similar version when compared to Jasons;

if a user matches it's login SID, it fine and nothing needs to be changed.

If the SID doesn't match , it builds the ALTER USER command

if the login is missing, it builds a CREATE LOGIN command(with a default password!), and also the ALTER USER command.

depending on your situation, you might not need to create missing logins

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 'CREATE LOGIN ' + quotename(dbloginz.name) + ' WITH PASSWORD=N''NotARealPassword'' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;

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

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.name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys')

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!