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

CREATE LOGIN [newLogin] WITH PASSWORD=’-p@ssw0rd-’ MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON, DEFAULT_DATABASE=[userDatabase]
GO

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]
GO

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>
GO

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’;
GO

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

sp_srvrolepermission;

To add a user to a fixed server role, you may run the below;

ALTER SERVER ROLE [dbcreator] ADD MEMBER [User]

GO

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]
GO

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]
GO

Then to add a user to this user defined role, you may run the below script;

ALTER ROLE [developers] ADD MEMBER [carla]
GO

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 responses to “70-457 Reviewer #13 Managing SQL Server Logins

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

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman

Just another WordPress.com site

Paul Turley's SQL Server BI Blog

from the world of Microsoft SQL Server BI and reporting

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We will improve your knowledge

SQL Studies

 Live, Learn, Share

Behind-the-Scenes of Xeno Hemlock (dot) com

#TeamAPE (author, publisher, entrepreneur)

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Stevespevack's Blog

IT Recruitment

SQL Problems and Solutions

Sharing the knowledge & experience of SQL, DBA & BI

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

Algonquin Studios Blog

Ideas and opinions on software development, web design, customer service, sales and marketing...and food

SutoCom Solutions

Success & Satisfaction with the Cloud

Terli Srinivasa rao Blog

Sql server and Tableau practices

Follow

Get every new post delivered to your Inbox.

Join 48 other followers