I have been tasked to STIG a new installation of SQL Server 2008R2 and I am hoping to automate the process. There are many STIG checks that have the Check queries and fix queries. You need to run the check query and the if there are results then run the fix query based on the results. Below is an example.
Obtain the list of accounts that have direct access to the server-level permission 'View any definition' by running the following query:
Select * from sys.server_permissions where permission_name = 'View any definition'
STEP - 2
If any rows are returned, run the following query, substituting the <grantee_principal_id> with those returned in the previous query.
Select * from sys.server_principals where principal_id = <grantee_principal_id>
STEP - 3
Remove the 'View any definition' permission access from the account that has direct access by running the following script:
REVOKE VIEW ANY DEFINITION TO <'account name'>
My questions are
A: How can I automate the three steps. For example, running the first query, use the results of that in the next query and then again used the results in the REVOKE query.
B: Has someone already invented a script or process to do this.
There are about 50 of these types of STIGs
I thought of a few possible options but have not yet tried getting them to work
A: I thought to use powershell and store the results of the first and second query in an array and then execute the revoke off the array.
B: I thought to create a script that would generate a temp table – then execute step two to create a new temp table and execute the revoke script off that table and then drop table
C: Using a view for each STIG check
D: Creating a Stored Procedure for each check
D: Somehow use a trigger, but not sure if a trigger would actually do the job.
Please let me know what you think or if you have any ideas
Any help is appreciated.