Logins and Schema

Objectives

• Understand logins and users
• Understand schema
• Create logins and users
• Create schemas and associate users

Schema

In this usage "Schema" refers to the security context in which an object is accessed. By default the schema is assigned to the user who created the object. The usual default schema, for instance, is dbo [dbo.Database owner]dbo.table1. But if the logged in user who create the table was joe the schema would be joe.table1.

Up until SQL Server 2005 that was the limit to the control over schema, but in SQL 2005 Microsoft separted schema from the objects in a way that allows them to be used as a powerful tool for organizing views and permissions.

Microsoft added the create schema statement

--create a human resourses schema
CREATE SCHEMA HR AUTHORIZATION [DBO];

Having created the schema you can create objects that belong to that schema

CREATE VIEW HR.Employees
AS
SELECT SocialSecurityNo, lastname, firstname, Department, hiredate,
    payrate
FROM Employees
    

To see the real power of this though, you must understand it in the context of Logins and Users

Logins, Users

Server Logins

Before you can access an instance of SQL Server you must have a server login. The server login in itself only lets you into SQL server. It does not give you access to any of the server's resources.

There are two kinds of server logins:

Windows logins

Windows logins use Windows the authenticate the user. Windows checks the user's id and password against a valid windows account and then that account is mapped to SQL Server. The Windows account must exist before you can make the login. It can be a local machine user account or it can be an Active Directory account. Microsoft suggests using Windows logins where possible.

SQL Server Logins

SQL server accounts are authenticated by SQL Server itself.

Here are some examples

--Windows login syntax
USE MASTER
CREATE LOGIN [domain or windowsuser]\{LoginName] FROM WINDOWS
	
CREATE LOGIN [Student01\Joe] FROM WINDOWS
	
--Sql Server Logins
USE MASTER	
CREATE LOGIN Joe 
WITH Password='p@$$w0rd', 
default_database=MagazineSubscription

	

Even the defualt database does not give you access to that database. You must add a "user" for that database who is mapped to an exisiting "Server Login."

Database Users

For a login to have access to a particular database there must be a database user. Now the real power of schema can be seen. When we create the login we can assign to it a default schema and when that user logs into the database, he or she will only see what is in their default schema. Here is the syntax for creating a database user. Assume "Project" is a database that tracks on going projects for a company.

USE Project
--create shema and give them database owner permissions
CREATE SCHEMA Proj_Managers AUTHORIZATION [DBO]

--Now add users
CREATE USER mangr FOR LOGIN manager WITH DEFAULT_SCHEMA=Proj_Managers

--Give Permissions
GRANT SELECT ON SCHEMA::[Proj_Managers] to [mangr]

-- now we will create an object that belongs to that schema
CREATE VIEW Proj_Managers.CurrentProjects
AS
SELECT ProjectDate, ProjectName, ProjectDescription
FROM Project
WHERE ProjectEndDate IS NULL

When the project manager log in to the database they will--at this point--see only the view of current projects.