Can't not revoke server permission.

  • Hi Folks,

    Please help on this.

    My MS SQL version is MS SQL 2012 EE.

    The problem is when I check the "Server Roles" and "Securables" of this login, "NCL\spdfarmadm" on instance level, I can see this login has "dbcreator", "public", and "securityadmin" role and has the following "Securables".

    "Connect SQL"

    "Control server"

    "View server state"

    If I ran the sql query below, I can see this login has 31 permissions.

    use MASTER

    go

    EXECUTE AS LOGIN = 'NCL\spdfarmadm';

    SELECT * FROM sys.fn_my_permissions(default, 'SERVER');

    entity_namesubentity_namepermission_name

    serverCONNECT SQL

    serverSHUTDOWN

    serverCREATE ENDPOINT

    serverCREATE ANY DATABASE

    serverCREATE AVAILABILITY GROUP

    serverALTER ANY LOGIN

    serverALTER ANY CREDENTIAL

    serverALTER ANY ENDPOINT

    serverALTER ANY LINKED SERVER

    serverALTER ANY CONNECTION

    serverALTER ANY DATABASE

    serverALTER RESOURCES

    serverALTER SETTINGS

    serverALTER TRACE

    serverALTER ANY AVAILABILITY GROUP

    serverADMINISTER BULK OPERATIONS

    serverAUTHENTICATE SERVER

    serverEXTERNAL ACCESS ASSEMBLY

    serverVIEW ANY DATABASE

    serverVIEW ANY DEFINITION

    serverVIEW SERVER STATE

    serverCREATE DDL EVENT NOTIFICATION

    serverCREATE TRACE EVENT NOTIFICATION

    serverALTER ANY EVENT NOTIFICATION

    serverALTER SERVER STATE

    serverUNSAFE ASSEMBLY

    serverALTER ANY SERVER AUDIT

    serverCREATE SERVER ROLE

    serverALTER ANY SERVER ROLE

    serverALTER ANY EVENT SESSION

    serverCONTROL SERVER

    I tried to revoke some of permissions.

    For example,

    use MASTER

    go

    REVOKE SHUTDOWN TO [NCL\SPDFarmAdm];

    The revoke command was successfully complete but I still can see it if I run the select query above.

    Do you know why?

    Thanks!

  • Because it's not an explicitly granted permission. It's part of what CONTROL SERVER grants (which is basically sysadmin rights). You can DENY the right, and the DENY will overrule the GRANT, but a REVOKE just negated a GRANT or DENY and that login didn't have a GRANT on that permission.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you, GilaMonster, for your quick response.

    Yes, I ran the "DENY" command.

    use MASTER

    go

    DENY SHUTDOWN TO [NCL\SPDFarmAdm];

    DENY ADMINISTER BULK OPERATIONS TO [NCL\SPDFarmAdm];

    DENY CREATE ENDPOINT TO [NCL\SPDFarmAdm];

    DENY CREATE AVAILABILITY GROUP TO [NCL\SPDFarmAdm];

    DENY ALTER ANY CREDENTIAL TO [NCL\SPDFarmAdm];

    DENY ALTER ANY ENDPOINT TO [NCL\SPDFarmAdm];

    DENY ALTER ANY LINKED SERVER TO [NCL\SPDFarmAdm];

    DENY ALTER ANY CONNECTION TO [NCL\SPDFarmAdm];

    DENY ALTER ANY DATABASE TO [NCL\SPDFarmAdm];

    DENY ALTER RESOURCES TO [NCL\SPDFarmAdm];

    DENY ALTER SETTINGS TO [NCL\SPDFarmAdm];

    DENY ALTER TRACE TO [NCL\SPDFarmAdm];

    DENY ALTER ANY AVAILABILITY GROUP TO [NCL\SPDFarmAdm];

    DENY AUTHENTICATE SERVER TO [NCL\SPDFarmAdm];

    DENY EXTERNAL ACCESS ASSEMBLY TO [NCL\SPDFarmAdm];

    DENY VIEW ANY DEFINITION TO [NCL\SPDFarmAdm];

    DENY CREATE DDL EVENT NOTIFICATION TO [NCL\SPDFarmAdm];

    DENY CREATE TRACE EVENT NOTIFICATION TO [NCL\SPDFarmAdm];

    DENY ALTER ANY EVENT NOTIFICATION TO [NCL\SPDFarmAdm];

    DENY ALTER SERVER STATE TO [NCL\SPDFarmAdm];

    DENY UNSAFE ASSEMBLY TO [NCL\SPDFarmAdm];

    DENY ALTER ANY SERVER AUDIT TO [NCL\SPDFarmAdm];

    DENY CREATE SERVER ROLE TO [NCL\SPDFarmAdm];

    DENY ALTER ANY SERVER ROLE TO [NCL\SPDFarmAdm];

    DENY ALTER ANY EVENT SESSION TO [NCL\SPDFarmAdm];

    DENY CONTROL SERVER TO [NCL\SPDFarmAdm];

    The problem is after I deny the following permission, I can not execute

    use MASTER

    go

    EXECUTE AS LOGIN = 'NCL\spdfarmadm';

    SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER');

    Msg 916, Level 14, State 1, Line 1

    The server principal "NCL\spdfarmadm" is not able to access the database "master" under the current security context.

  • I found the "VIEW ANY DATABASE" permission was dropped for some reason.

    But when I ran to add it. The command below was successfully completed.

    GRANT VIEW ANY DATABASE TO [NCL\SPDFarmAdm];

    But when I checked, this login still doesn't have the "VIEW ANY DATABASE" permission and got the same error below.

    Msg 916, Level 14, State 1, Line 1

    The server principal "NCL\spdfarmadm" is not able to access the database "master" under the current security context.

  • Weird, at this time, I revoked those permission after I deny.

    use [master]

    GO

    REVOKE ADMINISTER BULK OPERATIONS TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER ANY AVAILABILITY GROUP TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER ANY CONNECTION TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER ANY CREDENTIAL TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER ANY DATABASE TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER ANY ENDPOINT TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER ANY EVENT NOTIFICATION TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER ANY EVENT SESSION TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER ANY LINKED SERVER TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER ANY SERVER AUDIT TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER ANY SERVER ROLE TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER RESOURCES TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER SERVER STATE TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER SETTINGS TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE ALTER TRACE TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE AUTHENTICATE SERVER TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE CONTROL SERVER TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE CREATE AVAILABILITY GROUP TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE CREATE DDL EVENT NOTIFICATION TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE CREATE ENDPOINT TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE CREATE SERVER ROLE TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE CREATE TRACE EVENT NOTIFICATION TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE EXTERNAL ACCESS ASSEMBLY TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE SHUTDOWN TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE UNSAFE ASSEMBLY TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    use [master]

    GO

    REVOKE VIEW ANY DEFINITION TO [NCLPROD\spdfarmadm] AS [sa]

    GO

    Now I re-ran the query below and it is good now.

    EXECUTE AS LOGIN = 'NCL\spdfarmadm';

    SELECT * FROM sys.fn_my_permissions(default, 'SERVER');

    entity_namesubentity_namepermission_name

    serverCONNECT SQL

    serverCREATE ANY DATABASE

    serverALTER ANY LOGIN

    serverVIEW ANY DATABASE

    serverVIEW SERVER STATE

    Thanks again!

Viewing 5 posts - 1 through 5 (of 5 total)

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