How to set permissions for database in sql azure?

Question

Could anyone please tell me how to grant permissions for database in SQL Azure?

When I execute create table query, I am ending up with following message:

CREATE TABLE permission denied in database testDB

Thanks in advance :)

Answer 1

Ideally, you'd assign only the the permissions needed for the user. If you've just created a new user that you want to be able do anything in the new database, you can run the following command inside of that database using a server admin user:

EXEC sp_addrolemember 'db_owner', 'YourNewUser';

Answer 2

Take a look at these scripts .This may not be the exact script that you are looking for.But this will help you create the script that you want

STEP1 : Ran these scripts on azure masterdb

CREATE LOGIN mydb_admin 
WITH PASSWORD = 'nnn' 
GO
CREATE LOGIN mydb_user 
WITH PASSWORD = 'nnnnnnnnn
GO

'

Step2: Ran the below scripts on the actual azure db  eg., mydb

CREATE USER mydb_admin  FROM LOGIN mydb_admin ;
EXEC sp_addrolemember 'db_datareader', 'mydb_admin';
EXEC sp_addrolemember 'db_datawriter', 'mydb_admin';
GRANT CONNECT TO mydb_admin;
GRANT SELECT TO mydb_admin;
GRANT EXECUTE ON [dbo].[procDumpRowsInto_De_Common] TO [mydb_admin] AS [dbo]

CREATE USER mydb_user FROM LOGIN mydb_user;
EXEC sp_addrolemember 'db_datareader', 'mydb_user';
EXEC sp_addrolemember 'db_executor', 'mydb_user';
GRANT CONNECT TO mydb_user;
GRANT SELECT TO mydb_user;