IMHO, with Development and test servers who cares? When they want that stuff on production servers, then you probably need to be a bit concerned as to what they are doing with it.
Michael
<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
I'm always afraid to grant developers too many permissions, but in this case I don't think I can come up with a good argument as to why they shouldn't be allowed.
One more question. Is there a way to grant Execute permission on xp_sendmail without giving someone the ability to have Select permission against system tables in master?
Yes, you could give them db_denydatareader role in the master database. I'm sure that would work. But does it matter if they can see the system objects? They can't do anything with them. What's the harm of seeing in a development or test environment?
I tried that and received an error message while attempting to connect to the server. Select Permission Denied on spt_values. That one surprised me.
I'm not sure if it is a problem. I'm asking our entire DBA team for their oppinion. The only potential issue I can see is opening up the possibility of more questions due to the extra info. they can find in the system tables.
Unfortunately that didn't work. It looks like the login process needs to access master..spt_values regardless of the default database. db_denydatareader automatically makes accessing spt_values impossible and it takes precedence over me explicitly granting select permission on spt_values to the user id. That kinda makes me wonder why db_denydatareader even exists in master if it does not allow someone to login to the database server. Perhaps there is another work-around I'm not aware of.
OK to Grant Execute on xp_sendmail to Developers?
Posted - 01/08/2004 : 16:29:05
