Q. Explain the difference between Windows Only authentication and SQL Server and Windows (Mixed) authentication.
A. With Windows authentication the user does not need to log on to the server more than one time. Users you add to the SQL Server then gain the specified access to the server. With Windows authentication, you also get all the benefits of the operating system login process. For example, you can enforce minimum password length.
With SQL Server and Windows (Mixed) Authentication you can use SQL Server logins on the Windows 9x platform. You do not have to have organized Windows domains, and finally, SQL Server and Windows (Mixed) authentication enables you to support non-Windows users such as Novell users.
Q. Explain the SA login and what it can do.
A. The SA login is available only with Mixed Authentication. The SA login has unlimited powers! There is no way to modify or delete the SA account. It is therefore imperative that you assign a password to the SA account. Otherwise, any other security measures you take will be futile.
Q. Explain the interaction between sysadmin and the Windows Administrators group.
A. All members of the Windows Administrators group are added automatically to the sysadmin role upon installation of SQL Server. Remember that sysadmin is all-powerful in working with your server. Fortunately you can remove this mapping. You must first deny the Administrators group from logging on to the SQL Server. You then grant the individual users membership to the sysadmin role as required.
Q. Explain inherited permissions.
A. Inherited permissions refer to rights that a user has to an object because he is a member of a role that has rights to that object, or because he is the owner of the object.
Q. Explain the difference between WITH GRANT and DENY permissions.
A. WITH GRANT permissions revoke permission for an object unless that user is a member of a role that has rights to that object. DENY permissions revoke permission so that permission for an object cannot be inherited.
Q. Describe CONTROL permissions.
A. With CONTROL permissions the user or role has ownership-like capabilities for the object. The user can administer the object and has permission to all objects within it.
Q. Why is memory so important to a SQL Server?
A. SQL Server uses memory to hold all data pages, index pages, and log records. It also uses memory to hold compiled queries and stored procedures.
Q. Explain some of the security options available.
A. Using the Security page of the Server Properties dialog, you can change the type of authentication that you want to allow (Windows only versus Windows and SQL Server). You can also determine what type of logging SQL Serve will perform when logins are unsuccessful.
Q. Name and describe the three recovery models.
A. The Full recovery model enables you to restore all committed transactions. With this option, SQL Server backs up both the database and the log file. With the Bulk Logged recovery model, logging is minimal. You get the best performance while using the least memory, but with the minimal recovery. The Simple recovery model loses all data since the last backup. With this model you can recover data only as of the last backup.
Q. Name the query analysis tool that shipped with SQL 2000, and compare it to what is available in SQL Server 2005 Express.
A. SQL Server 2000 provided a tool called the Query Analyzer. This tool is now built into SQL Server Management Studio Express.
Q. Explain why you will probably want to analyze the queries that you include in your stored procedures, functions, etc.
A. Using the analysis tools built into SQL Server Management Studio Express, you can analyze your T-SQL statements to ensure that they execute as efficiently as possible. You can modify your queries at will, and observe the effect that those changes have on performance.