Answers to January Practice Questions Login Security

Q1. You have a SQL Server computer set up for your development team. You want them to be able to create test databases as needed for their development and testing. You should assign them to the following server roles, so that they can perform the listed tasks (select all correct answers):

Answers: A, B, D. A is necessary so that the developers can create test databases without needing the DBA's assistance. B is helpful, in case of a runaway query or process. C isn't correct—security administrators control logins to the server, not permissions on database objects. D lets the developers use the DBCC PINTABLE option to retain reference tables in memory (carefully, of course).

Q2. You add a login for a Windows NT user, set the default database to Pubs, and check the Deny Access box. Then you add another login for a group this user belongs to. You check Grant Access for this login and set the default database to Northwind. What is the result for the user?

Answer: D. A Deny is a veto, which overrides all other grants of access to groups or individuals. When a user is denied access, the access is to the server; the default database is irrelevant in that context.

Q3. Your server is set up for mixed security (SQL Server and NT). Which of the following statements are true?

Answers B and C are true. A is incorrect—users must supply the SQL Server login name and password, not their NT name and password (unless the names and passwords are the same, in which case, just let them use NT authentication). B is true—Windows 98 users can log on using NT authentication, as long as they're logging on to an NT domain. C is true—an NT user can use SQL Server authentication and give any valid SQL Server name and password. D is false—the DBA has to grant NT users access.