User handling in MS-SQL database



  • I have an MS-SQL installation, controlling different databases. This can be accessed using Microsoft SQL Server Management Studio 18. For doing that, I use Windows authentication.

    In there, I have different databases. One of them has the following permissions (while checking "Properties", "Permissions"):

    Users or roles:
    Name          Type
    ANALYSE       User
    

    Permissions for ANALYSE:
    Permission Grantor Grant With_Grant Deny
    ...
    Connect False False False
    ...
    Connect dbo True False False

    I have modified the "Grant" column checkbox, but that modification is not taken into account.

    I'm doing this because I want "ANALYSE" to be capable of accessing that particular database (I'm working with a connectionstring in a C# Entity Framework related program and I'd like to perform as least as possible source code modifications). When I access that database while having logged in as "ANALYSE" I get the following error message:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    The database  is not accessible. (ObjectExplorer)
    ------------------------------
    BUTTONS:
      OK
    ------------------------------
    

    Two questions:

    • In order to access that database, is it correct trying to check the "connect" feature in the "permissions" page? If not, what else do I need to do?
    • When I click a checkbox, how can I make sure that this gets stored instead of not taken into account?

    Edit: screenshots for better understanding
    As can be seen in the following screenshots, one "Connect" permission is granted, and all tables are indeed called "dbo.", nevertheless simply opening the database is not working:

    Permissions screenshot: enter image description here

    The error message appears when I click on the + at the left of DB_Name: enter image description here



  • In order to access that database, is it correct trying to check the "connect" feature in the "permissions" page? If not, what else do I need to do?

    You also need to grant some permission for example SELECT to be able to read data from this table. CONNECT gives you only possibility to connect and that is all.

    Of course make sure that User is mapped to some Login.

    When I click a checkbox, how can I make sure that this gets stored instead of not taken into account?

    In your case connect is already granted and 'dbo' is grantor of this permission and that is ok. If some permission (like connect) is granted you will see it twice in management studio. So don't pay attention to that. If you click on some checkbox and then OK and you don't get any error you can be sure it is saved. You can also use button "Script" on the top of the window to have a script ready to be executed in SSMS query window so you can also use that option.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2