If you must grant permissions on the table itself, you can use column level permissions. See this example below - I am starting from scratch, creating a login, user, a table, and granting that user access to only two columns in the table. The relevant part that is of interest to you is the code in green.USE [master]GOCREATE LOGIN [MyTestLogin] WITH PASSWORD=N'abcd1234dWQ', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ONGOUSE [TestDatabase]GOCREATE USER [MyTestLogin] FOR LOGIN [MyTestLogin]GOCREATE TABLE dbo.MyTestTable (col1 INT, col2 INT, col3 INT);GOINSERT INTO dbo.MyTestTable VALUES (1,2,3);INSERT INTO dbo.MyTestTable VALUES (4,5,6);GOGRANT SELECT ON OBJECT::dbo.MyTestTable(col1,col2) TO MyTestLogin;GOEXECUTE AS USER = 'MyTestLogin'GOSELECT col1,col2,col3 FROM MyTestTable;Msg 230, Level 14, State 1, Line 1The SELECT permission was denied on the column 'col3' of the object 'MyTestTable', database 'TestDatabase', schema 'dbo'.GOSELECT col1,col2 FROM MyTestTable;-- (2 row(s) affected)GOREVERTGODROP TABLE MyTestTableGODROP USER [MyTestLogin]GOUSE MASTERGODROP LOGIN [MyTestLogin]GO
Grant Access to 1 table/ 2columns in a database
Posted - 2013-07-30 : 19:09:15
