INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Windows user whose login cannot be found has access to server

Windows user whose login cannot be found has access to server

(OP)
Dear All,

I have a user who has access to the production server and databases. Same user did not have access to the development environment so I configured him. However, I am not able to find said NT user in the database. I looked under Security in SSMS, at the server and even database levels and couldn't find it.

CODE --> T-SQL

SELECT * FROM sys.server_principals ORDER BY Name
SELECT * FROM sys.database_principals ORDER BY Name
exec sp_helplogins 'domainname\username'
exec sp_helpuser 'domainname\username' 
return nothing. sp_who2 does show an entry for the user's.
It does not look like the user is granted access through a group neither. What could I possibly be missing? Where should I look for the user and why is he able to query some databases if he is nowhere to be found?

Thank you!

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

RE: Windows user whose login cannot be found has access to server

I don't know how you have your groups set up, but we create Windows Active Directory groups for access to SQL Server. Then we add the user to the group. That way we don't have to remove individual users when they leave us. Their access is removed by the admins when they remove them from AD groups.

If you are using Windows groups to access SQL Server, you can see who is a member of the group using this command:

exec master..xp_logininfo 'Domain\MyADGroup','members'

Just change Domain to your domain name and MyADGroup to the Windows AD group that has access to your SQL Server.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: Windows user whose login cannot be found has access to server

Thanks SQLBill, I was looking for that command a couple of weeks ago. My "google" skills are poor at best.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

RE: Windows user whose login cannot be found has access to server

Did you find how the user was 'getting in'?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: Windows user whose login cannot be found has access to server

(OP)
Hi guys. Thanks for your replies. I apologize if I seem to ignore you. I am not. It's just that I haven't been to the office since Tuesday and I am doing my best to follow doctor's orders and not remote in. I promise to publish my findings on Monday. Have a good week-end.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

RE: Windows user whose login cannot be found has access to server

(OP)
exec master..xp_logininfo 'Domain\MyADGroup','members' returns nothing, which is OK because no access is granted through NT groups but rather to individual users; this is a policy that I am implementing now.

However,
exec master..xp_logininfo 'Domain\User'
returns
account name type privilege mapped login name permission path
Domain\User user user Domain\User NULL

in the development environment where I personally granted the user access
AND
account name type privilege mapped login name permission path
Domain\User user admin Domain\User BUILTIN\Administrators

in the production environment where the user has not been explicitly granted access. He has access however through the Developers group which someone made a member of BUILTIN\Administrators.

I am certain this was done recently as so far we had been adding the NT users individually. This is a bit redundant now. How do you suggest I handle this: grant access to the Developers group (through BuiltIn Admins or not) or continue to do so individually? There is not one permission that only certain developers should have, however some members of the Developers group are manager and I can where in the future they may require exclusive privileges.

Thank you.


MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close