Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grant Permission to a table with restriction to data Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 11:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:03 PM
Points: 265, Visits: 122
Is it possible to grant access to specific columns in a table with restriction to the data with a where clause? Similar to building a view but doing it through Grant.
Post #1374963
Posted Friday, October 19, 2012 12:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
Jan Sorenson (10/19/2012)
Is it possible to grant access to specific columns in a table with restriction to the data with a where clause? Similar to building a view but doing it through Grant.


Are you asking if you can use permissions to prevent somebody from viewing a column based on the contents? Like allow somebody to view some rows but not others because the contents in the row are not allowed? Or do you mean to allow someone to see all the rows but only the contents of some columns based on the contents?

Either way, there is a way to do that, you identified it already, a view.



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1375003
Posted Monday, October 22, 2012 8:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:37 AM
Points: 997, Visits: 3,089
Jan Sorenson (10/19/2012)
Is it possible to grant access to specific columns in a table with restriction to the data with a where clause? Similar to building a view but doing it through Grant.

Using a view is the correct way to do this.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1375454
Posted Monday, October 22, 2012 9:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:03 PM
Points: 265, Visits: 122
I want to only allow a user to view certain columns in a table from SSMS, and well as restrict them to only those row with a certian department code. I know I can do this through a view, but was hoping to not have to create a view because I would then have to name the view something different than the table name. I am guessing that you cannot do this through the GRANT function?
Post #1375494
Posted Monday, October 22, 2012 9:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
how can we restricted a view to a particular user only?


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1375495
Posted Monday, October 22, 2012 9:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
kapil_kk (10/22/2012)
how can we restricted a view to a particular user only?


GRANT select on SomeView to SomeUser



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1375548
Posted Monday, October 22, 2012 11:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 12,962, Visits: 32,501
kapil_kk (10/22/2012)
how can we restricted a view to a particular user only?

you can do that either by checking the suser_name(), or comparing it agasint a table of permissions you create.


a basic examples:
SELECT
'ColumnList',
OBJECT_ID,
CASE
WHEN suser_name() IN( 'disney\lizaguirre','ClarkKent','Bob' )
THEN ''
ELSE name
END As name from sys.columns



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1375627
Posted Friday, October 26, 2012 1:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 27, 2014 11:02 PM
Points: 251, Visits: 618
I think we can create a view than we can
assign permission to a particular user for that view

GRANT SELECT ON view-name TO user-name
GRANT VIEW DEFINITION ON view_name TO user-name
Post #1377424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse