Help with: sys.server_role_members

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?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: