Grant permission to a new user in SQL Server 2014

Question

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

The following steps have been executed:

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

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

Now I’m trying to assign db_datareader and db_datawriter for the user apostila. I tried it the following ways and none worked:

exec sp_addrolemember 'db_datareader', 'apostila'

ALTER ROLE [db_datareader] ADD MEMBER [apostila]

In doing so I get the following error:

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

That’s because somehow, DB is not understanding that the user apostila exists.

If I log in as apostila and fetch CURRENT_USER he will return guest. That implies that if I put guest in sp_addrolemember, then he accepted and assigned db_datareader to all guests of the application, and this is the point.

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

Answer 1

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

CREATE USER NomeUsuario FOR LOGIN apostila   

ALTER ROLE db_datareader ADD MEMBER apostila