Here is an interesting problem and it seems I’ve been queried on several times in the past month so let’s see if I can’t give you a quick fix or “answer” as it were here today. The question has been:
“I created a userid that has db_datareader membership in master so that I can view all logins. However, it only returns itself and the sa accounts. I know that isn’t right but the only way I can make it see all logins is if I make the account a member of db_owner. Is there a way I can do this without the accounts being db_owner?”
In SQL 2000 and earlier editions all users had read access to all metadata.
Now enter SQL 2005. By default users cannot see metadata for which they do not have privileges.
Let’s try and keep it simple shall we?
“VIEW DEFINITION” allows you see the definition of an object, not the definition of a view.
Did that help?