This lesson covers managing security principals at the instance level. Security principals at this level allows access to the instance and serve as gateway that allow access to database hosted on the instance. This part of the lesson covers 18% of the certification exam.
Logins are the credentials that authenticate connections to an instance. Except in the case of contained databases, a database user must map to an existing SQL server login.
To create a SQL Login you can execute a script below
CREATE LOGIN [newLogin] WITH PASSWORD=’-p@ssw0rd-‘ MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON, DEFAULT_DATABASE=[userDatabase]
Windows Authenticated SQL Logins
Windows authenticated SQL server logins are instance logins in which the operating system handles the authentication. You can map a windows authenticated SQL server login to a local user account, security group, a domain user account or a domain security group.
To create a Windows Login you can execute a script as below
CREATE LOGIN [domain\user] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
Because of the differences between SQL Server Authentication and Windows Authentication, SQL Server authentication is considered to be less secure between the two. This is because when trying to connect to SQL server, you just need to pass the login name and the password, while when using Windows authentication, you are using the windows authentication process that passes the windows token.
You can manage four privileges for each login within the SQL server instance;
1. Alter – this privileges allows the user to make changes to the second login that the right was granted to. Altering a login gives the granted login the ability to change password, default database, default language and so on of the grantee login
2. Control – this privilege grants the user the other three privileges to the grantee login.
3. Impersonate – this privilege grants the granted user the ability to use the EXECUTE AS syntax specifying the grantee login the ability to execute the code as the grantee login.
4. View Definition – this privilege grants the granted user the ability to view the configuration of the grantee login
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 a multiple SQL logins, but it is only possible to map a single SQL login to one credential.
To create a new credential you may run the script below, supplying the needed details
CREATE CREDENTIAL <namehere> WITH IDENTITY = <identity>, SECRET = <secret>
Then you can proceed to map an existing user to the new credential created above. You can use this credential as a way to impersonate identity in SQL server.
Alternatively, you can open your SSMS and navigate to Security node, Credentials node, right click and choose New Credential. Enter the credential name, credential identity, the password associated with the credential and choose whether to associate the credential with a specific encryption provider.
SQL Server 2012 introduces a new concept called contained user, which is user within contained database. A contained user exists only within the contained database and the password for the contained user exists within the database and not within the master database. As the contained user exists only within the contained database, no instance level rights can be granted to a contained user.
Contained users are created by using the CREATE USER statement as shown below
CREATE USER <containedUser> WITH PASSWORD = ‘password';
Contained users can be created based on local or domain Windows accounts, or they can be created as SQL Server users. A contained Window user is simply a Windows account that doesn’t have a corresponding login at the server level. Contained SQL users do not have the option of being configured to follow domain policies like regular SQL Logins.
Fixed Server Roles
SQL Server has nine fixed server roles that cannot be changed and pre defined by Microsoft.
1. bulkadmin – this role gives its members the right to bulk insert data into the database. In older versions of SQL bulk loading data into the database required the sysadmin role, a very powerful role
2. sysadmin – the most powerful role that grants the user to perform any activity against any database without any restriction
3. diskadmin – grants the users the rights to manage physical database files.
4. dbcreator – grants the users the right to create database
5. setupadmin – grants the users rights to add and remove linked servers
6. processadmin – grants the right to kill other users process within SQL server instance.
7. Securityadmin – enables the members of the role to GRANT, DENY and REVOKE all server-level permissions as well as any database level permission
8. Serveradmin – enables the members to change any server wide configuration option as well as use SHUTDOWN command to shut down SQL server. Serveradmins are the guys who takes care of the linked servers.
9. Public – which grant no rights but has view permissions but all logins are members of this public role. By default, all created users are member of the public role.
In SQL server 2012 you can create user defined roles. The user defined roles can be made member of the fixed server role . any server wide right that can be granted to a login can be granted to a user defined role.
To view the all the server roles and their current permission you may run this built in stored procedure
To add a user to a fixed server role, you may run the below;
ALTER SERVER ROLE [dbcreator] ADD MEMBER [User]
And to turn off a specific task or permission from the [dbcreator] role in the example above, you may run the script below,
DENY ALTER ANY DATABASE TO [User]
Example above allows your user to be member of the [dbcreator] role but not allowing him to alter database any created database.
To create a user defined server role via T-SQL, you may run script below
CREATE SERVER ROLE [developers] AUTHORIZATION [sysadmin]
Then to add a user to this user defined role, you may run the below script;
ALTER ROLE [developers] ADD MEMBER [carla]
Exam tip: you need to be familiar with the create logins scenarios.
Next lesson will tackle more on database roles and application roles.