Grant access to the audit data using the sqlcmd utility

To grant access to the audit data:

  1. Copy the following script snippet to a document, for example, a Notepad file. 
    
USE SophosSecurity;

DECLARE @stmt NVARCHAR(max);

DECLARE @Account VARCHAR(512)

/* Replace <Domain>\<User> with the actual account name for which to grant access to the audit data. */    

SET @Account = N'<Domain>\<User>' 

IF NOT EXISTS( SELECT * FROM sys.server_principals WHERE name = @Account )
BEGIN
      SET @stmt = N'CREATE LOGIN [' + @Account + N'] FROM WINDOWS';
      EXEC sp_executesql @stmt;
END;

IF NOT EXISTS( SELECT * FROM sys.database_principals WHERE name = @Account )
BEGIN
      SET @stmt = N'CREATE USER [' + @Account + N'] FOR LOGIN [' + @Account + N']';
      EXEC sp_executesql @stmt;
END;

SET @stmt = N'GRANT SELECT ON SCHEMA :: [Reports] TO [' + @Account + N']';
EXEC sp_executesql @stmt;
GO
  2. Replace the <Domain> and <User> placeholders in the statement "SET @Account = N'<Domain>\<User>'" with the domain and username of the user to whom you want to grant access.

    If your computers are in a workgroup, replace <Domain> with the name of the computer where the database is installed. If the user will be accessing the data from a different workgroup computer, the user account must exist on both computers, with the same username and password.

  3. Open the command prompt.
  4. Connect to the SQL Server instance. Type: 
    sqlcmd -E -S <Server>\<SQL Server instance>

    The default SQL Server instance is SOPHOS.

  5. Copy the script snippet from the file and paste it into the command prompt.
  6. Press Enter to run the script.
    After the script runs, the user is granted "Select" permission on the Reports schema of the SophosSecurity database and can access the audit data.
  7. Repeat for each user who needs access.