Need name information for all NT Domain Accounts on SQL Server

Question

Post reply

Need name information for all NT Domain Accounts on SQL Server

S_DBA

Ten Centuries

Points: 1242
More actions
November 18, 2009 at 11:01 am

#212777

I basically need a script which can give me the complete names of all Windows NT users having access on SQL Server.
For eg: I need a list in the following pattern:
LoginAccount CompleteName
DOMAIN\AB0087 John Something
DOMAIN\AB1245 SomeName
I guess you got the idea.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Use this script

select [NAME] from dbo.syslogins where isntname = 1

If I am not wrong SQL Server does not store the First Name , Last Name of the Users, so that should be stored in the Active Directory or Domain Controller.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Bru Medishetty (11/18/2009)
Use this script
select [NAME] from dbo.syslogins where isntname = 1
If I am not wrong SQL Server does not store the First Name , Last Name of the Users, so that should be stored in the Active Directory or Domain Controller.

Interesting, if I do not prefix the database on this query and I am not in master then I get a not available error. Ah, this is because in 2005+ you need to use sys.syslogins, also you should really use sys.server_principals like this because the syslogins compatibility view will be deprecated:

SELECT

*

FROM

MASTER.sys.server_principals AS SP

WHERE

type_desc In ('WINDOWS_LOGIN', 'Windows_Group')

Also, SQL Server doesn't store the actual name information. You'd need to hit Active Directory for that. Your best bet to get all the info is probably using PowerShell since that allows you to query SQL Server and ActiveDirectory. I'm sure there is a script out there that will do this.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 3

Thanks Jack for correcting.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

S_DBA Ten Centuries Points: 1242 More actions · Answer 4

Can somebody share the script required to pull the information I need from the Active directory?

Thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

No problem Bru. Yours worked, but with some caveats, and using yours helped me clarify mine. I was originally excluding the Windows Groups.

Ankur, I'd share if I knew. I typically avoid AD.

Check out SQL Server PowerShell Extensions. The author is very helpful if you have questions. Check out his blog

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Do you have adequate permissions in AD to query and pull back the list?

Here is a query that you could integrate into xp_cmdshell if you wanted.

dsquery user -limit 0 | dsget user -dn -disabled >C:\getalluserdn.txt

Here is another alternative I found via Google

AD View in SQL Server

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 7

andrewkane17

SSCertifiable

Points: 7529

November 18, 2009 at 3:07 pm

#1081247

You could also use xp_logininfo

Andrew