asked
Problem in detecting orphaned user on database.
This is database restore case(MSSQL2005 to MSSQL 2008) in that database is restored from one server to another server and while checking orphaned user by command “sp_change_users_login 'report'” for few users it does not show report even these are orphaned user because these have no mapping login available on server. On user properties Login box is blank but in sysusers table field islogin is true, not understand in which case it happened and why these user are not showing in orphaned user report. Please let me know if you required any other details. ![users][1] [1]:
http://ask.sqlservercentral.com/storage/temp/222-User+Properties.jpg
`sp_change_users_login 'report'` neither would report for the users which are created without any login i.e. CREATE USER username WITHOUT LOGIN, nor would report the users which are created from Windows User. Moreover, sp_change_users_login is already marked as deprecated in SQL 2008, so a better way would be to use `sys.database_principals` system VIEW and afterwards use `ALTER USER` for mapping EDIT1: -- Finding SQL users without login SELECT * FROM sys.database_principals WHERE DATALENGTH(sid) >= 28 -- users mapped to logins have a 16 bytes SID, -- users without login have a length of ~28 bytes AND sid NOT IN ( SELECT sid FROM sys.server_principals ) -- No login with a matching SID AND type = 'S' -- Only SQL users AND principal_id > 4 -- filter system (well-known) principals go -- Finding orphaned SQL users SELECT * FROM sys.database_principals WHERE DATALENGTH(sid) 4 -- filter system (well-known) principals go -- Finding orphaned users created from Windows Login SELECT * FROM sys.database_principals WHERE sid NOT IN ( SELECT sid FROM sys.server_principals ) -- No login with a matching SID AND (type = 'U' --WINDOWS_USER --OR type = 'G' --WINDOWS_GROUP ) AND principal_id > 4 -- filter system (well-known) principals go
User without login have many uses. Users without login can be used to test if the application works with minimum permissions. They are also can be a replacement for application roles when combined with the optional "WITH NO REVERT" or "WITH COOKIE" clauses. The main advantage of using users without logins rather than approles is that permission to impersonate is based on permissions and not on a password, eliminating the need to share a password that most likely will be hardcoded into an application. Now I have edited my answer once again to show the difference between users without login and orphaned user.
check this link,if it resolve this issue..
http://support.microsoft.com/kb/246133 check and reply back..
9 People are following this question.
