Configuring Microsoft SQL Server, required permissions:
In all cases SYSADMIN is sufficient.
On 2008 and 2005 it is sufficient to have 'VIEW SERVER STATE' and SELECT on any database or object which automatically give select on master.. objects)
On 2000 you have to be SYSADMIN, no other options.
fn_get_sql - issues
compatibility level has to be set to the same level as the actual version of the
For example, SQL Server 2005 is level 90, but if the compatibility
is change to 80 the equivalent of windows 2000, then DB Optimizer won't
>> select name,compatibility_level from sys.databases
if the compatibility level is different than the default, one issue that happens is an error running the function fn_get_sql which is required by DB Optimizer
- 80 = SQL 2000
- 90 = SQL 2005
- 100 = SQL 2008
To set compatibility level you can use, for example
sp_dbcmptlevel MyOldDB, 90
SQL Server 2000 only
This flag will allow profiler to capture more SQL. If a session/sql is not showing up or the sql text is missing then turn this flag on:
then restart the server.
Using Trace flag 2861:
Trace flag 2861 instructs SQL Server to keep zero cost plans in cache, which SQL Server would typically not cache (such as simple ad-hoc queries, set statements, commit transaction and others). Other words, the number of objects in the procedure cache increases when trace flag 2861 is turned on. Because the additional objects are so small, you will see a small increase in memory, which is taken up by the procedure cache.
SQL Server Hints
SQL Server Express - special install/setup notes
If you are using 64bit Windows check out this Microsoft link
for login problems and solutions with JDBC and SQL Server.
SQL Server Database Info