what schema permissions are entailed when a SQL Server 2005 database role owns a schema?

In SQL Server 2005, a user-defined database role can "own" a schema. You can see this in the properties window of the database role. What exactly does this mean in terms of permissions/privileges against the tables in that schema? What does owning a schema mean?

Or, to ask another way: If I want a particular user-defined database role to have select/insert/update/delete privileges against every table in a schema, what is the best/smartest way to accomplish this?

I could go into the permissions of every individual table in the schema and grant the privs to开发者_如何学运维 the role, but this seems dumb. How do I do it for the whole schema?

thanks aj

"Own" means "has full access" or "can do anything."

If that is what you want, just make the group the schema owner.

Otherwise, grant schema-wide permissions to this group. You can grant select permission for the schema, and members of the group will be able to select from all tables in the schema, and so on.

See GRANT Schema Permissions for more info.

MSDN Documentation: db_owner

Making them members of the db_owner group for that a particular database will essentially do what you require - of course they have a few extra permissions - such as drop a database.

You could create a new user group/schema, of course as you pointed out you need to set appropriate permissions: SQL Server 2005 Permissions