SQL – The EXECUTE permission was denied on Stored Procedure

The EXECUTE permission was denied on the object ‘STORED_PROC’, database ‘MYDB’, schema ‘dbo’

This massage is received when the SQL Server account under which you make the request doesn’t have permission to execute the stored procedure. you can overcome this problem by modifying the permissions of the SP.
If you want to grant public access, so all users can execute the procedure:

USE [DATABASE];
GRANT EXEC ON dbo.[MYSTOREDPROCEDURE] TO PUBLIC

 

If you want to tighten security to allow only specific users to execute the SP, which is advisable, then its:

USE [DATABASE];
GRANT EXEC ON dbo.[MYSTOREDPROCEDURE] TO MYUSER

OR

USE [DATABASE];
GRANT EXEC ON dbo.[MYSTOREDPROCEDURE] TO MYGROUP

Advertisements

SQL Server SSMS Saving changes is not permitted

You may receive this dialogue if you’re using a fresh install of SSMS, or after an upgrade. It simply means your environment hasn’t been configured to allow certain changes which require the dropping, and re-creation, of a table.

Such actions include:

  • Adding a new column anywhere other than the end of a table
  • Deleting a column
  • Updating column configuration, such as Allow Nulls or the Data Type
  • Changing the column order

You can modify your environmental settings to allow for these actions form the Tools menu. Click Options, expand Designers, and then click Table and Database Designers. Un-tick Prevent saving changes that require the table to be re-created and click OK.

You may now commit your table changes.

 

SQL – View SQL Server Database Mail Log (All / Successful / Failed)

SQL Server keeps a log of mail sent via Database Mail and the sp_send_dbmail Stored Procedure.

I find these code snippets useful to view mail history including sent and failed items. You can view address, subject & body, file attachment(s), status, sent date, etc.

All Messages – regardless of status and deliverability
SELECT top 50 *
FROM [msdb].[dbo].[sysmail_allitems]
ORDER BY [send_request_date] DESC
Sent Items
SELECT TOP 50 *
FROM [msdb].[dbo].[sysmail_sentitems]
ORDER BY [send_request_date] DESC
Failed Items
SELECT TOP 50 *
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [send_request_date] DESC

SQL Server – One-off Backup and restore of ALL SQL Server Agent jobs using SQL Server Management Studio

One-off Backup and restore of all SQL Server Agent jobs using SQL Server Management Studio

1. Connect to the SQL server using SQL Server Management Studio.

2. Navigate to the Object Explorer Details view by selecting View à Object Explorer Details from the menu bar or press F7.

3. Double-click SQL Server Agent from the Object Explorer Details window.

4. Double-click the Jobs folder to expand the list of agent jobs currently configured on the SQL Server.

5. At this point you have the option to select only the jobs you want to backup, or select all jobs on the server (Ctrl+A).

6. Once your selection has been made, right-click over the selected area, hover Script Job As à CREATE To à File …

7.Navigate to a file location and save your SQL Server Agent job(s) creation code to a .SQL file.

Open the SQL file to execute on another SQL Server to restore a previous version of agent job(s), or to create the job(s) at a new location.

SQL – How to get the status of an SQL job (sp_help_job)

 

Here is a simple script using sp_help_job to display information about jobs that are used by SQL Server Agent:

USE msdb
EXEC dbo.sp_help_job @JOB_NAME = ‘JOB_NAME’, @job_aspect=‘JOB’

Look for ‘current_execution_status’ to get the status of the job.

1 = Executing.
2 = Waiting for thread.
3 = Between retries.
4 = Idle.
5 = Suspended.
7 = Performing completion actions.

 

Here is a more detailed script to store the results of sp_help_job into a table and query the results:

DECLARE @JOBNAME VARCHAR(100)
SET @JOBNAME = ‘JOB_NAME’

CREATE TABLE #JOBSTATUS(
       job_id uniqueidentifier 
      ,originating_server nvarchar(30)
      ,name sysname
      ,enabled tinyint
      ,description nvarchar(512)
     
,start_step_id int
     
,category sysname
     
,owner sysname
      ,notify_level_eventlog int
     
,notify_level_email int
     
,notify_level_netsend int
     
,notify_level_page int
     
,notify_email_operator sysname
     
,notify_netsend_operator sysname
     
,notify_page_operator sysname
     
,delete_level int
     
,date_created datetime
     
,date_modified datetime
     
,version_number int
     
,last_run_date int
     
,last_run_time int
     
,last_run_outcome int
     
,next_run_date int
     
,next_run_time int
     
,next_run_schedule_id int
     
,current_execution_status int
     
,current_execution_step sysname 
     
,current_retry_attempt int
     
,has_step int
     
,has_schedule int
     
,has_target int
     
,type int     
); 

INSERT INTO #JOBSTATUS
EXEC dbo.sp_help_job
 @JOB_NAME = @JOBNAME
,@job_aspect=‘JOB’

SELECT CASE current_execution_status
WHEN ‘0’ THEN ‘Returns only those jobs that are not idle or suspended. ‘        
WHEN ‘1’ THEN ‘Executing.’
        
WHEN ‘2’ THEN ‘Waiting for thread.’
       
WHEN ‘3’ THEN ‘Between retries.’
        
WHEN ‘4’ THEN ‘Idle.’

WHEN ‘5’ THEN ‘Suspended.’

WHEN ‘6’ THEN

WHEN ‘7’ THEN ‘Performing completion actions.’

