70-431 Certification Brain Dump (Configuration Section)

Configuring Server Security principals

(Lesson 4 in Microsoft cert book)

You can use the below as flash cards: print them out and fold over the answers

What are the two authentication modes. And Which one is reccomended	Windows and Mixed Mode. Windows Mode is recommended because you can completely rely on Active Directory's integrated security model.
What two ways can you re-configure the modes of authentication	Through SMSS graphically, right-click the server properties à Security Or Type script create login ….. domain\username from WINDOWS Or Create login …. With PASSWORD = '….'
What are the three other options the cert book offers with the create login script?	Must_change – login & pw must change at login Check_expiration – SQL Server will check the expiration of the login when the user logs in Check_policy – windows will apply the local windows password policy on the SQL Server logins
Why would you use SQL Server login (server thus set to Mixed mode)	If you have a contractor working on an external project and cannot log into the network, or if they can, the bandwidth is too large. If a legacy application requires a login as such. Then you can apply the windows rules to that user login by using the check_policy method
If you have mixed mode and create a user, what is the best practice to handle that user	Add an expiration date when you create a user (create user MyUser with PASSWORD = '…' check_expiration)
What are 8 SQL Server's fixed server roles	Sysadmin Serveradmin Setupadmin Securityadmin Processadmin Dbcreator Diskadmin Bulkadmin
Describe sysadmin fixed server role	Performs any activity in SQL Server. The permissions on this role all fixed server roles
Describe serveradmin fixed server role	Configure server-wide settings
Describe setupadmin fixed server role	Adds and removes linked servers and execute some system stored procedures. (ie: sp_serveroption)
Describe the securityadmin fixed server role	Manage server logins
Describe processadmin fixed server role	Manage processes running in an instance of SQL Server
Describe dbcreator fixed server role	Creates and alters databases
Describe diskadmin fixed server role	Manages disk files
Describe bulkadmin fixed server role	Execute the BULK INSERT statement

Create a user with an expiration date, enabling the password policy

CREATE LOGIN [login name] WITH PASSWORD='password', CHECK _EXPIRATION=ON, CHECK_POLICY=ON

Modify existing login

ALTER LOGIN [login name] WITH PASSWORD = 'password'

Disable login

ALTER LOGIN [login name] DIABLE

Drop a Windows login – or user:

DROP LOGIN [domain\user] or DROP LOGIN [username]

Get login information

Select * from Sys.sql_logins