Grumpy Old DBA

For the parnaoid production DBA - xp_logininfo

I‘ve been involved in some performance tuning of an application which involves the calling of system stored procedures.  The first of these is xp_logininfo, now if you’re a paranoid production DBA like me then this procedure can be a friend indeed.

But first to the application which calls this procedure as part of its login process. If you look in BOL you’ll see this procedure returns information concerning domain or active directory users, so in other words it queries the active directory, well this is fine except if there should happen to be any latency, I’m not going to discuss the client architecture other than to say that not all networks are simple and a geographically distributed system may need special considerations. 

The procedure actually uses openrowset  with the call to NetGroupGetMembers which goes and gets information via oledb – this isn’t an area in which I have expertise, I just mention this  in passing.

So my attention was drawn to this procedure because of latency in the calls for information which were part of a blocking problem. Now I’d hope in normal circumstances not to find an application calling this procedure, by default this procedure does not have any granted permissions, however this was not the case and this procedure was part of a problem, which brings me back to the paranoid production DBA.

Probably to much surprise I am not a great lover of integrated security, I think it offers less security as once a user has logged into the domain/AD and that user account has rights to the sql server, off they go. I personally prefer another level of authentication, sure make the domain login is secure, but if you want access to my SQL Server then you should need another login!!  However, my main pet hate is that usually the DBA’s have no control of AD group membership, it can be very difficult too to actually discover what route of authentication a user is taking, which is where xp_logininfo comes into its own.

exec dbo.xp_logininfo 'builtin\administrators','members'

This shows the members of the local admins group, which contains another group. You can’t query this group directly but if you add the group to the sql server logins then you can query this group for members, and so on until there are no more nested groups.

Nested groups are a potential loophole in security, I’ve actually found all manner of users buried in nested groups. When there are testing environments it can be common to drop groups into groups because “ well it worked for me in xxxx environment/group “  In one case this resulted in a test account being added to a production account and test data being created in the production system – you try explaining that one away to SOX !!!

If you have lots of domain/ad groups on your production server an audit with xp_logininfo may well bring some unpleasant surprises.