locked
How to grant two tables "select only" access to new sql user RRS feed

  • Question

  • Dear All,

    I am searching the solution since long time but didn't fine. i want to create new sql user and grant him two tables access. we have several databases created on same server so we want to allow only two table in ABC database. user should not be able to see other databases and their tables. And user also should not be able to access any other tables in ABC database except two tables. can anyone please advice

    is there any query to deny all tables in schema for all clauses (Select, Update, Insert) then grant two tables to user with select clause?

    Regards,

    Agha


    • Edited by Agha Ali Tuesday, May 5, 2015 1:08 PM crr
    Tuesday, May 5, 2015 12:56 PM

Answers

  • Hello,

    To access a database the login have to be added as a database user; he can't access any other database where he isn't a database user.

    If he isn't member of any database role (beside "Public") he can't access any table/view/stored procedure. So simple grant SELECT permission for those 3 table to the database user; that's it.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Agha Ali Thursday, May 7, 2015 5:17 AM
    Tuesday, May 5, 2015 1:06 PM
  • Thanks All,

    Finally i decided that i will create new database and copy those required tables to new database. after that i will allow new user to access this database.

    but how can we get the real time data from original table to new table in new database?

    is there any trigger need to perform?

    Regards,

    Agha


    • Edited by Agha Ali Wednesday, May 6, 2015 1:10 PM crrt
    • Marked as answer by Agha Ali Thursday, May 7, 2015 5:17 AM
    Wednesday, May 6, 2015 11:34 AM
  • but how can we get the real time data from original table to new table in new database?

    is there any trigger need to perform?

    Transactional replication is probably the best choice. Not the least since in this case you could if needed put the database on a different server.

    Setting up replication isn't too tricky, as there is a wizard. But it's a good idea to read about it Books Online first so that you understand the concept. When you play with it, try in test before you do it production.

    Triggers are doable, but they could be a bit tricky to write, and you will also have some admin work to get the cross-database access to work.

    The nice thing with transactional replication is that if you need third and a fourth table, you just add that table. With a trigger, you need to write yet another one.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Agha Ali Thursday, May 7, 2015 5:16 AM
    Wednesday, May 6, 2015 9:00 PM

