Difference between SQL Server Azure and SQL Server 200x

azure-sql-database

I'm a SQL Server DBA and I can understand the logic behind logins users roles and etc ( login for server access, user for databases and etc ).

I've asked some questions here but I think I need to choose better how to ask this.

I'm using SQL Server for 5 years+ and now I'm trying to use some azure servers and databases.

I have some doubts about how it works, why I need to use master to create a login, where can I find a list of roles for azure?

AS I can understand now, to create a Login I need to use master and then create a login. then I change to the database I want to use and then I create a user there pointing to the login. Ok.

My question is, how can I create a sysadmin login like we have in SQL Server? I would like to create myself a login that can do everything. what role should I assign my login? I've seen to crate databases and etc but not "do everything you want mr. DBA".

Does azure uses Contained users? That's why I need to create first a login inside Master and then a user inside a database?

Please have in mind that I've read a lot of official microsoft sites related to logins, but I still can't understand what is simple.

I would really like to understand something like "when you create a login, you are creating….then when creating a user, it means…"

With SQL Server I can create a login, then a user inside databases and assign what I want them to do, but there's not visual info about this with azure.

An example of adding a dbowner:

CREATE LOGIN teste123
    WITH PASSWORD = '12e32d2c23dohdas970h9h&(*H&*(H*&(HG*&bhiuosnad22s23sd23'
GO


Use DatabaseIwant
CREATE USER teste123
    FOR LOGIN teste123
    WITH DEFAULT_SCHEMA = dbo
GO

EXEC sp_addrolemember N'db_owner', N'teste123'
GO

and see? I'm creating a DBO. what should I do to create a sysadmin login? to be able to access all databases, create delete and etc? thats the point. Should I create a login and create a user inside all databases as a DBO? because with SQL Server I can simply create a sysadmin and thats it. I have acces to everything.

Thanks all.

Edit: I think I found a usefull page finally:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

Best Answer

Using logins are discouraged. Logins that will be used by your applications should be contained users and not server logins. Create contained users for your applications and users, place one or more database users into a database role and then assign permissions to the database role.

One common reason to create a database user based on a SQL Database server login is for users that need access to multiple databases. Since contained database users are individual entities, each database maintains its own user and its own password. This can cause overhead as the user must then remember each password for each database, and it can become untenable when having to change multiple passwords for many databases. However, when using SQL Server Logins and high availability (active geo-replication and failover groups), the SQL Server logins must be set manually at each server. Otherwise, the database user will no longer be mapped to the server login after a failover occurs, and will not be able to access the database post failover.

As the Sysadmin fixed role does not exist in SQL Azure, if you want to create a "sysadmin" on SQL Azure, in addition to the sysadmin created on the portal, you need to add that sysadmin login to the dbmanager database role in the master database.

CREATE LOGIN YourSysadminLogin WITH PASSWORD = 'hSUj4Grd' -- Dec 13, 2018

CREATE USER YourSysadminLogin FROM LOGIN YourSysadminLogin;

ALTER ROLE dbmanager ADD MEMBER YourSysadminLogin;

YourSysadminLogin, as it belongs to dbmanager, it now can perform privileged tasks like been able to create and drop databases.

If you want YourSysadminLogin to manage logins also (create/alter/drop logins) add it to the loginmanager database role in the master database.

ALTER ROLE loginmanager ADD MEMBER YourSysadminLogin;

Create your YourSysadminLogin as a user on user databases and assign it db_owner.

CREATE USER YourSysadminLogin FROM LOGIN YourSysadminLogin;
GO
ALTER ROLE db_owner ADD MEMBER YourSysadminLogin; 
GO

Now you have created a sysadmin.

Related Question