48

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;
Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
Q8Y
  • 3,801
  • 12
  • 39
  • 38

1 Answers1

118

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)'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • unfortunatlly, 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 Jul 14 '11 at 06:50
  • 2
    Never mind 'marc_s'... I solved the problem... I seam I was writting the domain name wrong, now its working :) Thanks alot;) – Q8Y Jul 14 '11 at 08:19
  • 4
    I always end up here when I forget to 'create user for login"!! ;) +1 – Mitch Wheat Jun 26 '15 at 00:23
  • 1
    Thank you. Why 10 pages of official Microsoft doecumentation NEVER show that example ? – Jurion May 30 '22 at 20:10
  • 1
    @Jurion because Microsoft documentation is just the *best* – Jonathan E. Landrum Dec 19 '22 at 16:58