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
This question is related to
sql-server
database
permissions
For the GUI minded people, you can:
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.
create LOGIN guest WITH PASSWORD='guest@123', CHECK_POLICY = OFF;
Be sure when you want to exceute the following
DENY VIEW ANY DATABASE TO guest;
ALTER AUTHORIZATION ON DATABASE::BiddingSystemDB TO guest
Selected Database should be Master
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'
Source: Stackoverflow.com