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
2
  • Where is sp_getlogindbpermissionsX looking,(its not a standard stored procedure)
    – Ste Bov
    Oct 13 '15 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 Jul 24 '17 at 17:18
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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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