none
grant developer access to tables

    Question

  • I have created a db for one of my developers and i want to give him access to develop to one table in the dB.  I created the table and have setup a sql server user for him. When i grant him select, update and insert on the server he can connect to the sql server and see the DB but not the table i created for him.  If i go to the server and login via Sql server mgmet studio I can't even see the DB.  Waht rights have i missed here?  thanks.  
    Monday, January 20, 2014 4:07 PM

Answers

  • Don't mix up logins which have SQL Server wide permissions, with users which have permissions inside a database.

    You should (and maybe you did) create a login for the developer, and then create a user for the developer that gives access to the appropriate database. Then you should grant the select, insert, update, and delete permission for the table to the user.

    You can't see the database? Are you a member of the sysadmin fixed server role? If not, do you have permission to the database?

    Or perhaps you created the table in another database?

    Or perhaps you created the table with your default schema and he is looking for the table with his default schema which would be a different table?


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, January 20, 2014 5:17 PM
  • Waht I i do notice is you can view all of the system tables when connected in VS 2012.  Is thereany thing you can do about that?  

    Although a user can select from the catalog views and DMVs, meta-data visibility is limited to those object the user has permissions on.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, January 20, 2014 5:35 PM

All replies

  • Don't mix up logins which have SQL Server wide permissions, with users which have permissions inside a database.

    You should (and maybe you did) create a login for the developer, and then create a user for the developer that gives access to the appropriate database. Then you should grant the select, insert, update, and delete permission for the table to the user.

    You can't see the database? Are you a member of the sysadmin fixed server role? If not, do you have permission to the database?

    Or perhaps you created the table in another database?

    Or perhaps you created the table with your default schema and he is looking for the table with his default schema which would be a different table?


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, January 20, 2014 5:17 PM
  • I just figured it out.  it seems that when i was adding the deny to view any database i also checked view any definiton under the securables and once i unchecked that i could then see the table under VS 2012.  Waht I i do notice is you can view all of the system tables when connected in VS 2012.  Is thereany thing you can do about that?  

    Monday, January 20, 2014 5:30 PM
  • Waht I i do notice is you can view all of the system tables when connected in VS 2012.  Is thereany thing you can do about that?  

    Although a user can select from the catalog views and DMVs, meta-data visibility is limited to those object the user has permissions on.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, January 20, 2014 5:35 PM