How to give a user only select permission on a database

Question

Hi ,

I want a to create a new user on a db with only the select permissions (read only access) how can i do this ? i'm working with sql 2008

Answer 1

You could add the user to the Database Level Role db_datareader.

Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.

See Books Online for reference:

http://msdn.microsoft.com/en-us/library/ms189121%28SQL.90%29.aspx

You can add a database user to a database role using the following query:

EXEC sp_addrolemember N'db_datareader', N'userName'

Answer 2

You can use Create USer to create a user

CREATE LOGIN sam
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
USE AdventureWorks;
CREATE USER sam FOR LOGIN sam;
GO

and to Grant (Read-only access) you can use the following

GRANT SELECT TO sam

Hope that helps.

Answer 3

For the GUI minded people, you can:

• Right click the Database in Management Studio.
• Choose Properties
• Select Permissions
• If your user does not show up in the list, choose Search and type their name
• Select the user in the Users or Roles list
• In the lower window frame, Check the Select permission under the Grant column