Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We use the database solution by Ola Hallengren. We're trying to give our developers a way to run the maintenance stored procedures after they do an ETL. Our developers have locked down permissions. We've installed Ola's stored procs: CommandExecute, CommandLog, DatabaseIntegrityCheck, and IndexOptimize.

Using Procedure with Execute as login as an example. I then setup the following:

-- Create the stored procedure in master. Otherwise, 
-- the certificate will need to be backed and restored
-- to the database where the stored procedure exists
use [master]
go


-- create certificate that the procedure will be signed with
create certificate maintenance_proc_cert
    encryption by password = 'H@rdP@w0rd'
    with subject = 'Enable maintenance through procedure',
    expiry_date = '01/01/2030';
go


-- create login that will be granted right to run maintenance
create login maintenance_login from certificate maintenance_proc_cert;
go


-- grant the login the SA role
exec master..sp_addsrvrolemember @loginame = N'maintenance_login', @rolename = N'sysadmin'
go


-- create procedures
if exists (select * from sys.objects where type = 'P' and name = 'SPECIAL_DatabaseIntegrityCheck')
drop procedure SPECIAL_DatabaseIntegrityCheck;
go
create procedure [dbo].[SPECIAL_DatabaseIntegrityCheck]
    @Databases nvarchar(max),
    @CheckCommands nvarchar(max) = 'CHECKDB',
    @PhysicalOnly nvarchar(max) = 'N',
    @NoIndex nvarchar(max) = 'N',
    @ExtendedLogicalChecks nvarchar(max) = 'N',
    @TabLock nvarchar(max) = 'N',
    @FileGroups nvarchar(max) = NULL,
    @Objects nvarchar(max) = NULL,
    @LockTimeout int = NULL,
    @LogToTable nvarchar(max) = 'N',
    @Execute nvarchar(max) = 'Y'
as
begin
    execute dbo.DatabaseIntegrityCheck
        @Databases = @Databases,
        @CheckCommands = @CheckCommands,
        @PhysicalOnly = @PhysicalOnly,
        @NoIndex = @NoIndex,
        @ExtendedLogicalChecks = @ExtendedLogicalChecks,
        @TabLock = @TabLock,
        @FileGroups = @FileGroups,
        @Objects = @Objects,
        @LockTimeout = @LockTimeout,
        @LogToTable = @LogToTable,
        @Execute = @Execute;
end
go
if exists (select * from sys.objects where type = 'P' and name = 'SPECIAL_IndexOptimize')
drop procedure SPECIAL_IndexOptimize;
go
create procedure [dbo].[SPECIAL_IndexOptimize]
    @Databases nvarchar(max),
    @FragmentationLow nvarchar(max) = NULL,
    @FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 int = 5,
    @FragmentationLevel2 int = 30,
    @PageCountLevel int = 1000,
    @SortInTempdb nvarchar(max) = 'N',
    @MaxDOP int = NULL,
    @FillFactor int = NULL,
    @PadIndex nvarchar(max) = NULL,
    @LOBCompaction nvarchar(max) = 'Y',
    @UpdateStatistics nvarchar(max) = NULL,
    @OnlyModifiedStatistics nvarchar(max) = 'N',
    @StatisticsSample int = NULL,
    @StatisticsResample nvarchar(max) = 'N',
    @PartitionLevel nvarchar(max) = 'N',
    @MSShippedObjects nvarchar(max) = 'N',
    @Indexes nvarchar(max) = NULL,
    @TimeLimit int = NULL,
    @Delay int = NULL,
    @LockTimeout int = NULL,
    @LogToTable nvarchar(max) = 'N',
    @Execute nvarchar(max) = 'Y'
