Learner44 (7/22/2013)
Hi Sean ...can you please suggest me any way to achive the following objective.?
what should I do , in order to restrict other 5 users , having same access as me on the server, so that they can not get an idea about that sensitive information in "clientAccountNumber" column.?
This is the problem. If all 6 have the same access they ALL see the same thing. If you want to change access for certain individuals by definition they no longer have the same access. There just isn't any way around this. You MUST do some sort of user level permissions.
If you want to keep your legacy systems working maybe you can create a view to this table that does not include the encrypted data. Then you could rename the original table to something like OriginalTable_Encrypted. Your new view would be the original table name. Then you deny select permission to the table, except for the people you want to view the encrypted data. And you then grant select permission on the new view to the people who can currently select from the existing table.
There really just is no way around this. You are going to have to do some work with permissions in order to do this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/