Troubleshooting Failed Object Creation

Troubleshooting is certainly a broad topic in the real development world, and certainly on all Microsoft exams. In this chapter the focus for troubleshooting is on the interactions with objects and the server, as well as application settings that are required for an object to be created and used. On the exam troubleshooting will be approached from a wide variety of angles. In the "real world" it is good practice to always view a procedure from a problem-solving perspective. Always be ready to ask yourself, "What could go wrong?" and "What can be done to resolve the problem?"

Most problems associated with creating and/or accessing objects can be resolved through setting appropriate object access permissions. However, other elements that can hamper the creation or use of objects include (but are not limited to) the following:

• Backup and restore operations

• Other users' operations locking parts of the system

• Hardware or resource problems

• Network connectivity

• Configuration settings

• Operating system

• Metadata corruption

A good starting point from which to resolve most problems is the wealth of feedback SQL Server gives in the form of the OS Application Event Log, SQL Server Logs, and the Current Activity Window, as well as the permission properties of the users, roles, and objects.

To create a database, you need to be a member of System Administrators or Database Creators server roles or have the Create Database permission. To create objects within a database, you must be a member of db_owner or db_ddladmin database roles or have the specific permission to create the object as given by statement-level permissions. Statement-level permissions can be found on the Permissions tab of the database Properties dialog box.

As databases and their objects are created, the system uses the default filegroup for the physical storage of the element. It is a good practice to create a storage group for user objects and make that the default filegroup. This way, as the user creates objects, those objects don't compete for storage with other data.

If a user lacks the permission to create or alter an object, an alternative is available that grants the user creation permission without giving the user too much control over the environment. An Application role that has permission to work with objects in this manner can be assigned to a stored procedure that creates the objects for the user. When the user executes the procedure, objects can be created or altered in a controlled manner.