way to check if windows auth user is real?

Question

Post reply

way to check if windows auth user is real?

SQLSandwiches

SSCrazy

Points: 2143
More actions
April 28, 2011 at 5:22 pm

#235468

I'm playing around with a little script to add new users. It's just for my own practice/learning fun time.
So far it looks like this:
USE [master]
declare @user varchar (100),
@LiveUser bit,
@sqls varchar(500)
set @user = 'somedude'
if exists ( select * from syslogins where name like '%' + @User + '%')
begin
set @LiveUser = 1
end
else
set @LiveUser = 0
if @LiveUser = 0
begin
set @sqls = 'CREATE LOGIN [domain\' + @user + '] FROM WINDOWS WITH DEFAULT_DATABASE=[somedb]'
exec (@Sqls)
print 'User is now in the system'
end
else
print 'User already in the system'
The one problem I am getting is if 'somedude' isn't in AD. Is there anyway using t-sql to check this?
I know you can check the opposite way with sp_validatelogins but wasn't sure if you could check if a potential new user was in AD or not.
I'd like to have a pretty message instead of
Windows NT user or group 'domain\somedude' not found. Check the name again.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

If the user(login ) exists there then it will be pulled out by

select * from syslogins where name like '%' + @User + '%'

Are you not getting the login name from above query ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SQLSandwiches SSCrazy Points: 2143 More actions · Answer 2

Nope. Have you ever used sp_validatelogins? It tells you what logins you have on your server aren't in your AD. I'm looking for the opposite.

I am not sure there is anything that can do this but I figured, I'd open it up to the public.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Yin Halen (4/29/2011)
Nope. Have you ever used sp_validatelogins? It tells you what logins you have on your server aren't in your AD. I'm looking for the opposite.
I am not sure there is anything that can do this but I figured, I'd open it up to the public.

I know there's a way to get the list of users in the AD. With that you'll be able to figure out the "missing" ones.

Now the question of auto_insert is better asked to the security team.

SQLSandwiches SSCrazy Points: 2143 More actions · Answer 4

What do you think is the best way to handle that error then? I'm just writing this for my own personal growth so I don't have a security team to view my code.

I want to be able to throw something to the user saying "Sorry buddy, no AD user in there" and then go to the next step rather than getting a SQL error stopping everything.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Check out Try catch in books online (sql server's help file).