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.
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.
yes you can hide with
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.
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
- Right click the Database in Management Studio.
- Choose Properties
If your user does not show up in the list, choose Search and type their name
- 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.
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.