how to create a login with only read rights?

Question

Post reply

how to create a login with only read rights?

anjali.vishwakarma

Ten Centuries

Points: 1112
More actions
January 22, 2010 at 2:21 am

#138431

I want to create a SQL login with only read rights..that is the user should be able to only view the structure and data of tables. That user should not be able to update the structure or data of any table. And it should be SQL server authentication login not the windows authentication.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

Suresh B.

SSC-Insane

Points: 22986

January 22, 2010 at 3:19 am

#1107203

Add the user to db_datareader database role only.

alpeshgediya Old Hand Points: 322 More actions · Answer 2

hey...

First create SQL login... then grant permission to that record... use below query.

GRANT SELECT ON (YOUR table Name ) TO (YOUR sql login name).

it allow only reading of records...

anjali.vishwakarma Ten Centuries Points: 1112 More actions · Answer 3

When we create a user with database db_datareader it restricts on all tables. My requirement is :

1. Should not be able to modify any table

2. Should be able to update data of some tables

3. Should not be able to update data of some tables

How can I achieve this?

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 4

My recommendation would be:

1. Create a database role or as many roles you will need.

2. Assign appropriate permissions to this role

3. Assign the users to the database role you have created.

Hope that gives you some direction. A hint; In SMSS expand to Security folder and you should be right click on "Database Roles".

Thanks

nageshp Right there with Babe Points: 719 More actions · Answer 5

1) Open Microsoft SQL server Management Studio

2) Expand security and right click on Logins then select new login.

3) Under the General page give a login name as ‘ABC’ and select sql server authentication specifying the password. Uncheck enforce password policy

3) Select the user Mapping page map the checkbox against the database and

Then check db_datareader checkbox

1) Right click on the particular table and select properties

2) Select Permissions page and click on Add tab under user or role and then add the

‘ABC’ login name

3) Select the name and give the permission that you would like provide under

explicit permissions for ‘ABC’

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

vstitte (1/29/2010)
My recommendation would be:
1. Create a database role or as many roles you will need.
2. Assign appropriate permissions to this role
3. Assign the users to the database role you have created.
Hope that gives you some direction. A hint; In SMSS expand to Security folder and you should be right click on "Database Roles".
Thanks

This is the preferred / recommended method. One step further would be that you would create stored procs that execute either the selects, updates, inserts, or deletes that are necessary. Assign permission to execute those procs to the Role. No direct table access, users are placed in groups and your needs are met.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events