I could fix this issue with the following steps:

  1. Create login
  2. Create DBO user and assign it to a specific database
  3. ADD this user to the MASTER database ( create user x for login x)
  4. DENY SELECT On SCHEMA::sys To x

But since there's a lot of logins in the server, I don't want to add ALL of them in the master database to deny this permission. For now, ALL users have DBO role, because both dev and the user uses the same login (yeah..I know...I really know ). It would be hard to track future issues.

Is there a better way to do this? (To deny select on sys tables and information_schema).

And one more question. WHY ReadOnly has access to sys tables (sys.databases for example )?

  • 1
    It's unclear what you're trying to accomplish, and so there's not really a question there that can be answered. It looks like you may be trying to control Metadata Visibility. If so, read docs.microsoft.com/en-us/sql/relational-databases/security/… – David Browne - Microsoft Sep 3 '18 at 14:48
  • I edited my question so its clearer now. The other part was just to hear some cool opinions about best practices with how to manage login. I think now the question is clearer of what I want ( Deny sys views and information schema to specific logins ).Thanks david – Racer SQL Sep 3 '18 at 14:52
  • But what are you trying to accomplish? Setting permissions on system views is not normal, and likely won't accomplish whatever you intend. – David Browne - Microsoft Sep 3 '18 at 15:16
  • Why do you add users to the master database? – Dan Guzman Sep 3 '18 at 15:20

Your Answer

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

Browse other questions tagged or ask your own question.