Practice Questions: Login Security

You have a SQL Server computer set up for your development team. You want them to be able to create test databases as necessary for 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.

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 the group 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.

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

1. NT users can log in by using their NT login name and password by selecting SQL Server Authentication and supplying their credentials.
2. A Windows 98 user can log on by using the NT security mode.
3. A user with an NT logon can log in under a different name with a valid 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.