Creating a user and grant read access to table level in SQL server

There are some occasions that you need to give limited permissions such as limiting permission in table level. Let say if you grant DB level rad access this permission cascade down to entire DB. However what if you want to grant read access only to few tables? Below i describe an easy way to create a SQL user or windows login and grant table level read access permission.

Launch SQL server management studio.

To create a new user collapse Security -> Right click on User -> Select New user. This way you can create a SQL User account

If you need to give access to domain user select the Windows authentication and follow the next steps.

Go to server roles and make sure Public user role has been granted

Go to User Mapping and select the DB and make sure Public Role membership has been selected

Click Ok to complete the user creation.

Open a new Query window and choose your DB and run the below query.

Template:

GRANT SELECT ON "dbo"."tablename1" TO "<domain>\<username>"
GRANT SELECT ON "dbo"." Tablename2" TO "<domain>\<username>"

GRANT SELECT ON "dbo"." tablename1" TO "<SQL username>"

GRANT SELECT ON "dbo"." Tablename2" TO "<SQL username>"

Example:

GRANT SELECT ON "dbo"."Account" TO "sqlusername"

GRANT SELECT ON "dbo"."Branch" TO "sqlusername"

GRANT SELECT ON "dbo"."Business" TO "sqlusername"

To see how the permissions are now showing, go to DB and collapse Security, then expand Users. Select the user you created then right click and select Properties. If you go to Securables it will show you the granted permission like below.