SEPARATION OF USERS AND SCHEMAS The owner of a schema (a single user or multiple users) can create database objects within that schema and also grant schema-level privileges to others. The schema owner does have to be granted permission to create the database objects, but the grant permission exists on a database level, not on a schema level. Here s an example of a user that has an associated schema and is also the owner of that schema. USE demo1 GO CREATE LOGIN alogin1 WITH password = password1 , DEFAULT_DATABASE = demo1 GO default named schema CREATE USER auser1 FOR LOGIN alogin1 WITH DEFAULT_SCHEMA = aschema1 GO CREATE SCHEMA aschema1 AUTHORIZATION auser1 GO GRANT CREATE TABLE TO auser1 GO SETUSER auser1 GO this works and creates aschema1.table1 CREATE TABLE table1 (theid INTEGER) go In this case, if we did not set a default schema for the auser1 user, his default schema would be dbo. Because auser1 is not a member of the dbo database role, the CREATE TABLEstatement would fail. What this means to the database administrator is that because schemas (and the objects they contain) can be owned by a role, an application role, or a Windows group, when a user is dropped from the database, the database objects she has have created do not have to be reassigned or dropped and re-created. Here s an example using a SQL Server role for a payroll system. We ll assume that a role called payrollhas already been created. USE payrolldb GO CREATE LOGIN janet WITH PASSWORD = temppwd , DEFAULT_DATABASE = payrolldb GO

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

Bookmark the permalink.

Comments are closed.