MS-Access / Getting Started

---

Schemas and Synonyms

After you create a SQL Server database, click Security | Schemas. There you will find a list of existing schemas. The dbo schema is typically the one that contains all your design objects, and it is where new objects that you create are stored, unless your login has been altered to use a different default schema for the database.

In earlier versions of SQL Server, objects were owned by users, so you could have a table owned by User1.Customers and User2.Customers. However, this caused problems if you wanted to remove User1 from the system but keep an object such as User1.Customers. As a result, in SQL Server 2005 a new interpretation of schemas was introduced that was independent of users.

If you have a large Access database (for example, 200 tables), you can use schemas to split up the tables, Views, and so on, into logical groupings to better manage the database design. You can also use schemas to flexibly and easily configure security; for example, restricting a group of users to having only read permissions on a particular set of tables.

The following example (UsingSchemas.sql) illustrates how to create a Schema, create a table in the Schema, and then create a Synonym to refer to the table without specifying the full Schema prefix:

-- Example Schema
CREATE SCHEMA [Companies] AUTHORIZATION [dbo]
GO
-- Example Creating a table on a Schema
CREATE Table [Companies].[Company](
CompanyID INT IDENTITY(1,1),
CompanyName NVARCHAR(100)
)
GO
SELECT * FROM [Companies].[Company]
GO
CREATE SYNONYM [dbo].[Company]
FOR [Companies].[Company]
GO
SELECT * FROM [Company]
GO

The Synonym is very important, because if you already had a table on the dbo schema and moved it to the Companies Schema, any SQL that referred to the table would no longer work, but once you add the Synonym, that problem is resolved. If you don't specify an explicit schema when creating objects, they will be placed on your default schema, which, as previously mentioned, is normally dbo.

The following example shows an object being moved into a Schema and creating an appropriate Synonym for the object:

-- Example moving an object onto a Schema
ALTER SCHEMA Companies TRANSFER dbo.Customers
GO
CREATE SYNONYM [dbo].[Customers]
FOR [Companies].[Customers]
GO

If you want to try the previous code and then move the Customers table back to the dbo Schema, you need to drop the synonym before you do this by using DROP SYNONYM Customers. You could then use the following line to move the table back to the dbo schema:

ALTER SCHEMA dbo TRANSFER Companies.Customers

After you have converted your database to SQL Server, you can consider making a list of all your tables, and then create a script to move all your tables into appropriate schemas and simultaneously create appropriate synonyms. You can use sp_tables to get a list of all your existing tables and the TABLE_OWNER property, which is the schema.

Using schemas and database roles to manage security
Often in an application you will want to provide users with different permissions on groups of objects in the system. SQL Server has a sophisticated security system with which you can establish fine control of security. One of the simplest approaches to security is to create database roles, and when mapping users, windows groups, or SQL Server logins into your system, assign them an appropriate database role, as demonstrated in the following:

CREATE ROLE [ReadOnlyUsers]
GO
CREATE ROLE [Administrators]
GO
GRANT Select, Execute on Schema::Companies TO [ReadOnlyUsers]
GO
GRANT Select, Insert,
Update, Delete, Execute on Schema::Companies TO [Administrators]
GO

The Execute and Select permissions will allow the ReadOnlyUsers to both view data and execute procedures on the Schema.