Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Orphaned users


Orphaned users

Author
Message
sunny.tjk
sunny.tjk
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 1344
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?
jasona.work
jasona.work
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1914 Visits: 10946
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 intoSmile

--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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14986 Visits: 39018
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search