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: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] 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
