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;