The script below shows how to grant select permissions to a specific table.
I create a login called test.
--Creates a login
USE [master]
GO
CREATE LOGIN [Test] WITH PASSWORD='P@ssw0rd'
GO
I then create a user in the smarts database mapped to my test login
--Create Users in the database
USE [Smarts]
GO
CREATE USER [Test] FOR LOGIN [Test]
GO
I can then either grant select to that specific user on that specific table:
--Grants Select permissions to the table
use [Smarts]
GO
GRANT SELECT ON [dbo].[Customers] TO [Test]
GO
Or you could add the user to the DB_READER database role...This will give the user read access to all database tables!
--Alteratively you could add each user to the db_datareader
USE [Smarts]
GO
EXEC sp_addrolemember N'db_datareader', N'Test'
GO
If you want something in between you can create a user defined role:
--CREATE A User defined role
USE [Smarts]
GO
CREATE ROLE [UR_SELECT_ROLE]
GO
Then add your users to the role who need the same set of permissions:
--AND the test user to the role
USE [Smarts]
GO
EXEC sp_addrolemember N'UR_SELECT_ROLE', N'Test'
GO
The grant select permissions to that role:
--GRANT SELECT permissions on the table to the new role.
--Add the users to this role that need the same permissions set
use [Smarts]
GO
GRANT SELECT ON [dbo].[LAPTest] TO [UR_SELECT_ROLE]
GO
This can all be accomplished through the Management Studio GUI too
Gethyn Elliswww.gethynellis.com