2

I moved and restored a database from a backup into another SQL Server 2008R2 about a year ago. I wasn't aware of the concept of orphaned users. From what I understood, is that we can end up having mistmatched SIDs? So I run the following below query and I see that sid names are not the same for the server and the database for the same login user. So far, users are not complaining but how would I fix this? is there any stored procedure that list them for me? or what problems that I could be facing if I leave everything like it is.

select d.name, d.sid, s.name, s.sid
from sys.database_principals d 
full join sys.server_principals s
on d.principal_id = s.principal_id

Any advice is much appreciated.

1
  • @GreenstoneWalker Actually a contained user is much like a user without a login - they authenticate against the database. It is only for SQL auth; Windows auth will authenticate at the server level. See this post for more info. Oct 23, 2014 at 0:51

1 Answer 1

5

There are a couple of possibilities.

First if the orphaned user is a windows login/group (type U or G) then no problem.

-- If the login doesn't currently exist on the server
CREATE LOGIN [Windows Account] FROM WINDOWS;
-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];

If it's a "SQL Login" (type S) and the login exists then again no problem.

-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];

If the login does not exist for a "SQL Login" then you have to create the login and that will require knowing the password.

-- Create the login
CREATE LOGIN [login name] WITH PASSWORD = '<strong password>';

Don't forget to make sure your password meets your windows policy requirements. If you still have the old server you can copy the login pretty easily from the other server including the password by getting the HASH of the password from the other server. (There may be some issues depending on if the two servers are different versions).

I did a post on this here: http://sqlstudies.com/2013/03/25/how-do-i-move-a-sql-login-from-one-server-to-another-without-the-password/

2
  • Thak you very much for your reply, as the link you provided was helpful to me after I checked your post Logins Vs. Users. So when I run this query to find all orphaned users: SELECT * FROM sys.database_principals WHERE sid NOT IN (SELECT sid FROM sys.server_principals) AND sid IS NOT NULL AND type <> 'R' AND sid <> 0x00 I get 4 users, that means they dont have an existing login, so I have to create each login and then alter the user with that login. But I wasn't sure why would I need to use the "With Password" as when I do it from the SSMS, it never asked me to know the password I think! Oct 22, 2014 at 21:44
  • They are probably Windows logins not SQL Server logins. (Types U or G vs type s) There is no password needed there and the SID comes from windows not SQL Server. Oct 22, 2014 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.