How do I grant read access for a user to a database in SQL Server?
This is a two-step process:
you need to create a login to SQL Server for that user, based on its Windows account
CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;
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)'
Q8Y
Updated on July 09, 2022Comments
-
Q8Y almost 2 years
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;
-
Q8Y almost 13 yearsunfortunatlly, I can't v=create the login based on the windows account. since, the account for the user it only available on the domain it doesn't availble on the windows account... So, when I use "from windows" it said that --> "Windows NT user or group 'aa.us\bb' not found. Check the name again. " !!! it can't see it... So, is there is a way to let it look in the domain rather than the windows account ?? Note: don't worry, I write my own domain & login name ;) Thanks alot;)
-
Q8Y almost 13 yearsNever mind 'marc_s'... I solved the problem... I seam I was writting the domain name wrong, now its working :) Thanks alot;)
-
Mitch Wheat almost 9 yearsI always end up here when I forget to 'create user for login"!! ;) +1
-
Jurion almost 2 yearsThank you. Why 10 pages of official Microsoft doecumentation NEVER show that example ?