3

I want to create separate logins for each database we create on a SQL Azure database server which only have read / edit permissions for a single database and do not have drop permissions, and to automate this process as much as possible. One of the main reasons for doing this is to make sure that Entity framework drop and recreate scripts can't accidentally run against the database.

I found the following which looks very similar to what I'm trying to do

Allow user to do anything within his own schema but not create or drop the schema itself

Am I right in thinking I need to first create a login, then create a user, then create a role and add the user to this role?

CREATE LOGIN dblogin WITH password='m77hHmSk';

CREATE USER dbuser FROM LOGIN dblogin;

CREATE ROLE dbuserrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'dbuserrole ', 'dbuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO dbuserrole ;

I've come up with the above.. am I close? Where would I specify the database which can be accessed?

2

Connect to master database and do the following:

CREATE LOGIN YourNewUser WITH PASSWORD = '<strong password here>';

Connect directly to database that the new user should own:

CREATE USER YourNewUser FOR LOGIN YourNewUser;

EXEC sp_addrolemember 'db_owner', 'YourNewUser';

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.