In most cases, you'll be better to do a combination of what's been suggested.
I wouldn't base new designs on fixed database roles. The doco already says "Fixed database roles are provided for convenience and backward compatibility". That's product-group-speak for "we're going to make these go away as soon as we get an excuse but we don't want to scare you yet". I suspect that setting up user-defined database roles will do what's needed. In general, we try to create logins for windows groups and add them to the user-defined database roles. Also, rather than granting permission on every sproc and function (need to consider functions too), group them together in a schema based on security requirements and then grant EXECUTE permission at the schema level to the user-defined database roles. HTH, Greg From: [email protected] [mailto:[email protected]] On Behalf Of Ken Schaefer Sent: Sunday, 23 January 2011 8:53 PM To: ozDotNet Subject: RE: Avoiding SQL Server 'sysadmin' accounts Grant EXEC permission on the sproc to your accounts (assuming you don't have too many of them). That said, the use of SQL Server accounts is also diminishing in most large environments because SQL Server doesn't provide the sort of monitoring/logging of SQL Server accounts as is available for Windows accounts. Cheers Ken From: [email protected] [mailto:[email protected]] On Behalf Of Greg Keogh Sent: Sunday, 23 January 2011 3:40 PM To: 'ozDotNet' Subject: Avoiding SQL Server 'sysadmin' accounts Folks, for years now I've had the bad habit of using database connection strings that uses an account in the 'sysadmin' role. Usually 'sa' or a trusted connection while I'm in the Administrators group. The apps don't go into production like that, but the habit lingers in my development environment. I've never really studied SQL Server security so I was looking for a way of making safer connections strings with minimum effort and brainpower. My first experiment was to create a SQL Login and map it to my database in the 'datareader' and 'datawriter' roles. I was hoping this would at least sandbox the connection to a sensible subset of functionality. I certainly can read and write, but sadly, I can't execute Stored Procedures with my new account. So my simple fix doesn't work as easily as I hoped. Rather than futz around for hours on this and start altering permissions on procs, I was wondering what others have done in similar situations. Does anyone else have nice tricks for making connections with more sensible permissions? Cheers, Greg
