SQL Server Best Practices – Using Schemas

What I would do is to benchmark the performance before and after the changes. There should be a performance gain after tempdb is moved to another drive. Use DMVs like sys.dm_io_virtual_file_stats to see the read and write wait times for the DB files.

Use the perfmon physical disk counters: Avg. Disk sec/Read, Avg. Disk sec/Write, Disk Reads/sec, Disk Writes/sec. The change in these metrics will let you know if the disks are physically separate or are just the same disks which are logically separated.

Want a third party disk benchmarking tool for your disks, so that the SAN admins cannot accuse SQL server of its results? Use SQLIO. Although there is SQL in the name, this tool does not require SQL server to run and is not related to SQL server.

This is a good question, and I look forward to see the other answers.

Quick answer: For developers, you can GRANT CONTROL on that schema.

Background:

• To see what can be GRANTed: GRANT Schema Permissions
• To see what each permission means: Permissions Naming Conventions

The intersection of these two give the schema permission meanings:

• CONTROL implies the rest and is the highest permissions of any securable
• SELECT, DELETE, INSERT, UPDATE is DML on objects in that schema
• EXECUTE to run scalar UDFs and Stored Procedures in that schema
• ALTER, REFERENCES is DDL (CREATE, ALTER, DROP) on objects in that schema
• VIEW DEFINITION lets folk see the code and definitions

Except for CONTROL, these are mostly orthogonal to each other: folk can EXECUTE code or SELECT, from a view but not see the definition (VIEW DEFINITION) of these.

You also have the database and server permissions which are implied higher up then CONTROL on schemas: see Permissions Hierarchy