Art of the DBA Rotating Header Image

Monday Scripts – xp_logininfo

How many times have you wished you could use SQL Server to query Active Directory and see who is in that group? This week’s script will allow you to do that, so if you have AD groups set up for logging into your server you can see exactly which logins have access.

xp_logininfo is an extended stored procedure that does just that. By passing a couple parameters, you can easily query any AD group that is a server principal. Wrap it up in a dynamic SQL loop, and you can quickly and easily audit your server’s security.

declare @winlogins table
(acct_name sysname,
acct_type varchar(10),
act_priv varchar(10),
login_name sysname,
perm_path sysname)

declare @group sysname

declare recscan cursor for
select name from sys.server_principals
where type = 'G' and name not like 'NT%'

open recscan
fetch next from recscan into @group

while @@FETCH_STATUS = 0
begin
insert into @winlogins
exec xp_logininfo @group,'members'
fetch next from recscan into @group
end
close recscan
deallocate recscan

select
r.name,
u.name,
u.type_desc,
wl.login_name,
wl.acct_type
from (select * from sys.server_principals where type = 'R') r
join sys.server_role_members rm on (r.principal_id = rm.role_principal_id)
join (select * from sys.server_principals where type != 'R') u on rm.member_principal_id = u.principal_id
left join @winlogins wl on u.name = wl.perm_path
order by login_name,r.principal_id,u.type_desc,u.name

Looking at the script, I first do a looped scan through sys.server_principals for all Windows groups (type ‘G’) and collect their AD group info. I store that in a table variable, then join it back to a select from server_principals again for a full listing of all my server permissions. Bam! Instant security report.

4 Comments

  1. […] , u.name , login_name , r.principal_id , u.type_desc Code above modified from original source: http://www.mikefal.net/2011/04/18/monday-scripts-%E2%80%93-xp_logininfo/ Share this:TwitterFacebookLike this:LikeBe the first to like this post. Categories: Active […]

  2. Ashwin says:

    Nice little script.

    I was trying to write this up and then thought let me first search if there is anything on the internet and got your blog.

    Thanks for putting this through

  3. David says:

    This is great!

    Just want to note this in case anyone else is trying to do what I was: There is one limitation, though (not of this script, of xp_logininfo). I was working on a project for our Security department, and they wanted me to get a list of all members of all AD groups into a data table to do analysis.

    They supplied a list of all AD Groups, since I would need something to loop through and query. I found that my results were the same as running the script above, querying the groups in sys.database_principals. It looks like xp_logininfo is limited to security groups that are assigned to the SQL Server. It will not query other groups.

  4. Anji says:

    Nice and simple script..

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *