Sql-server – Grant a database role in current database with permission to execute a SP in msdb

msdbpermissionsrolesql serversql-server-2012

I have created a role in my current SQL Server 2012 database with db_datareader, db_datawriter, and execute permissions. A procedure in the current db calls sp_start_job in msdb.

How can I grant access to my database role to execute the procedure in msdb?

I tried executing the procedure in my current database which calls sp_start_job as owner and the user who is a member the user defined role is still not able to execute the procedure.

I have made the server role public for the login, and mapped it to msdb database, but I'm not able to grant permission. The command I'm trying to execute is:

GRANT EXECUTE ON OBJECT::[msdb].[dbo].[sp_start_job] TO [db_executor]

When I execute from msdb, the error I get is:

Cannot find the user 'db_executor', because it does not exist or you do not have permission'.

When I execute from the database in which the user defined role, db_executor is created, it throws this error:

You can only grant or revoke permissions on objects in the current database.

I don't want to add users to SQLAgentOperatorRole. The idea is to group all users in one role within the app's database and the members of this role should be able to run a procedure in the database which calls sp_start_job.

Best Answer

Here are the steps you should follow:

Get familiar with the material in SQL Server Agent Fixed Database Roles.
Decide which one is good for you

Use a script similar to the following one (but first choose specific msdb role) to achieve what you ask for:

USE [master]
GO 
CREATE LOGIN [DOMAIN\user] FROM WINDOWS 
GO 
USE [msdb]
GO
CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user] 
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'DOMAIN\user'
GO

Related Solutions

Sql-server – Execute permission denied on object sp_start_job

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant to you in this scenario):

(And there are tons of other posts out there cautioning against the blind use of this property - just because it works and it is easy doesn't mean it's the right thing to do - in fact that should make you question it even more.) So I would suggest a different approach (and there are still others, such as signing with a certificate, but this has always worked for me):

Create the procedure in msdb.

Create a user for this user's login in msdb:

USE msdb;
GO
CREATE USER floobarama FROM LOGIN floobarama;

Grant the user execute privileges on the stored procedure:
```
GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;
```

Test it - either by calling the procedure from another database:

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.RunJob @job_name = N'whatever';
GO
REVERT;

Or an easier test, in case you don't want to run any job now and don't want to wait until this user executes it to find out whether they have sufficient access to msdb:

USE msdb;
GO
CREATE PROCEDURE dbo.whatever
WITH EXECUTE AS N'sysadminaccount'
AS
BEGIN
  SET NOCOUNT ON;
  SELECT [I am really...] = SUSER_SNAME();
END
GO
GRANT EXECUTE ON dbo.whatever TO floobarama;

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.whatever;
GO
REVERT;

Result should be:

I am really...
---------------
sysadminaccount

Validate that this doesn't expose anything else in msdb to this user:
```
USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
SELECT job_id FROM msdb.dbo.sysjobs;
GO
REVERT;
```
Result should be...

Msg 229, Level 14, State 5, Line 21
The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

...since creating a user in a database doesn't give them automatic rights to anything in that database; you need to explicitly do so either for that user, or for a role or group they are in (including public).

Sql-server – How to allow users to add members to a specific database role

Look at the syntax diagram a little more carefully. Typically, above object level, you must specify the type you are applying permissions to with <entity_type>::<entity_name>.

GRANT ALTER ON ROLE::[NewDBRole] TO [domain\username] WITH GRANT OPTION AS dbo;

Best Answer

Related Solutions

Sql-server – Execute permission denied on object sp_start_job

Sql-server – How to allow users to add members to a specific database role

Related Question