GRANT EXECUTE

A lot of SQL Server projects are using stored procedures to control their query behavior (which I like because developers shouldn't be in control of this :p). In previous versions of SQL Server there was something that was kind of disturbing in terms of security. In order to grant execute permissions to a user you had to give permission to every single stored procedure the user needed. When you have 1000 stored procedures you wouldn't want to do this manually so there was the possibility to generate a script by using sysobjects or INFORMATION_SCHEMA.Routines but you need to keep the permissions up-to-date when new procedures were created.

SQL Server 2005 has a great solution to this: GRANT EXECUTE
You can now create a database user role that has execute permissions on all the objects in that database.

CREATE ROLE db_ICanExecute
GO

GRANT EXECUTE TO db_ICanExecute
GO

Fun with jobs and the public role and proxies

My colleague came across some interesting 'features' with SSIS in jobs.

• When you execute a job and change the data sources the information is NOT stored and the next execution resets the connection information. Using a config file is definitely the way to go :-)
• Proxy accounts with the public role assigned to it have a problem removing this role.

USE [master]
GO

CREATE CREDENTIAL [myCredentials] WITH IDENTITY = N'svcaccount',
SECRET = N'xxxxxxxxx'
GO

USE [msdb]
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'myProxy',
@credential_name=N'myCredentials', @enabled=1
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'myProxy',
@subsystem_id=11
GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'myProxy',
@msdb_role=N'public'
GO

USE [msdb]
GO

EXEC msdb.dbo.sp_revoke_login_from_proxy @proxy_name=N'myProxy',
@name=N'public'
GO

This results in the following error message: "Msg 14523, Level 16,
State 1, Procedure sp_revoke_login_from_proxy, Line 63 "public" has not been granted permission to use proxy "myProxy"."

Also notice the @enabled parameter in the sp_add_proxy call. We can't find this in the interface but when this parameter is set to 0 the proxy is disabled. The problem is that you CAN select
disabled proxies as the account to run the job with but the job fails on execution.