• 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