patternsqlMinor
Creating SQL Azure login for single database
Viewed 0 times
creatingsqllogindatabaseazuresinglefor
Problem
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?
I've come up with the above.. am I close? Where would I specify the database which can be accessed?
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?
Solution
Connect to master database and do the following:
Connect directly to database that the new user should own:
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';Code Snippets
CREATE LOGIN YourNewUser WITH PASSWORD = '<strong password here>';CREATE USER YourNewUser FOR LOGIN YourNewUser;
EXEC sp_addrolemember 'db_owner', 'YourNewUser';Context
StackExchange Database Administrators Q#70058, answer score: 2
Revisions (0)
No revisions yet.