locked
Login_access RRS feed

  • Question

  • hi ,

    I have to create login users permission multiple database read only , But I getting error only one database table show another's not showing we are using Sql server 2016

    help me


    ajitkumar

    Wednesday, February 12, 2020 10:14 AM

All replies

  • Which exact error do you get? How does your SQL script to grant permissions look like?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, February 12, 2020 10:17 AM
  • Run

     GRANT SELECT  TO username


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, February 12, 2020 10:19 AM
  • hi,

    I want multiple database access for users , read only mode.

    I was using the below mention query

    USE (your database)
    EXEC sp_addrolemember 'db_datareader', '(your user name)'


    ajitkumar

    Wednesday, February 12, 2020 10:31 AM
  • Or you can try 

    use mydb
    go

    ALTER ROLE db_datareader
      ADD MEMBER MYUSER 

    It works on all databases I tested  before


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, February 12, 2020 10:43 AM
  • Hello,

    Could you post the error message?

    Or you can try another method to create read-only login users to multiple database in SSMS, steps as follow:

    1. Create new login users 
    2. Click Properties of the new login user
    3. Grant the login the 'Select All User Securables' permission in 'Securables' tab
    4. Tick the database requires read-only login user in 'User Mapping' tabe

    Here is an article tutorial how to Granting read-only access to multiple databases in SQL Server.  You can refer to it.

    Hope it will help. Any further question,please tell us.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, February 13, 2020 3:26 AM
  • Hello,
    Have you solved the question?
    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 14, 2020 9:39 AM