Creating and manging logins is one of the common tasks that a DBA does. Usually the end user comes up with a request that I need to have access to that database and I need to have the same privileges as Mr. X has. But Mr.X’s login was not explicitly granted access to the database. Instead his Domain account was added to a group on the Active Directory and that group was granted requisite permissions on the instance.
Mr. X might be member of several groups on the Active Directory and out of those only few groups may have access to the databases on the instance. To create a login which has same privileges as that of Mr.X, DBA must first check to which domains groups Mr.X belongs to and out of those which groups have access to the databases. Manually getting this information from the Active Directory and then comparing it with the SQL Server Logins can be time consuming. As in the below example, the Domain Account is part of more than 50 groups.
There are many ways of getting this information using Powershell, VBScript etc. The one that I always use is the xp_logininfo extended stored procedure. The following script will let me know how the login PRAADIGA\PRAADIGA is granted access to the SQL Server instance.
Even though praadiga is not explicitly added as a login on the Instance, it is getting the privileges from the BUILTIN\Administrators group membership.
A login can also be part of multiple groups which have access to different databases. One group may have read-only access to one database and another may have dbo privileges on another database. In this case the ‘ALL‘ parameter of xp_logininfo is very useful.
XP_LOGININFO 'loginname', 'ALL'
In this example the login has access to many databases through different Active Directory Groups.
xp_logininfo also has another useful parameter called members. This parameter can be used to get a list of all the members of a Local\Domain group and the level of access those members have on the Instance.
XP_LOGININFO 'BUILTIN\ADMINISTRATORS', 'MEMBERS'
This stored procedure had helped me save time while responding to the Auditor’s questions. You may also find this useful.