Grant Permission to a table with restriction to data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Grant Permission to a table with restriction...

Grant Permission to a table with restriction to data

Rate Topic

Display Mode

Topic Options

Author

Message

Jan Sorenson

Posted Friday, October 19, 2012 11:34 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 11:38 AM
Points: 265, Visits: 116

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

Sean Lange

Posted Friday, October 19, 2012 12:50 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 8,592, Visits: 8,233

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

Post #1375003

Sean Pearce

Posted Monday, October 22, 2012 8:26 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 345, Visits: 1,323

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.

http://thesqlguy.blogspot.com/

Post #1375454

Jan Sorenson

Posted Monday, October 22, 2012 9:13 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 11:38 AM
Points: 265, Visits: 116

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

kapil_kk

Posted Monday, October 22, 2012 9:14 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 1,061, Visits: 1,151

how can we restricted a view to a particular user only?

Post #1375495

Sean Lange

Posted Monday, October 22, 2012 9:51 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 8,592, Visits: 8,233

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

Post #1375548

Lowell

Posted Monday, October 22, 2012 11:13 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

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

jeetsingh.cs

Posted Friday, October 26, 2012 1:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:06 PM
Points: 179, Visits: 380

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 »

Permissions