During the SQL Server 2012 class I was working to finally nail down options for managing security across the numerous web platforms. One this to note is there is no good reason why dbo cannot own these roles as they should be created for reuse. The exception would be the single use like settings where it may make more sense to isolate at a lower level. It would take a typical and look like this
The disadvantage of this method is unless you include db_datareader or db_datawriter every object must be added by hand. however it can be used for masking tables and source objects from users. To set below allow the SP to be shown and executed.
 |
| Final screen shot of the role allowing access but locking the user out of changes and the definition |
The masking for select would be as below
