CODEWITHSUNDEEP
sqlsql-server
Kreol
Kreol

Reputation: 365

How to check whether a user has permission WITH GRANT OPTION?

In MS SQL Server 2012 I can grant a user some permission with a special option "WITH GRANT OPTION":

USE MyDatabase
GRANT ALTER ANY USER TO MyUser WITH GRANT OPTION

Now if I login to the server with this user and check its permission:

USE MyDatabase
SELECT HAS_PERMS_BY_NAME('MyDatabase', 'DATABASE', 'ALTER ANY USER')

It gives my "1" as expected (the user really has the "ALTER ANY USER" permission). But how do I check that the user has this permission with the option "WITH GRANT OPTION"? I'd expect probably something like this:

USE MyDatabase
SELECT HAS_PERMS_BY_NAME('MyDatabase', 'DATABASE', 'ALTER ANY USER WITH GRANT OPTION')

But of course it does not work...

Upvotes: 0

Views: 4265

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

I think you'll have to query sys.database_permissions:

select state from sys.database_permissions
where class_desc='DATABASE' and
      permission_name='ALTER ANY USER' and
      grantee_principal_id = DATABASE_PRINCIPAL_ID()

And state will be G for a plain grant and W for WITH GRANT OPTION

Upvotes: 3

Related Questions