2

I had to grant a server permissions to an active directory group, in order to solve a LIVE Bug:

GRANT VIEW ANY DEFINITION TO [mycompany\webDevSeniors]
GO

after the permission was no longer needed I revoked it:

revoke VIEW ANY DEFINITION TO [mycompany\webDevSeniors]
go

this didn't seem to revoke the permission on all databases so I have tried it in a different way:

use master
go
revoke VIEW DEFINITION ON SCHEMA::[dbo] to [mycompany\webDevSeniors]
go

still, when I use one of my procedures to check which permissions are active in a specific database, it shows the "VIEW DEFINITION"

sp_getlogindbpermissionsX @db='ATPayment',@login='MYCOMPANY\webDevSeniors'

enter image description here

as you can see on the picture below, the permission is not there at server level:

enter image description here

Problem Solved:

At the end, this is how I solved this problem:

I downloaded the sp_foreachdb stored procedure by Aaron Bertrand

And then I run the following command on my server:

sp_foreachdb @command='use ?; REVOKE VIEW DEFINITION ON SCHEMA::[dbo] to [mycompany\webDevSeniors]'
   --         ,@print_dbname = 1
            --,@print_command_only = 1
Improve this question
2
  • Where is sp_getlogindbpermissionsX looking,(its not a standard stored procedure)
    – Ste Bov
    Commented Oct 13, 2015 at 11:56
  • it comes from here basically: SELECT a.name AS Name, a.type_desc AS LoginType,b.class_desc AS ClassDesc ,b.permission_name AS ServerLevelPermission,b.state_desc AS PermissionState FROM sys.server_principals a JOIN sys.server_permissions b ON a.principal_id = b.grantee_principal_id
    – Marcello Miorelli
    Commented Jul 24, 2017 at 17:18

2 Answers 2

Reset to default
3

I believe this is because when you ran:

GRANT VIEW ANY DEFINITION TO [mycompany\webDevSeniors]
GO

Grant was given to that particular user. But when you tried to revoke, that was at the schema level.(2nd run). However when initially you gave the GRANT access , it was not specified at object level.Since REVOKE has no affect if the specified permission does not already exist.

I believe starting with creation of user and then assigning the roles accordingly can help here.

Please read Understanding GRANT, DENY, and REVOKE in SQL Server which will help you in this scenario.

Improve this answer
1

Revoking VIEW ANY DEFINITION and revoking VIEW DEFINITION are not the same I don't think.

  • VIEW ANY DEFINITION is at the Server scope
  • VIEW DEFINITION is at the DB scope

You may want to check that first.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.