Thursday, June 27, 2013

Schedule a SQL profiler to run at a specific time

I needed to reserach some external backups on the database that ran in the middle of the night. To find out the source and the status. I decided to set up a trace in sql profiler that consisted teh processID, Loginame, NTusername etc .

Steps on SQL Server 2008 R2
---
  • Connect to SSMS
  • Go to Tools
  • Go to SQL Profiler
  • Chose the appropriate trace template according to requirements ( or create your own)
  • Check enable trace stop time and specify the date and time that you want it to stop
  • Click on the Run

If you run the follwing query you will see our trace

select * from ::fn_trace_getinfo(0)
Immediately stop the trace

Now we want to export the create trace script and add this script as  one of the step in the schedule job

Click on File----Select Export--Select Script Trace Definition

Select the entire script and add it as a step in the schedule job.

In the script - Search for key word in the script 'InsertFileNameHere' and replace with where to store the trace file.

exec @rc = sp_trace_create @TraceID output, 0, N'E:\profilertrace\trace1.trc', @maxfilesize, @Datetime
 
Save the Job.

That's all !!! Schedule the job to run at the specific time that you would like it to run.

Tuesday, June 18, 2013

Giving execute permission to all stored procedures through a databaserole


I had a situation where I had to give a user( AD group) execution permission to all the sps.


- Created a role called db_executor data user role


USE pubs
GO

exec sp_addrole  'db_executor'
GO

--added memebers to the role
USE pubs
GO

exec sp_addrolemember 'db_executor', 'GRP_ProdSupport'
GO

--gave permission to execute the stored procedures to the role.

USE pubs
GO

DECLARE @SQL nvarchar(4000),
  @Owner sysname,
  @StoredProcedure sysname,
  @Return int

-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE xtype = 'P'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  BEGIN
    -- Create the SQL Statement. Since we're giving
    -- access to all stored procedures, we have to
    -- use a two-part naming convention to get the owner.
    SET @SQL = 'GRANT EXECUTE ON [' + @Owner
          + '].[' + @StoredProcedure
          + '] TO db_executor'

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @StoredProcedure
  END

-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END
ELSE
  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
    
    -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @StoredProcedure + ']'
    RAISERROR(@SQL, 16, 1)
  END
GO

Permission to run a specific SQL job to a user

I added a user to the msdb database and gave him the SQLAgentUserRole but that gave him access to all the jobs, which is not what I wanted. This is the work around I followed....


USE msdb
GO
CREATE PROC dbo.Start_Job_MyJobThatAGroupControls
-- Executes as the database owner, for msdb that is 'sa'
WITH EXECUTE AS OWNER
AS
-- Hardcoding the job name limits this procedure's control
EXEC dbo.sp_start_job @job_name = 'MyProductionJobName'
GO
-- Grant Execute rights to your selected users
GRANT EXECUTE ON Start_Job_MyJobThatAGroupControls TO <usernames>
GO


This way the user will not have direct access to the job MyProductionJobName but through this wrapper sp it can run the job.