SQLServerCentral / Discuss Content Posted by Brian Kelley / Article Discussions / Article Discussions by Author / SQL Server Security: The db_executor Role / Latest Posts

RE: SQL Server Security: The db_executor Role

K. Brian Kelley — Tue, 14 Jul 2009 12:50:38 GMT

[quote][b]sho (7/13/2009)[/b][hr]wow - quick response :-)thanks!think i'll have to look into "ownership chaining" but if you have any good links to this feel free to pass them on.cheers brian![/quote]An intro on ownership chaining in 2005/2008:[url=http://www.mssqltips.com/tip.asp?tip=1778]Ownership chaining in SQL Server security feature or security risk[/url]

RE: SQL Server Security: The db_executor Role

sho-467341 — Mon, 13 Jul 2009 11:27:21 GMT

wow - quick response :-)thanks!think i'll have to look into "ownership chaining" but if you have any good links to this feel free to pass them on.cheers brian!

RE: SQL Server Security: The db_executor Role

K. Brian Kelley — Mon, 13 Jul 2009 11:24:09 GMT

Yes, if you have taken advantage of ownership chaining.

RE: SQL Server Security: The db_executor Role

sho-467341 — Mon, 13 Jul 2009 11:05:58 GMT

hi, this may be a stupid question, so apologies in advance (i'm not a dba!)... does this solution work if the users are not members of the db_datawriter role?for example:if I have a stored procedure that does an INSERT and user executing the stored procedure is a member of the user created "db_executer" role but not of the "db_datawriter" role would the user still be able run the stored procedure so that it inserts data into a table?cheers

RE: SQL Server Security: The db_executor Role

Brendan Kerry — Sun, 21 Jan 2007 19:27:00 GMT

Excellent article and discussion, what would I do without sqlservercentral?

It occurred to me that with SQL 2005 you could combine it with a ddl trigger and eliminate the need for scheduling and the sql agent altogether:

create trigger tdGrantExecuteon database for create_procedure, create_functionasbegin declare @Exe varchar(128) declare @sql varchar(1000)

declare cExe cursor forward_only for select objects.name from sys.objects inner join sys.schemas on objects.schema_id = schemas.schema_id where schemas.name = 'dbo' and type in ('P', 'FN', 'FS','AF','PC')

open cExe fetch next from cExe into @Exe

while @@fetch_status = 0 begin set @sql = 'GRANT EXECUTE ON dbo.[' + @Exe + '] TO db_executor' exec (@sql)

fetch next from cExe into @Exe end

close cExe deallocate cExe

/*** SQL 2005 Object Types **AF = Aggregate function (CLR)C = CHECK constraintD = DEFAULT (constraint or stand-alone)F = FOREIGN KEY constraintPK = PRIMARY KEY constraintP = SQL stored ProcedurePC = Assembly (CLR) stored ProcedureFN = SQL scalar functionFS = Assembly (CLR) scalar functionFT = Assembly (CLR) table-valued functionR = Rule (old-style, stand-alone)RF = Replication-filter-ProcedureSN = SynonymSQ = Service queueTA = Assembly (CLR) DML triggerTR = SQL DML trigger IF = SQL inlined table-valued functionTF = SQL table-valued-functionU = Table (user-defined)UQ = UNIQUE constraintV = ViewX = Extended stored ProcedureIT = Internal table*/end

Note that in my environment I've restricted access to only the dbo schema.

Thanks again!

RE: SQL Server Security: The db_executor Role

Olga B — Tue, 25 Jul 2006 16:22:00 GMT

Melvin, that's an excellent idea that will be very helpful to me. One question, though. Why not assign all permissions through one cursor, like so? DECLARE cursAllExecutables CURSOR FAST_FORWARDFORSELECT USER_NAME(uid) Owner, [name] StoredProcedure FROM sysobjects where xtype in ('P', 'TF', 'FN') Seemed to work here.

RE: SQL Server Security: The db_executor Role

MELVYN PATRICK LOPEZ — Thu, 09 Mar 2006 14:04:00 GMT

Dear Brian:

I really am a fan of yours after going thru this..

In my case, I took it a step further by adding the Table functions and the regular functions... I thank you very much for your contribution...I am a happy DBA!

Enclosed is the code for a stored proc which incorporates the granting of permissions to stored procs, table functions and regular functions. By creating this procedure in master database, I can call it from any user database to maintain permissions for the db_executor role.

MELVYN

Create proc sp_grantpermsprocsfunctionsas /** Procedure : sp_grantpermsprocsfunctions** ** File : ** ** Author: MELVYN PATRICK LOPEZ ** Date: 03/08/2006**** Description : This Procedure is to be used to maintain db_executor role

** Inspired by Brian Kelly at SQL SERVER CENTRAL** ** ** ** Parameters : NONE ** ** Return Values : ** ** Data Retrieval Values: ** ** Errors: **** Tables** Select : cursor** ** ** Insert : ** ** Update : ** ** Delete : ** ** Procedures :** Calls : sp_executesql ** ** Called by : ** ** Modified:*****/

BEGIN

DECLARE @SQL nvarchar(4000), @Owner sysname, @StoredProcedure sysname, @Return int

-- Cursor of all the stored procedures in the current databaseDECLARE cursStoredProcedures CURSOR FAST_FORWARDFORSELECT USER_NAME(uid) Owner, [name] StoredProcedureFROM sysobjectsWHERE xtype = 'P'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first rowFETCH NEXT FROM cursStoredProceduresINTO @Owner, @StoredProcedure

-- Set the return code to 0SET @Return = 0

-- Encapsulate the permissions assignment within a transactionBEGIN TRAN

-- Cycle through the rows of the cursor-- And grant permissionsWHILE ((@@FETCH_STATUS = 0) AND (@Return = 0)) BEGIN -- Create the SQL Statement. Since we're giving -- access to all stored procedures, we have to -- use a two-part naming convention to get the owner. SET @SQL = 'GRANT EXECUTE ON [' + @Owner + '].[' + @StoredProcedure + '] TO db_executor'

-- Execute the SQL statement EXEC @Return = sp_executesql @SQL

-- Get the next row FETCH NEXT FROM cursStoredProcedures INTO @Owner, @StoredProcedure END

-- Clean-up after the cursorCLOSE cursStoredProceduresDEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.IF (@Return = 0) BEGIN -- Exited fine, commit the permissions COMMIT TRAN ENDELSE BEGIN -- Exited with an error, rollback any changes ROLLBACK TRAN -- Report the error SET @SQL = 'Error granting permission to [' + @Owner + '].[' + @StoredProcedure + ']' RAISERROR(@SQL, 16, 1) END----------------------------------------------------------------------------DECLARE cursTableFunctions CURSOR FAST_FORWARDFOR SELECT USER_NAME(uid) Owner, [name] TableFunction FROM sysobjects WHERE xtype = 'TF'

OPEN cursTableFunctions

-- "Prime the pump" and get the first rowFETCH NEXT FROM cursTableFunctionsINTO @Owner, @StoredProcedure

-- Set the return code to 0SET @Return = 0

-- Encapsulate the permissions assignment within a transactionBEGIN TRAN

-- Cycle through the rows of the cursor-- And grant permissionsWHILE ((@@FETCH_STATUS = 0) AND (@Return = 0)) BEGIN -- Create the SQL Statement. Since we're giving -- access to all stored procedures, we have to -- use a two-part naming convention to get the owner. SET @SQL = 'GRANT SELECT ON [' + @Owner + '].[' + @StoredProcedure + '] TO db_executor'

-- Execute the SQL statement EXEC @Return = sp_executesql @SQL

-- Get the next row FETCH NEXT FROM cursTableFunctions INTO @Owner, @StoredProcedure END

-- Clean-up after the cursorCLOSE cursTableFunctionsDEALLOCATE cursTableFunctions

-------------------------------------

DECLARE cursFunctions CURSOR FAST_FORWARDFOR SELECT USER_NAME(uid) Owner, [name] fFunction FROM sysobjects WHERE xtype = 'FN'

OPEN cursFunctions

-- "Prime the pump" and get the first rowFETCH NEXT FROM cursFunctionsINTO @Owner, @StoredProcedure

-- Set the return code to 0SET @Return = 0

-- Encapsulate the permissions assignment within a transactionBEGIN TRAN

-- Execute the SQL statement EXEC @Return = sp_executesql @SQL

-- Get the next row FETCH NEXT FROM cursFunctions INTO @Owner, @StoredProcedure END

-- Clean-up after the cursorCLOSE cursFunctionsDEALLOCATE cursFunctions

ENDGO

RE: SQL Server Security: The db_executor Role

nlitchfield — Tue, 18 May 2004 02:32:00 GMT

I'd also add to the comments about production and use of change management procedures. I'd never allow a procedure that automatically granted changed permissions to users to run in production (or for that matter test). Permissions to production data should be controlled, and in my view test should replicate the live environment - with the exception of the specific change that you are testing.

RE: SQL Server Security: The db_executor Role

Razvan Socol — Fri, 12 Dec 2003 02:08:00 GMT

We should also grant to db_executor the EXECUTE permission for scalar functions. It is interesting to note that:- Inline Table-valued Functions have SELECT, INSERT, UPDATE and DELETE permissions- Multi-statement Table-valued Functions have only the SELECT permission- Scalar Functions have only the EXECUTE permissionHere is how I would write the SELECT statement for the cursor:SELECT USER_NAME(uid) as Owner, [name] as StoredProcedureFROM sysobjects so WHERE (xtype='P' or xtype='FN') AND OBJECTPROPERTY([id], N'IsMSShipped')=0 AND [id] NOT IN (SELECT [id] FROM sysprotects sp WHERE UID=USER_ID('db_executor') AND [action]=224)Razvan

RE: SQL Server Security: The db_executor Role

K. Brian Kelley — Tue, 20 May 2003 06:57:00 GMT

Yup, sure have. And I've been in this position as a systems administrator with responsibility over the enterprise no authority. That's just as bad. K. Brian Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: SQL Server Security: The db_executor Role

Mike Pearson — Tue, 20 May 2003 06:45:00 GMT

Agreed all round. We've been using this model for some years years now and it has made a world of difference.It's a moot point - but how many DBA's out there are in a position where they have a say in what authentication models get used?I am surprised by the number of of client's DBA's who are held responsible for the integrity of the databases under their jurisdiction - whilst at the same time have absolutely no say in the security/access models used by developers.Have other folks out there found this to be a problem?

RE: SQL Server Security: The db_executor Role

K. Brian Kelley — Mon, 19 May 2003 11:07:00 GMT

Typically, I recommend creating the permissions with the stored proc and then whatever change control mechanism you use, stored procedure and permissions are stored together to be run together. Eliminates a lot of error.K. Brian Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: SQL Server Security: The db_executor Role

eliassal — Mon, 19 May 2003 10:13:00 GMT

Very nice article. I I think it would be a god idea to create a template for stored proc, at the end of it's creation it grants permission for db_executor for itself, no ?

RE: SQL Server Security: The db_executor Role

Greg M. Lucas — Mon, 19 May 2003 02:12:00 GMT

We use a dozen or so standard database roles - often added to the model database to save recreating in each new database, examples are:LOG_WRITERUSER_READUSER_WRITEADMIN_READADMIN_WRITEUSER_EXECUTEADMIN_EXECUTENO_ACCESSAS these roles are standard and appear in all databases (dev, test & live) - All stored procedure and table scripts in SourceSafe inlude the granting of relevant permissions to all standard roles.So if I'm creating a sproc called GetRecentTrades I will grant execute permissions to USER_EXECUTE and possibly ADMIN_EXECUTE. I don't care which users will be given permission to use that sproc later - that can be handled in production (ultimately). This means that I can manage permissions at the initial coding stage rather than in implementation to each successive environment - avoiding the risk that permissions for some action might be missed in live.The NO_ACCESS role is standard and all permissions are always denied to every object (sprocs, tables, views etc.) - with CASCADE. This makes decommissioning selected user accounts easier. I'm sure many of you have experienced the problem where a user account may have more than one use - but not necessarily documented. I can add this user to NO_ACCESS in one or more databases and if something unexpectedly breaks it's relatively simple to take the user out of NO_ACCESS in the relevant database(s) - especially when compared to how you'd recover after deleting that user.It's always interesting to know what fellow professionals think of how one does things so please feel free to comment.

RE: SQL Server Security: The db_executor Role

K. Brian Kelley — Sat, 17 May 2003 09:07:00 GMT

That works well, too. You know, that technique solves an issue I've seen posted here a lot... how to give developers the ability to create objects as dbo but without giving them the ability to carte-blanche change security settings!K. Brian Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: SQL Server Security: The db_executor Role

Lhot — Sat, 17 May 2003 00:39:00 GMT

I would have to say that this is indeed a good idea for development environment. We have been implementing this on our dev environment. I have to agree with Brian that the problem with this is, of course, keeping the permissions up-to-date. And I would like to share our own way of dealing with this. We have also created a SQL Server Agent for the script that grants permission on all stored procedure to a particular role but as a DBA, i didn't assign a schedule for it to run, instead, I created an alert for that when triggered, will then trigger the SQL Server Agent that runs the script. So when a developer creates or drop&re-create a stored procedure, all he has to do is to execute a raiserror to trigger the SQL Server Agent that grants execute permission on all stored procedure for that particular role.I hope I was able to share something worthwhile.Lhot

RE: SQL Server Security: The db_executor Role

K. Brian Kelley — Thu, 15 May 2003 07:35:00 GMT

I can't claim credit for the idea. I saw it in something that Richard Waymire of Microsoft wrote. However, it's an easy concept to make happen.K. Brian Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: SQL Server Security: The db_executor Role

Robert W Marda — Thu, 15 May 2003 07:32:00 GMT

We have a similar role in our development, testing, and production environments. It never occured to me to set something up to automatically grant execute permissions. I agree with Brian that the db_executor role would make a nice addition to the SQL Server roles.Nice article Brian.Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: SQL Server Security: The db_executor Role

ckempste — Thu, 15 May 2003 07:25:00 GMT

Hi BrianYep, deal with this one regularly and its real pain if there are large numbers of stored procs and you have inherited a system where "dbo" privs are the apparent norm!CheersCkwww.chriskempster.com

RE: SQL Server Security: The db_executor Role

dbamark — Mon, 12 May 2003 05:51:00 GMT

This is a very good way to go. I would also add that you might want to do the same things with views!!!

SQL Server Security: The db_executor Role

K. Brian Kelley — Sat, 10 May 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritythedb_executorrole.asp