Transcript Upsizing Large Access Applications

SQL Server 2005
Schemas, Database
Roles & Security
How To Secure Data
Andrew Couch
ASC Associates
Copyright © Andrew Couch 2007
All rights reserved.
Contents
 Using

Schemas and Security
These features apply only to SQL Server
2005 and SQL Express
 Creating
Database Roles
 Assigning Schema Permissions
 Missing Feature
 Adding Users
 Reporting & Read-Only Users
Schema
 A Schema
is a way to group together
Tables, Views, Stored procedures and
other design objects
 Using a schema means that the objects
need to be prefixed by the schema name


For example, creating a schema called
[Sales] containing the table [Customer], we
refer to the table as [Sales].[Customer]
Synonyms get around the need to use the
prefix (see later)
Schemas & Synonyms
 Together
this creates a harmony, that
enables you to maximise leverage on your
development environment, both
maintaining SQL compatibility, and
enabling an application to be split into
manageable chunks
Database Roles- Easy Security
 Users
can be assigned a database role,
such as Read-Only, Administrator, Sales,
Marketing
 Each Role can be granted permissions on
a schema
 Security then becomes very simple to
manage
Schemas, Database Roles &
Security
Creating Database Roles

The system has three pre-set roles
for you, which can be edited as
required
Assign Permissions For Each
Schema
 Once
defined for each role, press the
button to create the security MAP.
Use Management Studio To Tune
The Security

Displaying the properties of a schema will show
how the security has been assigned to the
different database roles (see next slide)
Missing Feature
 The
security feature does not have an
option for granting execute on a schema to
run stored procedures, you need to add
this yourself
Adding Users
 Whilst
best undertaken using the
Management Studio, the tool offers some
features to support this.
Reporting & Read-Only Users

With Read-Only users, you may




Still wish to log entry exit of an application
Update data for preferences
Update data for reporting
One option is to create a separate schema to
hold all the related objects, then permissions
can be granted to insert/update/delete/execute
on that schema
 The other alternative is to use Stored
Procedures to do the updates (as these execute
with owner permissions), even when a users has
no update permissions on the underlying table