Schemas enable you to organise objects (securables) into containers and thus simplify the process of assigning permissions
We can create a flexible database role and then create a schema owned by that role, adding security principals to that role as necessary to grant them permissions over the objects within that schema.
Objects belong to schemas, and schemas belong to security principals
create schema [mySchema] authorisation [myLogin]
The schema scope contains the following securables:
- schema collection
ALTER SCHEMA Lockdown TRANSFER dbo.Engines;
We can use the ALTER SCHEMA Transact-SQL statement to move securables between schemas in the same database using the above syntax
MORE INFO USING SCHEMAS TO SECURE DATABASE OBJECTSYou can learn more about using schemas to secure database objects at http://msdn.microsoft.com/en-us/library/dd283095(SQL.100).aspx
The use of the HAS_PERMS_BY_NAME function is to evaluate the effective permission of the current user on a securable
HAS_PERMS_BY_NAME ( securable , securable_class , permission [ , sub-securable ] [ , sub-securable_class ] )
An effective permission can be one of the following:
- Granted directly to the security principal.
- Granted to a role of which the security principal is a member.
- Implied by a higher-level permission held by the security principal.
- Not denied to the principal directly or indirectly through role membership.
The HAS_PERMS_BY_NAME function runs in the context of the current principal. To use the HAS_PERMS_BY_NAME function to determine the effective permission of another security principal, the caller must have the IMPERSONATE permission on that security principal.