locked
Deny/Revoke permission on sysdatabases RRS feed

  • Question

  • Hi,

    I want to revoke/deny select on master..sysdatabases from a specific user. 

    I ran DENY select ON master.dbo.sysdatabases to User1
    REVOKE select ON master.dbo.sysdatabases from User1

    But it doesn't help. User1 still can run select * from master.dbo.sysdatabases.
    Can anyone help me on this issue?

    Thanks
    Tuesday, October 4, 2011 6:25 PM

All replies

  • REVOKE removes any previous permission statement, both GRANT AND DENY, and returns the user to neutral. In your case, your REVOKE removed the DENY. So just do:

    DENY select ON master.dbo.sysdatabases to User1;

    And then stop. But also DENY sys.databases.

    DENY SELECT ON sys.databases TO User1;

     


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, October 4, 2011 9:26 PM
  • I think DENY on sys.databases can cause quite some problems.

    Better is probably to REVOKE VIEW ANY DATABASE for this user. This permission is granted by default. Without this permission, you can only see the system databases, and any database you own.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, October 4, 2011 9:37 PM
  • Thank you very much for your reply.

    I am sorry I posted the statements in incorrect order.

    At the first time I tried to revoke the permission by running REVOKE select ON master.dbo.sysdatabases from User1 But it did not help.

    Then I tried with DENY select ON master.dbo.sysdatabases to User1 It did not help either.

    Right now I tried your suggestion DENY select ON master.dbo.sysdatabases to User1; and DENY select ON master.dbo.sysdatabases to User1;

    But It cannot help. The User1 still can run select * from master.dbo.sysdatabases. I am using SQL Server 2005 Standard Edition.

    REVOKE removes any previous permission statement, both GRANT AND DENY, and returns the user to neutral. In your case, your REVOKE removed the DENY. So just do:

    DENY select ON master.dbo.sysdatabases to User1;

    And then stop. But also DENY sys.databases.

    DENY SELECT ON sys.databases TO User1;

     


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty



    • Edited by lazymouse Tuesday, October 4, 2011 10:50 PM
    Tuesday, October 4, 2011 10:28 PM
  • Thanks for your help. I tried Revoke view any database to User1. But it did not help. :(

    I think DENY on sys.databases can cause quite some problems.

    Better is probably to REVOKE VIEW ANY DATABASE for this user. This permission is granted by default. Without this permission, you can only see the system databases, and any database you own.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 4, 2011 10:29 PM
  • Can you run this batch and post the output?

    EXECUTE AS LOGIN = 'User1'
    go
    SELECT * FROM fn_my_permissions(NULL, NULL)
    go
    REVERT

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, October 5, 2011 9:47 PM
  • Hi Lazymouse,

    Could you please let us know the latest update?


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, October 11, 2011 6:55 AM