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
