Grant permission to a new user in SQL Server 2014

I'm trying to assign permissions of db_datawriter and db_datareader to a new user in SQL Manager

The following steps were performed:

-- criação do usuário
CREATE LOGIN apostila   WITH PASSWORD = 'qweQWE123!@#';

-- usuário como dbcreator
ALTER SERVER ROLE  dbcreator  ADD MEMBER apostila;

I am now trying to assign db_datareader and db_datawriter to user apostila. I tried the following ways and none worked:

exec sp_addrolemember 'db_datareader', 'apostila'

ALTER ROLE [db_datareader] ADD MEMBER [apostila]

When doing this I get the following error:

Cannot add the principal 'Apostille', because it does not exist or you do not have permission.

That why somehow, the DB is not understanding that the user apostila exists.

If I log in as apostila and fetch CURRENT_USER it will return guest. This implies that if I put guest in sp_addrolemember, ai it accepts and assigned db_datareader to all the guests of the application, and this is the point.

How to assign db_datareader only to one user and not all guests?

Author: anisanwesley, 2018-09-18

1 answers

You need to create a user to give db_datareader permission. This is because login refers to access in the SQL instance. Users is at the level of bank objects. And since reading is referring to the bank, it is necessary to create a user.

CREATE USER NomeUsuario FOR LOGIN apostila   

ALTER ROLE db_datareader ADD MEMBER apostila
 1
Author: devgirl, 2018-09-19 13:36:45