as
begin
    execute dbo.IndexOptimize
        @Databases = @Databases,
        @FragmentationLow = @FragmentationLow,
        @FragmentationMedium = @FragmentationMedium,
        @FragmentationHigh = @FragmentationHigh,
        @FragmentationLevel1 = @FragmentationLevel1,
        @FragmentationLevel2 = @FragmentationLevel2,
        @PageCountLevel = @PageCountLevel,
        @SortInTempdb = @SortInTempdb,
        @MaxDOP = @MaxDOP,
        @FillFactor = @FillFactor,
        @PadIndex = @PadIndex,
        @LOBCompaction = @LOBCompaction,
        @UpdateStatistics = @UpdateStatistics,
        @OnlyModifiedStatistics = @OnlyModifiedStatistics,
        @StatisticsSample = @StatisticsSample,
        @StatisticsResample = @StatisticsResample,
        @PartitionLevel = @PartitionLevel,
        @MSShippedObjects = @MSShippedObjects,
        @Indexes = @Indexes,
        @TimeLimit = @TimeLimit,
        @Delay = @Delay,
        @LockTimeout = @LockTimeout,
        @LogToTable = @LogToTable,
        @Execute = @Execute
end
go


-- sign the procedures with the certificate
add signature to SPECIAL_DatabaseIntegrityCheck
by certificate maintenance_proc_cert
with password ='H@rdP@w0rd';
go
add signature to SPECIAL_IndexOptimize
by certificate maintenance_proc_cert
with password ='H@rdP@w0rd';
go


-- Grant execute to the procedure to NNEACC Admins
grant execute on SPECIAL_DatabaseIntegrityCheck to [developer1];
grant execute on SPECIAL_IndexOptimize to [developer1];
go

Then as developer1, I tried running:

EXECUTE dbo.SPECIAL_DatabaseIntegrityCheck
    @Databases = 'ALL_DATABASES',
    @CheckCommands = 'CHECKDB',
    @LogToTable = 'Y';

However, it fails with the error:

Msg 50000, Level 16, State 1, Procedure DatabaseIntegrityCheck, Line 156
The stored procedure CommandExecute is missing. Download http://ola.hallengren.com/scripts/CommandExecute.sql.

Msg 50000, Level 16, State 1, Procedure DatabaseIntegrityCheck, Line 170
The table CommandLog is missing. Download http://ola.hallengren.com/scripts/CommandLog.sql.

Date and time: 2013-08-22 14:44:30

I found that I had to sign each of the procs that the parent proc calls. I did this by running:

add signature to DatabaseIntegrityCheck
by certificate maintenance_proc_cert
with password ='H@rdP@w0rd';
go
add signature to IndexOptimize
by certificate maintenance_proc_cert
with password ='H@rdP@w0rd';
go
add signature to CommandExecute
by certificate maintenance_proc_cert
with password ='H@rdP@w0rd';
go
add signature to CommandLog
by certificate maintenance_proc_cert
with password ='H@rdP@w0rd';
go

Now developer1 can run SPECIAL_DatabaseIntegrityCheck successfully. I guess my question is, is there a much simpler way that I'm missing?

share|improve this question
    
If you put it behind a job, can't you just give them rights to operate the job? – Aaron Bertrand Aug 22 '13 at 20:20
    
I thought of that, but the problem is that if I make them the owner of the job, then the job runs under their context (won't work). I could make the job run under an SA user's context, but then I would need to give them the SQLAgentOperatorRole, which would be more then I'd prefer to give them. – Elijah W. Gagne Aug 22 '13 at 20:27
2  
I wasn't suggesting you make them the owner of the job. But you could create a stored procedure as a wrapper to sp_start_job, have it take a parameter to indicate the job to start (and maybe validate that to be within a set of jobs), have it execute as owner, and give them permissions to the procedure. – Aaron Bertrand Aug 22 '13 at 20:29
    
Interesting, I will look into that and follow up – Elijah W. Gagne Aug 22 '13 at 20:32
1  
Here is a thread where I offered a solution. And Erland Sommarskog offered another. social.technet.microsoft.com/Forums/sqlserver/en-US/… – RLF Aug 23 '13 at 20:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.