I was migrating a database from SQL Server 2000 to SQL Server 2005 today and was a little puzzled that none of the users appeared in the public role in SQL 2005. It took me a minute to realize that nothing has really changed — it’s just that since all users are members of the public role, and can’t be removed from that role, Microsoft decided not to bother to show the role at all. Personally I liked the SQL Server 2000 Enterprise Manager UI better in this regard — it shows users in the public role, but it’s grayed out, and if you try to remove them from the role you get a helpful dialog explaining that it’s not allowed.
I mean, if you have a list box labeled “Database role membership”, shouldn’t it show all the roles? It doesn’t say “Non-default database role memberships”. I suppose it’s not of great concern unless you’re spot-checking that your users and roles came across correctly and comparing them in both the old and new management tools. But on the other hand, “out of sight” equals “out of mind”. Maybe it’s a good idea to remind folks that those default permissions exist, reduced though they may be in the new version of the product.
