January 27, 2014 at 8:08 am
I'm working on creating a query to run through a series of privilege checks required by my employer. They require that we meet all the DoD STIGs for our servers, and helpfully the DoD STIG docs include the queries to run to check the privileges.
What I'd like to happen with the query below is, *IF* the inner join returns no results (which would be a good thing,) I'd still like for the [STIGID] and [PrivChecked] to be returned. I'd rather they not be a separate query, as that's a bit harder to read.
Select 'SQL2-00-004200' as [STIGID]
, 'Shutdown' as [PrivChecked]
, SSP.*
, SPrin.*
from sys.server_permissions as SSP
inner join sys.server_principals as SPrin
on SPrin.principal_id = SSP.grantee_principal_id
where SSP.permission_name = 'Shutdown';
Possible? Or more of a PITA than it would be worth and I should toss in a couple "Print [STIGID] = " and output to text?
😉
Thanks,
Jason
January 27, 2014 at 8:50 am
Not an outer join, right? You just want something returned?
Can you give a result set example of what you'd want here? Is it just the first 2 columns with null for others?
January 27, 2014 at 8:53 am
jasona.work (1/27/2014)
I'm working on creating a query to run through a series of privilege checks required by my employer. They require that we meet all the DoD STIGs for our servers, and helpfully the DoD STIG docs include the queries to run to check the privileges.What I'd like to happen with the query below is, *IF* the inner join returns no results (which would be a good thing,) I'd still like for the [STIGID] and [PrivChecked] to be returned. I'd rather they not be a separate query, as that's a bit harder to read.
Select 'SQL2-00-004200' as [STIGID]
, 'Shutdown' as [PrivChecked]
, SSP.*
, SPrin.*
from sys.server_permissions as SSP
inner join sys.server_principals as SPrin
on SPrin.principal_id = SSP.grantee_principal_id
where SSP.permission_name = 'Shutdown';
Possible? Or more of a PITA than it would be worth and I should toss in a couple "Print [STIGID] = " and output to text?
😉
Thanks,
Jason
Pretty sure that something like this will get you started.
Select checked.STIGID,
checked.PrivChecked, s.*
from
(
select 'SQL2-00-004200' as [STIGID],
'Shutdown' as [PrivChecked]
) as checked
outer apply
(
select ssp.*
from sys.server_permissions as SSP
inner join sys.server_principals as SPrin on SPrin.principal_id = SSP.grantee_principal_id
where SSP.permission_name = 'Shutdown'
) s
Please notice that this does not include Principals. When writing this you need to have a unique name for each column and there are a few collisions among those two tables.
--edit--
fat fingers monday. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2014 at 12:50 pm
Steve - Yes, that would be sufficient for what I'm looking for...
And Sean - THANK YOU! I'll tweak what you gave me to return the columns I need / want!
Outer Apply...
Hadn't thought of that one, and haven't used it before (not much on the Dev side of the fence, so not a lot of call for it for what I do)
Once again, thanks!
Jason
January 27, 2014 at 12:56 pm
jasona.work (1/27/2014)
Steve - Yes, that would be sufficient for what I'm looking for...And Sean - THANK YOU! I'll tweak what you gave me to return the columns I need / want!
Outer Apply...
Hadn't thought of that one, and haven't used it before (not much on the Dev side of the fence, so not a lot of call for it for what I do)
Once again, thanks!
Jason
Glad that will work for you. You can read up about APPLY by following the links in my signature to Paul White's articles.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply