Take the 2-minute tour ×
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.

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.