6

Can anyone please share the steps to hide the particular column from the table in SQL Server 2012 as I don't want to delete that column

By hide I mean that whenever I use the select query against that particular table it should never show me that column.

Is it possible? I need to make the column as hidden irrespective of any user login and whatever query i use

3rd party edit

Based on the comment But the problem is whenever i open the table in sql i dont want to see that particular column i assume that the question is:

  • How can i configure so that opening a table definition inside sql management studio to only show the columns the connected user has select right to?

The screenshot below shows all columns of the table Employee despite the fact that the login StackoverIntern has no select rights to the columns SSN, Salary

SSMS table defintion shows all columns

Ameya Deshpande
  • 3,580
  • 4
  • 30
  • 46
Deepti
  • 101
  • 1
  • 1
  • 10
  • 1
    Please describe what you mean by "hide". – Gordon Linoff May 16 '16 at 21:36
  • Edited my question @GordonLinoff – Deepti May 16 '16 at 21:38
  • 1
    https://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/ – Iłya Bursov May 16 '16 at 21:39
  • Possible duplicate of [Security against select expressions in SQL](http://stackoverflow.com/questions/11595312/security-against-select-expressions-in-sql) – surfmuggle May 16 '16 at 21:42
  • Thanks a lot for your response but my question is i want to hide particular column of table in sql not on the basis of any particular user but at the same time when my java code makes sql connection and display the hidden column it should be able to display – Deepti May 16 '16 at 21:50
  • sounds like you might need a separate table for "hidden" columns and just join to it when you need the values – JamieD77 May 16 '16 at 22:01
  • 1
    And how does the java application connect? It must have some user context. Anyway, if you don't want this column in your java application, just don't include it in your SELECT clause. You do know that `SELECT * ` is usually poor practice, right? – Joel Coehoorn May 16 '16 at 22:04
  • Yes i know select * is a poor practice But the problem is whenever i open the table in sql i dont want to see that particular column as it is useless column but i simply not supposed to delete it and i have to hide it so this is the reason i am looking is there any option in sql to hide the column – Deepti May 17 '16 at 18:47
  • As joe said the database-connection uses a certain user that has certain roles / rights. [Lashane](http://stackoverflow.com/users/2864275/) linked to _How to set select rights for columns for a role?_ If a user with that role tries to `Select * from Employee` errors are returned for each column. But this does not change that you see this column in [Managment Studio](http://stackoverflow.com/tags/ssms/info). Is the core of your question **What can i do that opening a table definition inside [tag:ssms] only shows columns the connected user has select rights to?** – surfmuggle May 17 '16 at 22:33

3 Answers3

8

Late post but I think its worth to share

Earlier to SQLSERVER-2016 there was no any option to hide few columns from table when selecting *, however SQLSERVER-2016 come up with HIDDEN keyword by which you can now set columns hidden from Select * which you don't want to show and want only for some background process of your business logic.

The HIDDEN property is optional and will hide these columns from a standard SELECT statement for backward compatibility with our application and queries. You cannot apply the HIDDEN property to an existing column

.

you can alter existing table as well lets take an example of existing table

ALTER TABLE [dbo].[Account] ALTER COLUMN [StartDate] ADD HIDDEN;
ALTER TABLE [dbo].[Account] ALTER COLUMN [EndDate] ADD HIDDEN;

You can check this concept used more often in Temporal table

you can find more on this in below Temporal Table

Ameya Deshpande
  • 3,580
  • 4
  • 30
  • 46
3

You can use column level permissions so that targeted users cannot select on that column. However, this will not "hide" the column in the case of doing a SELECT * or SELECT SpecialColumn. Instead, it will fail the query, resulting in an error.

An alternative to allow easier queries, you can make a View that does not include this column:

create view MyTableEx As
    SELECT Every, Other, Column
    FROM MyTable

Then only grant SELECT permissions to the View, rather than the table, for certain users. However, this is still problematic for an application, which now has to know whether it should select from the Table or the View.

When it comes down to it, column level permissions are kind of an unnatural thing to do in a database.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Unless the view name is the same as the table name. The OP could use a separate schema for the views and only allow that particular schema access via the login used. `Table Name: [dbo].MyTableName` `View Name: [CustomSchema].MyTableName`. Give the login access to the [CustomSchema]. – Boyd P May 16 '16 at 22:11
0

If you do not want the column to show, then you should not include it in you select statement. It is also more efficient to not use an asterisk (*) in your select statement.

See this post for more info in the performance issue: Performance issue in using SELECT *?

Community
  • 1
  • 1
jelliaes
  • 485
  • 5
  • 18