From the course: SQL Server: Security for Developers

Logins and users - SQL Server Tutorial

From the course: SQL Server: Security for Developers

Start my 1-month free trial

Logins and users

- [Instructor] The first layer of security we have is our login and our user account. When we talk about authenticating, we need an identity to authenticate us. Let's talk about how SQL Server handles that. Users and logins are related by different objects within the SQL Server Database Engine. Both users and logins are used to allow rights to various objects like tables, views, and procedures within the SQL Server databases. And logins are also used to connect to the SQL server database engine. As the login is used, so that the person connecting is able to authenticate against the database instance itself, there are also permissions that are set at the instance level that can be granted to logins. These include the permission to connect to the engine, alter other logins, create databases, restore databases, amongst others. Users are created within databases themselves, so that the user is able to access specific databases and objects within those databases. Permissions are granted to that user, so the objects in the database can be accessed. When a user is created, they are mapped to a login, so that when a user connects to the database engine using a login, they can then access the database. You can view all of this information within the system catalog views in SQL Server by looking at the sys.server_principals, and the sys.database_principals catalog views. You'll see the output of the logins created at both the instance level and at the sample database level. SQL Server supports two authentication modes, Windows authentication and mixed mode authentication. SQL Server authentication, which is mixed mode authentication, takes place when the username and the password for the account are both stored within the database engines. These accounts do not have to have any relation to any local or domain user accounts, and can be used by any number of people to connect to the database engine. Windows authentication is based on an account being created and managed on the operating system that is running under a SQL Server, that is joined to a Windows Active Directory domain that the server running the SQL Server is a member of, or has access to via a domain trust. Windows authentication is considered to be more secure than SQL authentication, because with Windows authentication, the username and password are not sent between the client application and SQL Server. Instead, a ticket is generated on the domain controller and passed to the client, who then passes it onto that SQL Server instance where the authentication takes place. The ticket is then verified against the domain controller to make sure that it's a valid ticket, and that it was passed to the SQL Server from the correct main controller. When we talk about Azure SQL Database, the Microsoft Database is a service offering. We also have Azure Active Directory authentication. This relies on Azure Active Directory, which is a slightly different form of Active Directory, but can be connected to your on-premise's Active Directory. SQL Server provides server level roles to help you manage the permissions on a server. These roles are security principals and server level roles, or system roles as I call them here, are server-wide in their permission scopes. Roles are kind of like groups in Windows operating system. The fixed server roles here are provided for convenience and backwards compatibility. To be more secure, what you want to do is assign specific permissions whenever possible. SQL Server provides nine fixed server roles and the permissions that are granted to these roles except public cannot be changed. However, beginning with SQL Server 2012, you can create user defined roles. You can also create database roles, and these include some fixed database roles like db_owner, or you can create custom roles that assign the permissions you want to create. In general, for your application and best security practices, you want to create user-defined roles that give specific permissions to the users in your app and group together.

Contents