Security

Principals are entities with permissions to securables. In general you make a login first, and then go to the database and make a user tied to that login. There are three levels of principals:

• Windows level
  • Windows Group
  • Windows Domain login
  • Windows Local login
• SQL Server level
  • Server Role
  • SQL Server login. There is a special SQL Server login called sa. sa stands for System Administrator, and it is the lowest and most powerful user of a SQL Server server. The sa password must be very secure and well protected. Avoid using sa whenever possible.
• Database level. Users, roles, and groups on the database level eventually map to Windows and server level principals. Roles can be nested, i.e. a role can be a member of another role.
  • Database Role
  • Database User. Database Users can own schemas and be a member of 0+ roles.
  • Application Role
  • Group

Securables are resources that are secured, and only principals have certain permissions to each. There are three scopes of principals:

• Server. A company may have one or more SQL Server servers.
  • SQL Server login; Endpoint; Database;
• Database. A server has multiple databases.
  • Application Role; Assembly; Asymmetric Key; Certificate; Contract; Full-Text Catalog; Message Type; Remote Service Binding; Role; Route; Service; Symmetric Key; User; Schema;
• Schema. A database has one or more schemas. A schema is a collection of resources of resources grouped into a namespace.
  • Table; View; Function; Procedure; Queue; Synonym; Type; XML Schema Collection;

User-Schema Separation. In both SQL Server 2000 and 2005, a resource is qualified with this syntax: server.database.schema.object. EG: myServer.myDatabase.mySchema.myTable.

• In SQL Server 2000, when a securable like a table is created, the default is to have the schema owned by a single user. If the user is a member of the sysadmin fixed server role, or is a login aliased to the dbo, then objects created by that user have the schema of dbo. EG: myServer.myDB.dbo.myTable. When other users create objects, then the schema takes the same name as the user. EG: myServer.myDB.jane.janeTable.
• In SQL Server 2005, a schema may be owned by a database user but is most likely owned by a database role or an application role (but  not a server role). EG: srvr2005a.myDB.sales.product.
  • Each user has a default schema that is checked first during name resolution; if not found in the default schema, then the dbo schema is checked.
  • You can specify the default schema for a user even if the schema does not exist yet, but you cannot make a resource within a schema if the schema does not exist yet.
  • Objects created by a user are owned or are part of that user's default schema.

Permissions. Users are granted permissions either directly or through a role. SQL Server permissions are similar to other permissions (such as the more common "rwx" or read, write, execute), but is more granular and specific. One distinction is that principals have permissions to securables but a schema is not a seucrable or a principal, but rather something that is owned by one or more principals.

• Control. This is roughly the same as ownership or full control.
• Alter. This is like Control except that the user cannot change or transfer ownership.
• You can grant a user permissions to a schema with a command like: GRANT select ON SCHEMA :: MySchema TO UserX.

Page Modified: (Hand noted: 2007-10-13 19:42:41Z) (Auto noted: 2007-11-17 06:43:48Z)