SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Orphan User vs User Without Login
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 04/14/2014 :  13:21:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/14/2014 :  16:27:42  Show Profile  Reply with Quote
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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 04/14/2014 :  16:52:02  Show Profile  Reply with Quote
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)."
--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



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

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/14/2014 :  17:20:23  Show Profile  Reply with Quote
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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 04/14/2014 :  19:33:48  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000