1. Use the sample
database and T-SQL to create a view that comprises the first and last names of
all employees who entered their projects in the second half of the year
2007. Show the T-SQL statements.
CREATE VIEW v_firstlastnames_2007
AS SELECT emp_fname,emp_lname
FROM employee as e , works_on as w
WHERE e.emp_no = w.emp_no and w.enter_date BETWEEN ‘07.01.2007’ AND ‘12.31.2007’
2. What is a difference
between Windows mode and Mixed mode with regard to security?
In windows mode the user will connect to the database Engine
through windows user credentials. SQL server automatically validates the
credentials in the operating system. Windows mode is more secure than SQL
server authentication and this connection to the database is called trusted
connection. Windows authentication has more level of protection when compare to
SQL Server authentication since it uses Kerberos security protocol.
In mixed mode the user will connect to the database Engine
either through windows authentication or SQL Server authentication. The user’s
credentials are stored in the SQL Server. One of the main problem with mixed
mode is it lacks account lockout capabilities and expose the systems to attack
through SQL server vulnerable.
3. What is a difference
between a SQL Server login and a database user account?
SQL server login allows user to login to the system or the
server. Logins are referred to accounts like windows account or SQL server
authentication so logins are used for authentications. Logins will handle
authentication and server-level permissions like creating database or backup
database. If login needs to access any database the login should be mapped to a
database user. Login can connect to the SQL server but cannot access the
database until unless user should exits to access the database. Any access that
are related to the server should be granted by logins.
On the other hand database user is an additional account to use
specific 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 sample
database and T-SQL to create three logins called ann, burt, andchuck. The
corresponding passwords are a1b2c3d4e5 !, d4e3f2g1h0 !, andf102gh285 !,
respectively. The default database is the sample database. After
creating the logins, check their existence using T-SQL to access the system
catalog. (This problem must be completed if you are going to do problem #
47) Show the T-SQL statements.
Create login ann with Password = ‘a1b2c3d4e5’
Create login burt with Password = ‘d4e3f2g1h0’
Create login chuck with password = ‘f102gh285’
Select name from sys.syslogins