Microsoft SQL and orphaned users

This post is just a little note for me and little collection of steps I found and used (and wrote down not to forget 😉 ).

Find orphaned users:

CREATE TABLE ##ORPHANUSER 
( 
DBNAME VARCHAR(100), 
USERNAME VARCHAR(100), 
CREATEDATE VARCHAR(100), 
USERTYPE VARCHAR(100) 
) 

EXEC SP_MSFOREACHDB’ USE [?] 
INSERT INTO ##ORPHANUSER 
SELECT DB_NAME() DBNAME, NAME,CREATEDATE, 
(CASE 
WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ”SQL LOGIN” 
WHEN ISNTGROUP = 1 THEN ”NT GROUP” 
WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ”NT LOGIN” 
END) [LOGIN TYPE] FROM sys.sysusers 
WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND 
SID NOT IN (SELECT SID FROM sys.syslogins)’ 

SELECT * FROM ##ORPHANUSER 

DROP TABLE ##ORPHANUSER

CREATE TABLE ##ORPHANUSER

(

DBNAME VARCHAR(100),

USERNAME VARCHAR(100),

CREATEDATE VARCHAR(100),

USERTYPE VARCHAR(100)

)

EXEC SP_MSFOREACHDB‘ USE [?]

INSERT INTO ##ORPHANUSER

SELECT DB_NAME() DBNAME, NAME,CREATEDATE,

(CASE

WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ‘‘SQL LOGIN’‘

WHEN ISNTGROUP = 1 THEN ‘‘NT GROUP’‘

WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ‘‘NT LOGIN’‘

END) [LOGIN TYPE] FROM sys.sysusers

WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND

SID NOT IN (SELECT SID FROM sys.syslogins)’

SELECT * FROM ##ORPHANUSER

DROP TABLE ##ORPHANUSER

With this little script we can get list of all orphaned users in all DBs. Now we have 2 options. First we select the DB (doesn’t matter if in GUI of Managemetn studio or with USE ‘db’) and then we can create new login and fix the orphan:

EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, NULL, ‘password’

1	EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, NULL, ‘password’

Or only fix the orphan with already existing user

EXEC sp_change_users_login ‘Auto_Fix’, ‘user’

1	EXEC sp_change_users_login ‘Auto_Fix’, ‘user’

Thanks for support:
https://gallery.technet.microsoft.com/scriptcenter/List-out-Orphan-User-fccf2867
http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm

Microsoft SQL and orphaned users

Comments

Leave a Reply Cancel reply