SQL Profiler Access in SQL SERVER 2005/2008

In SQL Server 2005/2008 the good feature is a SQL User can run the PROFILER or Trace without having SYSADMIN Privilege.

As per Volume Data policy Developer doen’t have SYSADMIN priviledge on UAT&LIVE SQL Instances.

At the same time few developers might be interested to perform same using SQL Server Management Studio (SSMS).

Below are the steps to accomplish this task without giving SYSADMIN privilege.

SSMS àunder Security tab à Logins à right click name to modify select properties à securables tab à Click Search à Select the instance you want to add the permissions to. à Grant ALTER TRACE permission.

The permissions appear in the Effective Permissions list

-------------------------------------------------------------------------------------------

But this privilege revoked automatically in some cases it's a bug.

That’s it Job done!

SQL Server Backup Solution:

SQL Server Backup Solution:

This is the traditional backup solution by running backup script on regular intervals.

SQLServerMaintenanceSolution.sql . This script is used for Sql Server Backup, Integrity Check and Index Optimization.

The Solution is supported on SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.

To implement this solution run this script once in the SQL Instance with the default Master database. Before you run the script, you have to modify following variables for the SQL backup location.

SET @BackupDirectory = N'C:\Backup' -- <== Change this to your backup directory.

SET @CreateJobs = 'Y' -- <== should jobs be created, 'Y' or 'N'?

This Script creates following Objects in Master Database:

Temporary Table:

1. #Config

Procedures:

1. dbo.CommandExecute

2. dbo.DatabaseBackup

3. dbo.DatabaseIntegrityCheck

4. dbo.IndexOptimize

Functions:

1. dbo.DatabaseSelect

Schedular Jobs:

1. DatabaseBackup - SYSTEM_DATABASES– FULL

2. DatabaseBackup - USER_DATABASES – FULL

3. DatabaseBackup - USER_DATABASES – DIFF

4. DatabaseBackup - USER_DATABASES-LOG

5. DatabaseIntegrityCheck - SYSTEM_DATABASES

6. DatabaseIntegrityCheck - USER_DATABASES

7. IndexOptimize - USER_DATABASES

8. Output File Cleanup

After running above scirpt schedule the Database backup/other job as per company’s backup policy.

That’s it, Job Done!