locked
How to deny connect to all databases by default and be able to connect to only ones that you specify? RRS feed

  • Question

  • It's SQL Server 14.0.1000.169

    I want to be able to deny connect for certain login to all databases except the ones I choose. I've tried denying "Connect Any Database" for a login and granting connect to a specific database and that did not work, apparently, you can't override "Connect Any Database"' Is this possible, or do I need to specifically deny to every database to login/user?

    It allows connect to a database even if that database has no user mapped to the login, but if you want to deny connect for that login for a database it throws an error that database has no user, that is a strange behavior to me. Logins even have access to insert update and delete for all table in all databases by default, with just public server role. So this is model where a user has all the permissions by default and you need to spend a quite an amount of time end effort to make it work as if it has no permissions only to those that you would give him, this seems like insecure approach.

    • Edited by hbatrnek Tuesday, April 24, 2018 1:05 PM
    Tuesday, April 24, 2018 12:30 PM

Answers

  • Hello,

    User with only Membership of "Public" role can connect to SQL Server and System databases "master" and "temp", but no customer database ... or in all databases the "Guest" Login is active with to many permissions.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by hbatrnek Tuesday, April 24, 2018 1:54 PM
    Tuesday, April 24, 2018 1:28 PM

All replies

  • I want to be able to deny connect for certain login to all databases except the ones I choose. I've tried denying "Connect Any Database" for a login and granting connect to a specific database and that did not work, apparently, you can't override "Connect Any Database"' Is this possible, or do I need to specifically deny to every database to login/user?

    Since your requirement is to be able to deny access to some and allow access to other databases, your best bet would be to deny connect to those databases you don't want the user to access. 

    USE <Databasename>
    GO
    DENY CONNECT TO <UserName>
    GO

    As for your second part, yes, "Connect Any Database" only allows the login to access the database without actually granting any object-level permissions etc. So the user cannot do anything when granted "Connect Any Database". I am curious as to how the login has access to all tables by default. two reasons come to my mind:

    1. Check and see if the users exist in the model database with all the permissions you see for them by default. Whenever a new database is created, it takes the image of model database and if someone created users in the model database with all those permissions, any new database created will have those users with permissions by default.

    2. Check the permissions for the public role in the database. 

    Hope this helps.


    Please click Mark As Answer if my response answered your question or vote as helpful if it helped you in any way


    Tuesday, April 24, 2018 1:04 PM
  • It allows connect to a database even if that database has no user mapped to the login, but if you want to deny connect for that login for a database it throws an error that database has no user, that is a strange behavior to me. Logins even have access to insert update and delete for all table in all databases by default, with just public server role. So this is model where a user has all the permissions by default and you need to spend a quite an amount of time end effort to make it work as if it has no permissions only to those that you would give him, this seems like insecure approach.

    So if someone creates a new database, that login would see and be able to view and edit table data for every new database and I would have to keep a record for login that is not supposed to see new databases and every time the new one is created I need to deny connect on it? Wouldn't a better approach be not to grant access by default?

    My case now it that I need to first create a user for that login on every database (20 databases) and for each one call deny connect.



    • Edited by hbatrnek Tuesday, April 24, 2018 1:16 PM
    Tuesday, April 24, 2018 1:10 PM
  • with just public server role.

    Hello,

    And in which public Server role(s) are the user member? When it's SysAdmin (as I guess) then you can't deny any permissions for the users, SysAdmin always have Access to everything in SQL Server.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, April 24, 2018 1:15 PM
  • The server role is just public, nothing else.

    My case now it that I need to first create a user for that login on every database (20 databases) and for each one call deny connect. I just need to create the login that can do the only thing that I allow him to do, forever, not to be forced to constantly monitor or change his permissions when new objects (databases) are added to the server.




    • Edited by hbatrnek Tuesday, April 24, 2018 1:24 PM
    Tuesday, April 24, 2018 1:19 PM
  • Hello,

    User with only Membership of "Public" role can connect to SQL Server and System databases "master" and "temp", but no customer database ... or in all databases the "Guest" Login is active with to many permissions.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by hbatrnek Tuesday, April 24, 2018 1:54 PM
    Tuesday, April 24, 2018 1:28 PM
  • Thanks! The Problem was the Guest user, it had too many permissions.
    Tuesday, April 24, 2018 1:54 PM
  • Tuesday, April 24, 2018 4:45 PM