Orphan User vs User Without Login

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Orphan User vs User Without Login

Author

Topic

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-04-14 : 13:21:14

Q: How to tell the difference between an Orphaned User and a User created "Without Login"?
I have a script to find orphaned Users which was working fine until someone created a user "without login". My script joins the sys.database_principals and sys.server_principals tables for type in S, U or G and eliminates a few specific users (dbo, guest, etc.). I would have thought that there was some way to identify Users that were created Without Login.

===============================================================================
“Everyone wants a better life: very few of us want to be better people.”
-Alain de Botton

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-14 : 16:27:42

After a quick test (because I don't know the answer) The only difference I can see is with the [Authentication_Type] and [Authentication_type_desc] in sys.database_principals.


name                 authentication_type authentication_type_desc
---------------------------------------- ----------------------------
testLogin            1                   INSTANCE
testUserNoLogin      0                   NONE

My test code:


use junk
go
create login testLogin with password = 'testLogin', check_policy = off

create user testLogin for login testLogin
create user testUserNoLogin without login

select name, authentication_type, authentication_type_desc from sys.database_principals where name like 'test%'

go
drop user testUserNoLogin
drop user testLogin
drop login testLogin
go

Be One with the Optimizer
TG

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-04-14 : 16:52:02

To be filed under "This is sure to break in the next release (or soon thereafter)"...

Per Raul Garcia: http://blogs.msdn.com/b/raulga/archive/2006/07/03/655587.aspx

"Pretty much the only way to distinguish between a user and a user without login is by looking at the SID. Regular SQL users will have a GUID as a sid (16 bytes) while users without login will have a SID structure (28 bytes for current version of SQL Server)."[CODE]--List of Orphaned Users (Excluding WithoutLogin)
select Datalength(dp.sid), *
from sys.database_principals dp
left outer join
sys.server_principals sp
on sp.sid = dp.sid
where
sp.sid is null
and dp.type in ('S', 'U', 'G')
and dp.name not in ('dbo', 'sys', 'guest', 'INFORMATION_SCHEMA', 'MS_DataCollectorInternalUser')
and not (dp.type = 'S' and Datalength(isnull(dp.sid, 0)) > 16) -- Remove "without login" Users from resultset
order by
dp.name[/CODE]

===============================================================================
“Everyone wants a better life: very few of us want to be better people.”
-Alain de Botton

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-14 : 17:20:23

I notice that comment of Raul's was written in 2011. Not sure when 2012 came out but 2008 r2 didn't have the authentication_type columns in sys.database_principals. Looks to have been added for 2012.

Be One with the Optimizer
TG

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-04-14 : 19:33:48

I'm in 2008 R2. I noticed the lack of the columns in sys.database_principals but figured that it was a version related change. My bigger concern is that in 2014 or whenever, this work-around won't work. It does seem kinda cheesy.

I'm surprised that when this new way of defining a User was designed, that they didn't simply make a new User Type; there's only a handful of Types and numbers are cheap.

Thanks for your help though.

===============================================================================
“Everyone wants a better life: very few of us want to be better people.”
-Alain de Botton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources