70-457 Reviewer #13 Managing SQL Server Logins

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.

SQL Logins

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


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


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

Creating Credentials

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.

Contained User

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;



And to turn off a specific task or permission from the [dbcreator] role in the example above, you may run the script below,


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


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.

About these ads

2 thoughts on “70-457 Reviewer #13 Managing SQL Server Logins

  1. Pingback: 70-457 Reviewer #13 Managing SQL Server Logins | Coffee and SQL Server

  2. Hello there, I believe your site may be having web browser compatibility issues.
    When I take a look at your website in Safari, it looks fine
    however, if opening in IE, it’s got some overlapping issues.
    I merely wanted to provide you with a quick heads up!
    Other than that, great blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s