Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating a second sysadmin on SQL Azure

When creating a SQL Azure server, we get one 'superuser' that has full access to all databases. We would like to create more of these users, but not quite sure how.

We have looked at the following article: http://msdn.microsoft.com/en-us/library/azure/ee336235.aspx

It allows us creating new logins via:

-- first, connect to the master database
CREATE LOGIN login1 WITH password='<ProvidePassword>';

Then we can add the 'dbmanager' and 'loginmanager' roles:

-- first, connect to the master database
CREATE LOGIN login1 WITH password='<ProvidePassword>';
CREATE USER login1User FROM LOGIN login1;
EXEC sp_addrolemember 'dbmanager', 'login1User';
EXEC sp_addrolemember 'loginmanager', 'login1User';

The new user can now connect to our SQL Azure server and see all the databases. However, the user is then unable to connect to individual databases. A workaround is to create a user mapped to a new login in each database and grant that user db_owner rights, but that is cumbersome and requires maintenance.

Is there an easy way of creating another sysadmin user in SQL Azure?

like image 509
David Airapetyan Avatar asked Oct 02 '14 22:10

David Airapetyan


1 Answers

There is only 1 server admin account allowed for Azure SQL Databases. However, you can add an Azure Active Directory (AAD) group as the Active Directory admin, enabling multiple AAD users to be server admins. More details here about using AAD on Azure SQL Database.

like image 132
ashja99 Avatar answered Sep 17 '22 17:09

ashja99