1

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;
3

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

  • You don't know what you've done! You just saved me from failing an exam! – Ahsan Dec 26 '17 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.