All replies

  • Hello,

    To access a database the login have to be added as a database user; he can't access any other database where he isn't a database user.

    If he isn't member of any database role (beside "Public") he can't access any table/view/stored procedure. So simple grant SELECT permission for those 3 table to the database user; that's it.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Agha Ali Thursday, May 7, 2015 5:17 AM
    Tuesday, May 5, 2015 1:06 PM
  • Thanks. i created one sql login user id and mapped with required database. by default user has public role rights so when i am trying to login from that account i can perform any action on all tables in only that database. how can i restrict it?

    can you please advice how to do this, if you can send me the steps:

    regards

    agha


    • Edited by Agha Ali Tuesday, May 5, 2015 1:54 PM crrt
    Tuesday, May 5, 2015 1:19 PM
  • Thanks. i created one sql login user id and mapped with required database. by default user has public role rights so when i am trying to login from that account i can perform any action on all tables in only that database. i want to restrict it.

    other databases i can see but i cannot do any thing with them so it is fine.

    Assuming the server role of the login is public, and the user is mapped to the database with only public role, only the tables you have granted permissions to should be visible to the user.  If that is not the case, check the following:

    a) Is the login a member of an active directory group that also is a login on the server, and has more permissions at the server level, or at the database level?

    b) Has the public role been granted permissions to access other tables in the database? You can check using the following query:

    SELECT  p.state_desc ,
            p.permission_name ,
            u.[name] AS [Schema] ,
            o.[name] ,
            o.[type],
            r.name AS PrincipalName
    FROM    sys.sysobjects o
            INNER JOIN sys.database_permissions p ON o.id = p.major_id
            INNER JOIN sys.sysusers u ON o.uid = u.uid
            INNER JOIN sys.database_principals r ON r.principal_id = p.grantee_principal_id
    ORDER BY u.[name] ,
            o.[name]

    Tuesday, May 5, 2015 1:59 PM
  • Thanks a lot.

    a) Is the login a member of an active directory group that also is a login on the server, and has more permissions at the server level, or at the database level?

    New user is not a member of active directory

    b) Has the public role been granted permissions to access other tables in the database? You can check using the following query:

    i ran the query and found that public role has all the permissions so what to do in this case.

    looking forward to your response.

    Thanks

    Agha

    Tuesday, May 5, 2015 2:21 PM
  • i ran the query and found that public role has all the permissions so what to do in this case.

    Oh hell, that wasn't a good idea to grant all permissions to the database role "Public".

    In this case you have to explicit DENY all permission to all objects without the 3 tables (SELECT perm) for this user; DENY override all granted permissions.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 5, 2015 2:29 PM
  • thanks, i don't know how can i do this, i am not much expert. can you please help me to do this.

    Regards,

    Agha

    Tuesday, May 5, 2015 2:39 PM
  • See DENY Object Permissions (Transact-SQL)

    And you will have to do this for every new object (table) you are going to create in the database.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 5, 2015 2:43 PM
  • thanks, i don't know how can i do this, i am not much expert. can you please help me to do this.

    Regards,

    Agha

    agree with Olaf - public Role should not have full access(based on what said) to the database.

    can you verify that the public role really has full access - it is very uncommon for this to happen and if they do, it should be revoke.

    there is no point restricting access to the two users alone while allowing any user full access through public role. I believe there some oversight or big mistake in database setup.

    My suggestion would be to really verify that the user is getting all the access through public role. make sure there is no oversight .

    manging permissions through individual objects can be a pain,in your case not worth it,in short


    Hope it Helps!!

    Tuesday, May 5, 2015 4:39 PM
  • i ran the query and found that public role has all the permissions so what to do in this case.

    REVOKE SELECT TO public

    Or more precise, make sure that no permissions is granted to public.

    Once you have granted acess on everything to public, it's an uphill battle to create a restricted user, so you might as well as not try, as long as you don't revoke what you granted to public.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 5, 2015 9:26 PM
  • Thanks, we have around 3000 tables so i cannot do one by one table. is there any query to deny all?

    Like this

    DENY ALL Tables for Select to user

    DENY ALL Tables for Update/execute/insert to user

    then Grant only to two tables to user

    i hope there should be the way to do this.

    if i assign db_datareader role to database user it works and all tables become ready only instead of full permissions but i want to restrict more and want to allow user to see only two tables.

    Regards,

    Agha


    • Edited by Agha Ali Wednesday, May 6, 2015 6:12 AM crrt
    Wednesday, May 6, 2015 6:06 AM
  • Or more precise, make sure that no permissions is granted to public.


    +1

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, May 6, 2015 7:25 AM
  • i cannot revoke public role permissions because it is needed for our software.
    Wednesday, May 6, 2015 8:44 AM
  • i cannot revoke public role permissions because it is needed for our software.

    Running GRANT to 3000 tables is not that difficult:

      SELECT 'DENY SELECT ON ' + quotename(s.name) + '.' + quotename(o.name) +
             'restricted_user'
      FROM   sys.objects o
      JOIN   sys.schemas s ON o.schema_id = s.schema_id
      WHERE  o.type = 'U'

    Copy and paste result into a query window. Execute.

    But how do you deal with all new table that may pop up?

    No, it is not a tenable solution. So here are you options:

    1) Forget it all.

    2) Review your software so that you don't have permissions granted to public.

    3) Some other solution, for instance set up transactional replication on these particular tables and grant access to this user in that database.

    The key is that once upon a time you (or your organisation) made a very bad design decision. Now it's coming back to bite you.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 6, 2015 8:59 AM
  • Thanks All,

    Finally i decided that i will create new database and copy those required tables to new database. after that i will allow new user to access this database.

    but how can we get the real time data from original table to new table in new database?

    is there any trigger need to perform?

    Regards,

    Agha


    • Edited by Agha Ali Wednesday, May 6, 2015 1:10 PM crrt
    • Marked as answer by Agha Ali Thursday, May 7, 2015 5:17 AM
    Wednesday, May 6, 2015 11:34 AM
  • but how can we get the real time data from original table to new table in new database?

    is there any trigger need to perform?

    Transactional replication is probably the best choice. Not the least since in this case you could if needed put the database on a different server.

    Setting up replication isn't too tricky, as there is a wizard. But it's a good idea to read about it Books Online first so that you understand the concept. When you play with it, try in test before you do it production.

    Triggers are doable, but they could be a bit tricky to write, and you will also have some admin work to get the cross-database access to work.

    The nice thing with transactional replication is that if you need third and a fourth table, you just add that table. With a trigger, you need to write yet another one.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Agha Ali Thursday, May 7, 2015 5:16 AM
    Wednesday, May 6, 2015 9:00 PM