Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL Server Security: The db_executor Role Expand / Collapse
Author
Message
Posted Saturday, May 10, 2003 12:00 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritythedb_executorrole.asp

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #12091
Posted Monday, May 12, 2003 5:51 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 6:59 AM
Points: 692, Visits: 53
This is a very good way to go. I would also add that you might want to do the same things with views!!!




Post #61937
Posted Thursday, May 15, 2003 7:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1
Hi Brian

Yep, 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!

Cheers

Ck
www.chriskempster.com




Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Post #61938
Posted Thursday, May 15, 2003 7:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.




Robert W. Marda
SQL Programmer
Ipreo
Post #61939
Posted Thursday, May 15, 2003 7:35 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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 Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #61940
Posted Saturday, May 17, 2003 12:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 21, 2004 10:54 AM
Points: 2, Visits: 1
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




Post #61941
Posted Saturday, May 17, 2003 9:07 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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 Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #61942
Posted Monday, May 19, 2003 2:12 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:54 AM
Points: 53, Visits: 165
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_WRITER
USER_READ
USER_WRITE
ADMIN_READ
ADMIN_WRITE
USER_EXECUTE
ADMIN_EXECUTE
NO_ACCESS

AS 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.



Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
Post #61943
Posted Monday, May 19, 2003 10:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 3, 2013 12:52 PM
Points: 519, Visits: 26
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 ?




Post #61944
Posted Monday, May 19, 2003 11:07 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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 Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #61945
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse