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):

1. Database Creators so they can add and delete databases
2. Process Administrators so they can control processes running on the SQL Server, in case a query or procedure enters a loop condition
3. Security administrators so they can check how permissions on database objects affect performance and data access
4. Server Administrators so they can experiment with retaining frequently used reference tables in memory

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?

1. Grant Access overrides the previously denied access, letting the user connect to the SQL Server with no restrictions.
2. The user can connect to Northwind but not to Pubs.
3. The user can connect to any database except Pubs and has a default database of Northwind.
4. The user can't connect because the deny always overrides the grant.

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?

1. All NT users can log on using their NT login name and password by selecting SQL Server Authentication and supplying their credentials.
2. A Windows 98 user can log on using the NT security mode.
3. A user with an NT logon can log on under a different name with a SQL Server user name and password.
4. All NT users have automatic access to the SQL Server, but the DBA has to set up non-NT users with a name and password.

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.