Sql-server – Cannot find the user, because it does not exist or you do not have permission

permissionssql server

I am using mssql and I would like to achieve this:
Have 1 database, 2 non-SA users. 1st user should be owner of database, 2nd one should have read permissions on some tables, read+write on others.

Tables for 2nd user are created by our application which uses 1st user.

Steps to what I do:
1) I execute script which creates DB + users

CREATE LOGIN USER1
WITH PASSWORD = 'password';
GO
CREATE USER USER1 FOR LOGIN USER1;
GO

CREATE DATABASE my_database;
ALTER AUTHORIZATION ON DATABASE ::my_database TO USER1;

CREATE LOGIN USER2
WITH PASSWORD = 'password';
GO
CREATE USER USER2 FOR LOGIN USER2;
GO

2) My application executes scripts which creates tables + grant privileges on tables to USER2, this fails, because:

Cannot find the user 'USER2', because it does not exist or you do not 
have permission.

My question is how to grant privileges to USER1 to add read/write privileges for another users within my_database? I though, that database owner is enough.

Example how I grant privileges to USER2:

GRANT SELECT ON OBJECT ::schema_version TO USER2;

Best Answer

You've created the login for user1 and user2 to the master database not the database you want.

rearrange to:

CREATE LOGIN USER1
WITH PASSWORD = 'password';
GO

CREATE DATABASE my_database;
ALTER AUTHORIZATION ON DATABASE ::my_database TO USER1;

CREATE LOGIN USER2
WITH PASSWORD = 'password';
GO
USE my_database;
GO
CREATE USER USER2 FOR LOGIN USER2;
GO

Then you can add permissions to that user as it will exist in that database.

NOTE: you may want to go into your master database (or whichever database you were running those commands in before) and remove USER1 and USER2

Related Question