Windows user whose login cannot be found has access to server

[TheBugSlayer]

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.

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)

 

[SQLBill]

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

 

[djj55]

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!

 

[SQLBill]

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

 

[TheBugSlayer]

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)

 

[TheBugSlayer]

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)