3

Is there a way to restrict a user from viewing the table structure? I just want them to have a SELECT, INSERT, UPDATE, DELETE access only on that table. I want to restrict a specific programmer on the following: 1. He is not allowed to see any table definition 2. He is not allowed to create Stored Procedure 3. Since there were already Stored Procedures (SPs) for saving, updating, selecting and deleting records, that programmer will just use this SPs in accessing/manipulating records. Overall, for security reason, we don't want that programmer to have any of design of the database.

  • 1
    What are you hoping to accomplish by hiding the table definition? – Max Vernon Mar 2 '17 at 15:17
  • If I can SELECT * then I can view the table structure. If I can't view the table structure, it's likely that my INSERT / UPDATE / DELETE statements would be incorrect. As Martin Smith indicated below, it's trivial to view the data types merely by selecting into another table. Or I could just use SSMS to get the CREATE TABLE DDL. Seems like an XY Problem to me. – Michael McGriff Mar 2 '17 at 15:48
6

This seems a strange request but it is possible.

GRANT SELECT ON YourTable TO FOO;
GRANT INSERT ON YourTable TO FOO;
GRANT UPDATE ON YourTable TO FOO;
GRANT DELETE ON YourTable TO FOO;
DENY VIEW DEFINITION ON YourTable TO FOO;

They will of course be able to see the names of the columns by issuing a SELECT * and be able to get the datatypes with a bit more effort by select into a new (possibly #temp) table. They will lose intellisense on that table.

You also say

Since there were already Stored Procedures (SPs) for saving, updating, selecting and deleting records, that programmer will just use this SPs in accessing/manipulating records.

In that case probably they don't need any permissions at all on the table. If the table and stored procedures have the same owner you just need to grant exec permissions on the procedure and the access to the table in the procedure will still succeed through ownership chaining.

  • Your Example is awesome to understand the new user. +1 – Md Haidar Ali Khan Mar 2 '17 at 8:32
  • 1
    @MdHaidarAliKhan - I haven't tested in 2008R2 though. It works as desired in 2014. Not sure if your extended property answer is needed in older versions? – Martin Smith Mar 2 '17 at 8:35
5

yes you can hide with sp_addextendedproperty DMV.

suppose you have a database mycompany schema users in which you have a table with important information containing telephone numbers, address and many more details table emp_details. you could hide the table from viewing in management studio with the help of sp_extendedproperty.

usage:

EXEC sp_addextendedproperty 
@name = N'microsoft_database_tools_support', 
@value = '<Hide? , sysname, 1>', 
@level0type ='schema', 
@level0name ='users',  --give the schema name here. if it is dbo give 'dbo'
@level1type = 'table', 
@level1name = 'emp_details'  --give the table name which you want to hide.
Go

And to provide only select ,insert,update and delete permission to particular User through Management studio

  1. Right click the Database in Management Studio.
  2. Choose Properties
  3. Select Permissions

  4. If your user does not show up in the list, choose Search and type their name

  5. Select the user in the Users or Roles list In the lower window frame, Check the SELECT,INSERT, UPDATE, DELETE permission under the Grant column

if you want to give only VIEW access instead of table access to the external client on the database. To do this way

DENY SELECT ON table TO user;  //  To Deny on the particular table to user


GRANT SELECT ON view TO user; // To give view permission to particular user.

For further ref How to hide a table in sql server management studio and Here

Note :- Don't directly run the script in production server. without testing in Test Server.

-1

DbDefence can hide database schema and close database completely even for DBA. It is free for SQL Server 2005 and 2008. As well as free for other SQL Server for small databases.

Disclaimer: I'm associated with the vendor.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.