Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I grant read access for a user to a database in SQL Server?

I want to grant access to a user to a specific database with read and write access. The user is already available in the domain but not in the DB.

So, how can I give them that access with creating a new user and password?

Someone told me that it can be done with only specifying the user, domain & the DB that you want to give the user the access to without needing to create a new user and password.

This is the old way that I was implementing. It works but it creates a new login and user rather than using the one that is available in the domain:

use DBName; create login a_2 with password='Aa123'; create user a_2 for login a_2; grant insert to a_2; grant select to a_2; 
like image 247
Q8Y Avatar asked Jul 14 '11 05:07

Q8Y


People also ask

How do I grant a read-only access user?

SQL>create user scott_read_only_user identified by readonly; SQL>grant create session to scott_read_only_user; SQL>grant select any table to scott_read_only_user; This will only grant read-only to scott tables, you would need to connect to another schema owner to grant them read-only access.


1 Answers

This is a two-step process:

  1. you need to create a login to SQL Server for that user, based on its Windows account

    CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS; 
  2. you need to grant this login permission to access a database:

    USE (your database) CREATE USER (username) FOR LOGIN (your login name) 

Once you have that user in your database, you can give it any rights you want, e.g. you could assign it the db_datareader database role to read all tables.

USE (your database) EXEC sp_addrolemember 'db_datareader', '(your user name)' 
like image 198
marc_s Avatar answered Oct 12 '22 23:10

marc_s