RE: Scripting SQL server security

> > This morning I had a minor disaster on my development DB, nuking all of
the
> > permissions for access to my stored procedures.
>
> How'd you manage to do that?

I'm rather ashamed to say, it was pure stupidity. I was setting up a new
login/user in the DB, one that should have rather privileged access directly
to the tables. So I wanted it to have permissions on all the tables in a
couple of the schemas. Rather than GRANT SELECT etc. for each schema/action,
I stupidly granted ownership of those schemas to the role I was going to
assign the user to. (my excuse is that in the SSMS GUI, there doesn't seem
to be any way to grant permissions to a role for a whole schema)

It was stupid to do it that way in any case. But this had a side effect
removing permissions that other users should have had into those schemas. I
still don't get why changing the ownership from DBO to a "real" user has
this effect, but that's what did it.

So the bottom line was that re-assigning the permissions was a red herring.
The only way to really fix it was to give ownership of those schemas back to
DBO.