Deny/Revoke permission on sysdatabases

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

Answer 1

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

Answer 2

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, [e-mail]

Answer 3

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

Answer 4

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, [e-mail]

Answer 5

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, [e-mail]

Answer 6

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.