CODEWITHSUNDEEP
c#azureazure-sql-database
DSKVP
DSKVP

Reputation: 663

How to set permissions for database in sql azure?

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 :)

Upvotes: 9

Views: 11898

Answers (5)

roney
roney

Reputation: 1082

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;

Upvotes: 3

Caleb Doise
Caleb Doise

Reputation: 460

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

Upvotes: 10

knightpfhor
knightpfhor

Reputation: 9399

Ensure that you're not trying to run the create table script in the master database.

Upvotes: 1

Shaun Xu
Shaun Xu

Reputation: 4656

SQL Azure uses the same security mode as SQL Server. Looks like you use a non-dbo user to create table. I think you can use the GRANT command it get the proper permission.

Upvotes: 0

JuneT
JuneT

Reputation: 7860

have a look at: Managing Databases and Logins in SQL Azure

Upvotes: 1

Related Questions