locked
SQL database permission- View only(database & Table) RRS feed

  • Question

  • I have four user and four user have four database access

    user 1 - A database access

    user 2 B database access

    user 3 C database access

    user 4 D database access

    All four user have full control on database, now i want to remove all the access and give only to view the database & table. They should have only select permission.

    They should not have permission on edit, delete, insert, update and create new table.

    please support me. I google but i am not getting correct solution as per my requirement.

    Thank you.


    Afzalkhan

    • Moved by Dan GuzmanMVP Sunday, January 29, 2017 2:28 PM Move question to more appropriate forum
    Sunday, January 29, 2017 1:58 PM

Answers

  • Start by revoking existing database permissions and role memberships for these users. You can then either add the users to the db_datareader fixed database role or grant database-level SELECT permission:

    USE DatabaseA;
    GRANT SELECT ON DATABASE::DatabaseA TO User1;
    
    USE DatabaseB;
    GRANT SELECT ON DATABASE::DatabaseB TO User2;
    
    USE DatabaseC;
    GRANT SELECT ON DATABASE::DatabaseC TO User3;
    
    USE DatabaseD;
    GRANT SELECT ON DATABASE::DatabaseD TO User4;
    
    
    
    
    
    
    I'll move this question to the more appropriate Security forum.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Sunday, January 29, 2017 2:27 PM
  • Set the user with datareader permission only. Security==>Login for each user. As below

    Please mark as answer if this post helped you

    • Marked as answer by AfzalKhanBH Monday, January 30, 2017 8:32 AM
    Sunday, January 29, 2017 2:34 PM

All replies

  • Start by revoking existing database permissions and role memberships for these users. You can then either add the users to the db_datareader fixed database role or grant database-level SELECT permission:

    USE DatabaseA;
    GRANT SELECT ON DATABASE::DatabaseA TO User1;
    
    USE DatabaseB;
    GRANT SELECT ON DATABASE::DatabaseB TO User2;
    
    USE DatabaseC;
    GRANT SELECT ON DATABASE::DatabaseC TO User3;
    
    USE DatabaseD;
    GRANT SELECT ON DATABASE::DatabaseD TO User4;
    
    
    
    
    
    
    I'll move this question to the more appropriate Security forum.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Sunday, January 29, 2017 2:27 PM
  • Set the user with datareader permission only. Security==>Login for each user. As below

    Please mark as answer if this post helped you

    • Marked as answer by AfzalKhanBH Monday, January 30, 2017 8:32 AM
    Sunday, January 29, 2017 2:34 PM
  • How can Hide the other database like User A cannot able to see User B database?

    Afzalkhan

    Monday, January 30, 2017 8:33 AM
  • How can Hide the other database like User A cannot able to see User B database?

    Afzalkhan

    Monday, January 30, 2017 8:33 AM
  • How can Hide the other database like User A cannot able to see User B database?

    All users with instance-level logins can see the names of other databases on the instance but cannot access them unless authorized.  If the query used to list database names is within your control (e.g. not SSMS), you could use something like SELECT name FROM sys.database WHERE HAS_DBACCESS(name) = 1 to limit the list to those database the user can actually access.

    I don't know the particulars of your situation but another solution is a partially contained database.  That would allow users to be authenticated at the database level instead of the instance. The context database name need to be specified in the connection string  with this method.

    EXEC sp_configure 'contained database authentication',1
    RECONFIGURE;
    GO
    
    CREATE DATABASE DatabaseA
    	CONTAINMENT = PARTIAL;
    GO
    
    USE DatabaseA;
    CREATE USER [User1] WITH PASSWORD='0dDP^2*ddp0dEEN<A888ODS@#dZ"p'; --omit password if Windows account
    GRANT SELECT ON DATABASE::DatabaseA TO User1;
    GO

    See https://msdn.microsoft.com/en-us/library/ff929071.aspx for more information on contained databases.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com



    Monday, January 30, 2017 12:51 PM