ELSE ‘UNKNOWN’
END as ‘current_execution_status’
FROM #JOBSTATUS

DROP TABLE #JOBSTATUS

 

OUTPUT:


current_execution_status
——————————————————–
Idle.

SQL – How to get the day of the week

Using the DATEPART function to calculate the integer value of the day of the week.

select datepart(dw,getdate()) as DayOfWeekINT


Output:
DayOfWeekINT
————
2  

1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday

Using the DATENAME function to calculate the  day of the week.

select datename(dw,getdate()) as DayOfWeekCHAR


Output:
DayOfWeekCHAR
——————————
Monday
 
 

 

You can get the day of the week for a given date (MM/DD/YYYY) using:

select datepart(dw,’03/07/2011′) as DayOfWeekINT
select datename(dw,’03/07/2011′) as DayOfWeekCHAR

 

SQL – How To Shrink Un-shrinkable Database Log / Database Log Won’t Shrink SQL Server 2008

To shrink the transactional logs in SQL Server 2005 follow this link:
https://robbamforth.wordpress.com/2009/11/16/sql-how-to-shrink-un-shrinkable-database-log-database-log-wont-shrink/

In SQL Server 2008 it’s a matter of changing the recovery model to SIMPLE and re-running the DBCC SHRINKFILE  command – assuming the current recovery model is FULL.

Firstly, here is a quick script to backup the database and also the transactional log (if required to back up separately) to disk:

— QUICK SCRIPT TO BACKUP THE DATABASE TO DISK
BACKUP DATABASE [DATABASE]
TO DISK = ‘c:\backup\DATABASE.BAK’
WITH NOFORMAT, NOINIT, NAME = ‘DATABASE-Full Database Backup’,
SKIP,NOREWIND,NOUNLOAD,STATS = 10— QUICK SCRIPT TO BACKUP THE LOG FILE TO DISK
BACKUP LOG [DATABASE]
TO DISK = ‘c:\backup\DATABASE_LOG.BAK’
WITH NOFORMAT, NOINIT,  NAME = ‘DATABASE-Transaction Log  Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10

 

 

Step 1: View the current recovery model configuration.

— SHOW AVAILABLE DATABASES AND THEIR CURRENT RECOVERY MODEL CONFIGURATION
select name,recovery_model_desc from sys.databases

 

Step 2: Change the recovery model to SIMPLE.

–CHANGE THE RECOVERY MODEL CONFIGURATION TO SIMPLE
Alter database [DATABASE] set Recovery simple

 

Step 3: Use DBCC SHRINKFILE to truncate the transactional logs of the database.

— USE BDCC CHRINK FILE TO TRUNCATE THE TRANSACTIONAL LOGS
USE [DATABASE]
GO
DBCC SHRINKFILE ([DATABASE_LOG], 0, TRUNCATEONLY)
GO

 

Step 4: Return the recovery model back to FULL if required.

— CHANGE TEH RECPVERY MODEL BACK TO FULL IF REQUIRED
Alter database [DATABASE] set Recovery full

 

  

SQL – How To Shrink Un-shrinkable Database Log / Database Log Won’t Shrink SQL Server 2005

To shrink the transactional logs in SQL Server 2008 follow this link: https://robbamforth.wordpress.com/2010/06/24/sql-%e2%80%93-how-to-shrink-un-shrinkable-database-log-database-log-won%e2%80%99t-shrink-sql-server-2008/

The following should only be considered as a last resort to force the log file to shrink. I suggest you take a full database back-up first.

The DBCC LOGINFO can be run against the database. This will give you information about your virtual logs inside your transaction log.
In particular look at the Status column. Status = 2 indicates the portions of the log that are in use, status = 0 are not in use.

DBCC LOGINFO (DATABASE_NAME)

Step 1:
Attemp to shrink the problematic log file and back up the database with TRUNCATE_ONLY.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N’DATABASE_NAME_LOG’ , 0, TRUNCATEONLY)
GO

BACKUP LOG [DATABASE_NAME]
WITH TRUNCATE_ONLY

Step 2:
Create a temporary table and insert a record.

CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO

Step 3:
Run the following loop to update the table.

SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 20000)
BEGIN
   UPDATE MyTable SET MyField = MyField WHERE PK = 1
   SELECT @Index = @Index + 1
END
SET NOCOUNT OFF

The log rows with status 2 should now be available with statis = 0.

Step 4:
Re-run the shrink command.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N’DATABASE_NAME_LOG’ , 0, TRUNCATEONLY)
GO

BACKUP LOG [DATABASE_NAME]
WITH TRUNCATE_ONLY

— Drop the temporary table as no longer required
drop table MyTable

The log file should now have shrunk successfully.

Source: http://www.broad-lea.com/sql_server/sql_reduce_log_size.html

SQL – Get Exclusive Lock – Fix for The database could not be exclusively locked to perform the operation error

In order to run certain update commands against an SQL database you need to gain exclusive lock. This is done by setting the database to single user mode.
The command is:

ALTER DATABASE [DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

To set the database back to multi user mode you must remember to run the following code once you’ve completed the tasks requiring single user mode:

ALTER DATABASE [DATABASE] SET MULTI_USER

The following code shows how to alter the collation of a database which requires single user mode.

ALTER DATABASE [DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DATABASE] COLLATE Latin1_General_CI_AS
ALTER DATABASE [DATABASE] SET MULTI_USER