Login Types in SQL Server 2012
- Windows-authenticated login
- SQL Server–authenticated login
- Asymmetric key
The ALTER ANY LOGIN permission is required to be able to create SQL logins.
As a security principal, permissions can be granted to logins.
The scope of a login is the whole Database Engine and to connect to a specific database on the instance of SQL Server a login is mapped to a database user.
- Permissions inside the database are granted and denied to the database user, not the login.
- Permissions that have the scope of the whole instance of SQL Server (for example, the CREATE ENDPOINT permission) can be granted to a login
Windows-authenticated SQL Server logins
Instance logins in which the operating system handles authentication.
You can map a Windows-authenticated SQL Server login to the following:
- local user account - CREATE LOGIN “<server_name\username>” FROM WINDOWS;
- local security group - CREATE LOGIN “<server_name\username>” FROM WINDOWS;
- domain user account - CREATE LOGIN “<domain_name\username>” FROM WINDOWS;
- domain security group - CREATE LOGIN “<domain_name\group_name>” FROM WINDOWS;
SQL Server–Authenticated Logins (Mixed-mode Authentication)
SQL Server–authenticated login passwords are stored within the master database and are authenticated by the Database Engine instance rather than through the host operating system or a domain controller. This is a useful method when users may be connecting from with none domain accounts especially if the client is running on a different OS
CREATE LOGIN sql_user_a WITH PASSWORD = 'Pa$$w0rd';
CREATE CERTIFICATE Michael_Mouse WITH SUBJECT = 'Michael Mouse certificate in master database', EXPIRY_DATE = '01/01/2016';
CREATE LOGIN Michael_Mouse FROM CERTIFICATE Michael_Mouse; Asymmetric Key Authentication
Unlike certificates, asymmetric keys contain both a public key and a private key. If there is no database master key, you must provide a password when creating an asymmetric key.
CREATE ASYMMETRIC KEY sql_user_e WITH ALGORITHM = RSA_2048; CREATE LOGIN sql_user_e FROM ASYMMETRIC KEY sql_user_e;
Altering Existing Logins
ALTER LOGIN sql_user_a DISABLE;
If the login uses SQL Server authentication, you can use the MUST_CHANGE or UNLOCK options with this statement to configure the login so that the password must be changed at next login or to unlock a locked login.
Login-Related Catalog Views
The sys.server_principals catalog view - provides information about the login creation date, modification date, whether the login has been disabled, and whether the login type is SQL_LOGIN, SERVER_ROLE, WINDOWS_LOGIN, or CERTIFICATE_MAPPED_LOGIN.
sys.sql_logins catalog - view a list of SQL Server–authenticated logins
- You cannot drop a login while that login has an active connection to the database instance.
- You cannot drop a login that owns a SQL Server Agent job, a server-level object, or a securable.
- Although it is possible to drop logins that are mapped to database users, this creates orphaned users.
Denying Server Access
Denying the mydomain\domain_user_z login access to the Database Engine instance, use the following Transact-SQL statement:
USE [master] GO DENY CONNECT SQL "mydomain\domain_user_z"; GO
Credentials store the authentication information that facilitates a connection to a resource external to the Database Engine instance. You can map a single credential to multiple SQL logins, but it is only possible to map a single SQL login to one credential.
CREATE CREDENTIAL RemoteFTP with IDENTITY = 'FTP_Login', SECRET = 'Pa$$w0rd';
Related Posts via Categories
- SQL Logins
- SQL Server Roles
- Managing Users
- Performance Monitor
- Policy-Based Management
- Flexible Database Level Roles
- Managing Database Permissions
- SQL Trace
- Running jobs within SQL Manager