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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With