Automation: Granting Read Perms for Developers

Yeah, you heard me. “But Kendra, why would we want to grant developers read  permissions? And why would we automate  it? And at what point did you lose your mind?” Well, Virginia, there may or may not be a Santa Claus, but there are a lot of developers and good reasons to give them read access on many SQL Server instances. In my world, it’s every instance in the pre-production environment, which is a couple  hundred and growing. And it ain’t just read they’re needing, they should be empowered with all of: In master: View server state– so they can look at all those pesky spids they can block, and which may be causing problems they’re investigating.In msdb: db_datareader — so they can query things not easily seen through the GUI SQLAgentReaderRole — so they can look at currently executing jobs and history through the GUI In each user database:…
Read More

Checking Permissions on Linked Servers

One reason I started this blog was just the idea of going through my catalog of scripts and reviewing them and sharing out what might be useful to people.

Here is a quick one I put together a while back. I was starting to work with a group of servers [at an unnamed company, always an unnamed company!]. Some of the instances had been configured long ago, and I found some linked servers where passwords had been hardcoded into the login mappings.

This can be a big security vulnerability, particularly if the option has been chosen to map all users to that login, and the login has significant powers on the other end of the linked server….

Read More