Expand the Security node in Object Explorer for the AdventureWorks2012 database.Then follow these steps in Management Studio to use the graphical tools to assign the needed permissions.
Listing 5.1: Creating the DataEntry role in AdventureWorks2012.
#Adventureworks2012 schema code
The code in Level 4 created that role if you didn’t create it there, execute the code in Listing 5.1 in Management Studio. Use the following steps to grant SELECT, UPDATE, DELETE, and INSERT permissions on the Purchasing schema to the DataEntry user-defined database role in the AdventureWorks2012 database. Now you’ll see how you can use schemas to assign permissions to objects. Multiple users and roles can have the same default schema, and if a principal has no default schema set, SQL Server attempts to find or create the object in the dbo schema. Continuing the SELECT example, if a year from now you add Dog Table4 to DogSchema, all principals with SELECT permission on the schema automatically have that permission on the new table. Best of all, the permissions you assign on a schema apply automatically to any future objects you add to the schema. Setting permissions individually on objects within a schema is always an option, but if you’ve designed the schemas in a database well, in some sort of functional categories that make sense for the database, you can set permissions on the schema and have them apply to dozens if not hundreds of objects. Like all user-defined database objects, a schema has an owner that has complete control over the object. For example, if you assign SELECT permission on DogSchema to a principal, all the tables in that schema will have that permission. You can assign permissions on a schema that apply to all objects in the schema. But more importantly for security, it simplifies permissions management.įigure 5.1: A sample schema that contains database objects and is owned by a user. This organizational structure simplifies user management, particularly when you have to change ownership of objects. A schema is a collection of objects, such as tables and code modules, as shown in Figure 5.1.
Usually you’ll just need to refer to objects in the current database context by using the schema and object names. A fully qualified database object name consists of four parts: The relationship of schemas with roles and permissions is an important security concept in SQL Server. You’ll also learn about the benefits of user-schema separation and how it can increase object security, and how using default schemas for users and groups can simplify object access management and security. In this stairway level you’ll learn how you can give principals access to groups of objects by assigning permissions on schemas instead of individual tables, code modules, and other objects. But schemas have important security features as well. A schema is essentially just another database object, a container for other objects that makes it easier to manage groups of objects in complex databases.