Grant permission to a new user in SQL Server 2014

Question

Grant permission to a new user in SQL Server 2014

Navigation

#1 by (1 votes)

1

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

The following steps have been performed:

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

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

Now I am 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:

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

This is because somehow the DB is not understanding that apostila exists.

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

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

sql sql-server

asked by anonymous 18.09.2018 / 22:19

1 answer

How to ensure that the generated JSON does not return error to the Client? Pass controller error to the view

score 1 · Accepted Answer

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 object level of the database. And as a read is about the bank, it is necessary to create a user.

CREATE USER NomeUsuario FOR LOGIN apostila   

ALTER ROLE db_datareader ADD MEMBER apostila