Granting a user Select only Permission or other permissions in SQL

Granting a user Select only Permission or other permissions in SQL

In many cases for the security reasons of Database you want that developer or user should only have select permission to database they cannot perform insert,Update,Delete in the Database in order to give only select permission to user below are the steps and script.

--Create a New Login 

--In our case we created a login Name as TestLogin 

CREATE LOGIN TestLogin

    WITH PASSWORD = 'test#login123';

If you will have password that doesnt match with windows policy or very small, then it will throw an error like

Msg 15116, Level 16, State 1, Line 1

Password validation failed. The password does not meet Windows policy requirements because it is too short.

--Now run below script to create a user for that login

USE Testingnew  ;--only this DB will have select permission

CREATE USER usertest FOR login TestLogin ;

grant select  to usertest

Now connect to user ssms login with TestLogin password-test#login123'

only TestingDB will have select permission try to open other db it will give an error

The database 'XYZ' is not accessible. (ObjectExplorer)

Now run a insert query for the DB (testing new) for which you have created your user.

insert into table1 (col1) values('2')

Msg 229, Level 14, State 5, Line 1

The INSERT permission was denied on the object 'table1', database 'Testingnew', schema 'dbo'.

Try to run a select query 

select * from table1 

It will work without any issue.

If you want to provide other permission like insert update delete to your user then use below script

USE Testingnew

grant  insert, update, delete to usertest

Note:If you will run above code with same login i.e TestLogin it will not works Displaying a message like

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

To solve this connect with your main login i.e sa or the login you used to create your login first time then run 

USE Testingnew

grant  insert, update, delete to usertest










then run a insert query


insert into table1 (col1) values('2')


It will insert record into your table.