Tuesday, October 13, 2009

Using SQL Profiler Trace without the SA server role

By default users who has the SA role (SysAdmin) server role on the SQL server are able to trace activity on the SQL server with SQL Profiler. However not all users who need to trace SQL server activity will have the SA server role. To give users permissions to run SQL Profiler traces without the SA role, you need to grant ALTER TRACE permissions to the these users.

For instance grant SQL Profiler permissions to the user account Mark from domain MyCompany:

GRANT ALTER TRACE to [MyCompany\Mark]

For more information see: Permissions required to run SQL Profiler trace.

Monday, October 5, 2009

IIS Database Manager to easily manage your local and remote databases within IIS Manager

IIS Database Manager allows you to easily manage your local and remote databases from within IIS Manager. IIS Database Manager automatically discovers databases based on the Web server or application configuration and also provides the ability to connect to any database on the network. Once connected, IIS Database Manager provides a full array of management options including managing tables, views, stored procedures and data, as well as running ad hoc queries.

IIS Database Manager provides native support for SQL Server and is also fully extensible for developers to add support for other database systems. In addition, because IIS Database Manager is an extension of IIS Manager, administrators can securely delegate the management of databases to authorized local or remote users, without having to open additional management ports on the server.

First of all install the IIS Database Manager of your web server.
Start the IIS Manager.
Select your web server
In the management view click on the Database Manager icon

Create a database connection to the database you want:

Open your connection:

Press on the new query button:

Now you can start executing queries on your database

Be aware this is a simple query analyzer. You can't for instance retrieve query plans etc. But for simple checks it can be a nice tool without the need to install the full SQL Server Management Studio (SSMS)