Tuesday, May 1, 2012

Setup Azure Database user permission

When doing the F# Azure stuff, I bump into issues about setting up a Azure database which supposed to be easy, but not!

When I am asked why I want to do this, I really got upset. Just answer my question and stop asking me why! Should I tell you I do not want to share my password? Must be a "won't fix" or "by design" scenario where I should only create one user. Ok, Ok, all my fault.

Anyway, here is the procedure:

I use Visual Studio 11's Server Explorer and then create a "new query" from right-click menu.

  • Create login in the master database
    CREATE LOGIN login0 WITH password='< your password >';
  • create user in the database you want to login, remember to switch out of master database
    CREATE USER user0 FROM LOGIN login0;

  • grant permission to the user

the last step is to set up the permission by choose one of the following

EXEC sp_addrolemember 'db_datareader''user0'
EXEC sp_addrolemember 'db_datawriter'' user0 '
EXEC sp_addrolemember 'db_accessadmin'' user0 '
EXEC sp_addrolemember 'db_backupoperator'' user0 '
EXEC sp_addrolemember 'db_ddladmin'' user0 '
EXEC sp_addrolemember 'db_denydatareader'' user0 '
EXEC sp_addrolemember 'db_denydatawriter'' user0 '
EXEC sp_addrolemember 'db_owner'' user0 '
EXEC sp_addrolemember 'db_securityadmin'' user0 '
remember to use login0 to login.

GRANT EXECUTE ON < stored procedure >  TO user0
deny select on < table or view > to user0

No comments: