Managing Logins
If you have worked with SQL Server a while you may recognize there is a difference between logins and users. When it comes to managing security with SQL Server it is important to know the difference, and the differences that have come about over the last few editions of SQL Server. Logins and Users can be broken down pretty easily.
- Logins – A login provides the ability to log on to the SQL Server in general. Very similar to when you log on to your laptop. This is the layer of security that says that login has access to that machine.
- Users – A user is the permissions that a login carries into the database itself. The user is how SQL Server determines if you have permissions to see payroll data or not.
A login is to the SQL Server Instance level while the user account covers the database. There are many errors that can be corrected when this is understood. Over the past couple releases of SQL Server a few variations have been introduced (future posts). When it comes to applying this understanding to Azure SQL Databases the mold breaks quickly.
Consider that with Azure SQL Databases may not be bound to any one server; this database could be on one machine today and another tomorrow. The flexibility benefit that Azure provides adds a different layer to how security is handled. Consider this when we compare Azure SQL Databases to what is commonly referred to On-Premise or OnPrem SQL Servers.
With a traditional OnPrem SQL Server a DBA can look at system views or the user interface to gather information about logins. For example:
Select * from sys.syslogins
With Azure SQL Databases use a different view called sys.sql_logins. For example:
Select * from sys.sql_logins
Notice the slight difference in the name. A nice feature is that you can query the sql_logins view with and OnPrem system as well. However, if you query the sql_logins view on an OnPrem server the results will be displayed for SQL Server Authenticated accounts only.
It is important to know the differences between OnPrem SQL Server and Azure SQL Databases. Many of the traditional tasks have changed however the skills transfer very easily.