Help with NOT EXISTS logic

Question

Help with NOT EXISTS logic

doodlingdba

Hall of Fame

Points: 3164
More actions
September 10, 2015 at 12:45 am

#303063

Hi all
I need to check to do a check on a built in server role and check it's permissions are in place. So, 'if role has these three permissions, return success, else print some message'. But my problem is that IF NOT EXISTS only cares that something is returned, so as long as one row comes back it prints success, whereas i need it to check for all three. This is what i came up with:
IF NOT EXISTS (
SELECT SP.Name, SPM.permission_name from sys.server_principals SP
INNER JOIN sys.server_permissions SPM ON SP.principal_id = SPM.grantee_principal_id
WHERE
(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW ANY DEFINITION')
OR
(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW ANY DATABASE')
OR
(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW SERVER STATE')
)
PRINT 'Please add permissions to role
Can anyone advise how to ensure I only print the message if all three permissions are not present?? Thanks!!!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

You could just do a count and check if it equals 3.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 2

Quick suggestion (you are almost there)

😎

IF NOT EXISTS (

SELECT SP.Name, SPM.permission_name from sys.server_principals SP

INNER JOIN sys.server_permissions SPM ON SP.principal_id = SPM.grantee_principal_id

WHERE

(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW ANY DEFINITION')

AND --OR

(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW ANY DATABASE')

AND --OR

(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW SERVER STATE')

)

PRINT 'Please add permissions to role';

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SELECT

SP.Name,

m.permission_name

FROM sys.server_principals SP

CROSS APPLY (VALUES ('VIEW ANY DEFINITION'),('VIEW ANY DATABASE'),('VIEW SERVER STATE')) m (permission_name)

WHERE SP.name = 'something'

AND NOT EXISTS (

SELECT 1

FROM sys.server_permissions SPM

WHERE SPM.grantee_principal_id = SP.principal_id

AND spm.permission_name = m.permission_name)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

doodlingdba Hall of Fame Points: 3164 More actions · Answer 4

This is awesome, thanks. I haven't ever used CROSS APPLY so good to see it in action. Now I need to figure out how to only print a message if there are missing permissions, which I have managed to do by placing your script into an IF EXISTS statement, like so:

IF EXISTS (

SELECT

SP.Name,

m.permission_name

FROM sys.server_principals SP

CROSS APPLY (VALUES ('VIEW ANY DEFINITION'),('VIEW ANY DATABASE'),('VIEW SERVER STATE')) m (permission_name)

WHERE SP.name = 'something'

AND NOT EXISTS (

SELECT 1

FROM sys.server_permissions SPM

WHERE SPM.grantee_principal_id = SP.principal_id

AND spm.permission_name = m.permission_name)

)

PRINT 'The role is missing permissions'

But ideally I'd like to print out what those missing permissions are - any ideas? Thanks!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

doodlingdba (9/10/2015)
This is awesome, thanks. I haven't ever used CROSS APPLY so good to see it in action. Now I need to figure out how to only print a message if there are missing permissions, which I have managed to do by placing your script into an IF EXISTS statement, like so:
IF EXISTS (
SELECT
SP.Name,
m.permission_name
FROM sys.server_principals SP
CROSS APPLY (VALUES ('VIEW ANY DEFINITION'),('VIEW ANY DATABASE'),('VIEW SERVER STATE')) m (permission_name)
WHERE SP.name = 'something'
AND NOT EXISTS (
SELECT 1
FROM sys.server_permissions SPM
WHERE SPM.grantee_principal_id = SP.principal_id
AND spm.permission_name = m.permission_name)
)
PRINT 'The role is missing permissions'
But ideally I'd like to print out what those missing permissions are - any ideas? Thanks!

You could use the FOR XML PATH syntax to concatenate the missing permissions into a comma-delimited string and capture the result to a variable. Give it a try, it's all well-documented online. Post back if you get stuck, and post back your final code if you don't so folks here can give it the once-over for you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

drew.allen SSC Guru Points: 76944 More actions · Answer 6

Eirikur Eiriksson (9/10/2015)
Quick suggestion (you are almost there)
😎
IF NOT EXISTS (
SELECT SP.Name, SPM.permission_name from sys.server_principals SP
INNER JOIN sys.server_permissions SPM ON SP.principal_id = SPM.grantee_principal_id
WHERE
(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW ANY DEFINITION')
AND --OR
(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW ANY DATABASE')
AND --OR
(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW SERVER STATE')
)
PRINT 'Please add permissions to role';

Did you not have your morning coffee? Changing the OR to an AND will not fix this, because the OR will allow records that meet any of the criteria, but the AND means the records have to meet ALL of the criteria. Specifically, any one record has to simultaneously have permission_name = 'VIEW ANY DEFINITION', 'VIEW ANY DATABASE', and 'VIEW SERVER STATE'. Since a record can only have one value for a field, no records will meet these criteria.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

doodlingdba Hall of Fame Points: 3164 More actions · Answer 7

Drew - yep, I can confirm AND doesn't work, for exactly the reason you've said...

doodlingdba Hall of Fame Points: 3164 More actions · Answer 8

Thanks Chris - I will look through the FOR XML syntax and see if I can figure it out. All new stuff for me but interesting! Thanks for taking an interest...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

doodlingdba (9/10/2015)
Thanks Chris - I will look through the FOR XML syntax and see if I can figure it out. All new stuff for me but interesting! Thanks for taking an interest...

We're nearing closing time here in the UK so here's one I knocked up for you earlier:

DECLARE @Role VARCHAR(50), @MissingPermissions VARCHAR(200)

SELECT

@Role = pr.Name,

@MissingPermissions = x.MissingPermissions

FROM sys.server_principals pr

CROSS APPLY (

SELECT MissingPermissions = STUFF(

(SELECT ',' + m.permission_name AS [text()]

FROM (VALUES ('VIEW ANY DEFINITION'),('VIEW ANY DATABASE'),('VIEW SERVER STATE')) m (permission_name)

WHERE NOT EXISTS (

SELECT 1

FROM sys.server_permissions pm

WHERE pm.grantee_principal_id = pr.principal_id

AND pm.permission_name = m.permission_name)

FOR XML PATH(''))

, 1, 1, '' )

) x

WHERE pr.name = 'chris'

IF @MissingPermissions IS NOT NULL

PRINT 'The role [' + @Role + '] is missing permissions ' + @MissingPermissions

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183698 More actions · Answer 10

Did you considered Koen's suggestion?

I reformatted your conditions to make it clear what you're doing.

IF NOT EXISTS (

SELECT 1

from sys.server_principals SP

INNER JOIN sys.server_permissions SPM ON SP.principal_id = SPM.grantee_principal_id

WHERE SP.name = 'SomeRole'

AND SPM.permission_name IN( 'VIEW ANY DEFINITION', 'VIEW ANY DATABASE', 'VIEW SERVER STATE')

HAVING COUNT(*) = 3

)

PRINT 'Please add permissions to role';

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 11

drew.allen (9/10/2015)
Eirikur Eiriksson (9/10/2015)
Quick suggestion (you are almost there)
😎
IF NOT EXISTS (
SELECT SP.Name, SPM.permission_name from sys.server_principals SP
INNER JOIN sys.server_permissions SPM ON SP.principal_id = SPM.grantee_principal_id
WHERE
(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW ANY DEFINITION')
AND --OR
(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW ANY DATABASE')
AND --OR
(SP.name = 'SomeRole' AND SPM.permission_name = 'VIEW SERVER STATE')
)
PRINT 'Please add permissions to role';
Did you not have your morning coffee? Changing the OR to an AND will not fix this, because the OR will allow records that meet any of the criteria, but the AND means the records have to meet ALL of the criteria. Specifically, any one record has to simultaneously have permission_name = 'VIEW ANY DEFINITION', 'VIEW ANY DATABASE', and 'VIEW SERVER STATE'. Since a record can only have one value for a field, no records will meet these criteria.
Drew

He he, apparently not enough

😎

doodlingdba Hall of Fame Points: 3164 More actions · Answer 12

Hello Chris! Sorry for the late response, I was away for a few weeks without access to e-mail. This script works an absolute treat! Thanks for taking the time to write it for me - I have learned a useful application of CROSS APPLY with FOR XML PATH.

Thanks again for your input!

doodlingdba Hall of Fame Points: 3164 More actions · Answer 13

Hello Chris! Sorry for the late response, I was away for a few weeks without access to e-mail. This script works an absolute treat! Thanks for taking the time to write it for me - I have learned a useful application of CROSS APPLY with FOR XML PATH.

Thanks again for your input!