1. Use the sampledatabase and T-SQL to create a view that comprises the first and last names ofall employees who entered their projects in the second half of the year2007.
Show the T-SQL statements. Answer: use sample;GO CREATE VIEW v_firstlastnames_2007AS SELECT emp_fname,emp_lname FROM employee as e , works_on as wWHERE e.emp_no = w.emp_no and w.enter_date BETWEEN ‘07.01.2007’ AND ‘12.31.
2007′ 2. What is a differencebetween Windows mode and Mixed mode with regard to security? Answer: Windows mode:In windows mode the user will connect to the database Enginethrough windows user credentials. SQL server automatically validates thecredentials in the operating system. Windows mode is more secure than SQLserver authentication and this connection to the database is called trustedconnection. Windows authentication has more level of protection when compare toSQL Server authentication since it uses Kerberos security protocol.Mixed mode:In mixed mode the user will connect to the database Engineeither through windows authentication or SQL Server authentication. The user’scredentials are stored in the SQL Server.
One of the main problem with mixedmode is it lacks account lockout capabilities and expose the systems to attackthrough SQL server vulnerable. 3. What is a differencebetween a SQL Server login and a database user account? Answer:SQL server login allows user to login to the system or theserver. Logins are referred to accounts like windows account or SQL serverauthentication so logins are used for authentications. Logins will handleauthentication and server-level permissions like creating database or backupdatabase. If login needs to access any database the login should be mapped to adatabase user. Login can connect to the SQL server but cannot access thedatabase until unless user should exits to access the database.
Any access thatare related to the server should be granted by logins. On the other hand database user is an additional account to usespecific databases. User grant access to specific objects such as tables,views, etc. Database user doesn’t have permission on the server level.
4. Use the sampledatabase and T-SQL to create three logins called ann, burt, andchuck. Thecorresponding passwords are a1b2c3d4e5 !, d4e3f2g1h0 !, andf102gh285 !,respectively. The default database is the sample database.
Aftercreating the logins, check their existence using T-SQL to access the systemcatalog. (This problem must be completed if you are going to do problem #47) Show the T-SQL statements. Answer: USE sample;Create login ann with Password = ‘a1b2c3d4e5’Create login burt with Password = ‘d4e3f2g1h0’Create login chuck with password = ‘f102gh285’ Use sample;Select name from sys.syslogins
