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?
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