At times while monitoring a database server, we may need to know not only which SQL users are accessing, but also windows users for security and auditing purposes. Frankly, this task may be quite simple to get it completed when all of those users are created and visible inside the database engine. Despite this, it is not a surprise that windows users can access via windows groups which means that DBAs can not see them from SQL Server easily unless we have the manner to list them. Luckily, there is an extended stored procedure named “xp_logininfo” that we need to use to get that information. (The “xp_logininfo” asks the Active Directory for the windows users).
First of all, this system stored procedure takes two input parameters. The first one is the windows group name, and the second one is the value ‘members’. For instance, it lists the windows users that access via the windows group “MyDomain\SQLProdUsrs”. (You need to have “sysadmin” role to execute it).
EXEC xp_logininfo @acctname ='MyDomain\SQLPrdUsrs', @option='members'
Naturally, this is perfect if we only have one windows group to query. As time goes by, we are likely to get created more windows groups inside the database engine and in no time we will need to create an specific script to figure out the other windows users. Here is that code.
SET NOCOUNT ON
CREATE TABLE #WindowGroup(
privilege char(9) ,
mapped_login_name varchar(300) ,
DECLARE @WindowGroupName varchar(max)
DECLARE @db [NCHAR](128)
DECLARE cursor_WG CURSOR FOR SELECT [name] FROM sys.server_principals WHERE TYPE='G'
FETCH NEXT FROM cursor_WG INTO @WindowGroupName
WHILE @@FETCH_STATUS= 0
INSERT #WindowGroup(account_name , type , privilege ,mapped_login_name ,permission_pathsysname )
EXEC xp_logininfo @acctname =@WindowGroupName , @option = 'members'
FETCH NEXT FROM cursor_WG INTO @WindowGroupName
UPDATE #WindowGroup SET server_name=@@servername
select server_name AS ServerName, account_name as WindowsAccountName, Type, Privilege, mapped_login_name as MappedLoginName, permission_pathsysname as WindowsGroupName
ORDER BY permission_pathsysname,mapped_login_name
DROP TABLE #WindowGroup
SET NOCOUNT OFF
As you have seen, the T-SQL code is simply easy to understand. Clearly, it filters the windows groups by indicating the value ‘G’ for the Type column of the system view “sys.server_principals”, and eventually through the cursor each of them is read. Now I hope you make the most out of this script to have better visibility of the windows users accessing the databases. Thanks for reading again!
It is clear that we always need to have some kind of history for SQL Backup jobs in order to check whether or not they were executed correctly. I personally use a .txt file to write on it all details of each step executed so that I can use it to diagnose any problem or error behind it. Nevertheless, at times set it up may become something not so easy as the .txt file might not be created during the SQL Backup job execution.
There are some reasons why this may be happening. The first one is because the directory where the .txt file does not exist. It should have been created manually before executing the SQL Backup job. The second reason is because the backup directory is blocked. Surprisingly, you will realise it is with blocked access when you try to open it. One way to get it unblocked is by simply opening the directory and then clicking on “continue” via Windows Explorer.
The third one is because the SQL Agent account does not have permissions on that directory. There must have given Read and Write permission on it and there are some cases where we will need to give explicit permissions to the SQL Agent account on that directory via CMD windows command tool. Undoubtedly, it would be no problem if we are working on only one directory but what would it happen whether we are implementing many SQL Backup jobs? it would become a very tedious job to manage one by one. So, in this case we need some manner to automatize and get them done rapidly. For instance, here I am going to show you a technique to achieve it:
icacls "H:\SQLBackup\FinancialDB\Full" /grant MyDomain\sqlagentAccount:(OI)(CI)F
Now for creating the script to give permission on all necessary directories we can create the code by using this T-SQL (based on reading the directory from Backup Devices):
DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT', 'ObjectName', @sn OUTPUT;
select 'icacls "'+SUBSTRING(physical_name,1, LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name)))+'" /grant '+@sn+':(OI)(CI)F'
from sys.backup_devices where name
ORDER BY name
All the output results must be executed on CMD tool (as Administrator) and finally after executing the SQL Backup job we will verify that the .txt file was created:
And the history details are inside:
I hope this tip is useful and practical for you. Let me know any remark you may have. Thanks for reading!