Read access to Master Tables

• BobAtDBS

  SSCarpal Tunnel

  Points: 4592

  More actions

  August 11, 2006 at 3:28 pm

  #173772

  In SQL 7, I wrote a SP that let a user have find out who was logged on to the server.  The code in part looked like this:

      Select (Fields) FROM

  master..sysprocesses s

      LEFT

  JOIN master..sysdatabases d ON s.dbid = d.dbid

  The SP lives in a normal database, and it uses some other information from that database.  My problem, now that I've moved the database to SQL Server 2005, is that even after I give that logon Select access to the two views mentioned above, the users only see themselves rather than everyone logged in.  If I (very temporairily) make the user a sysadmin, they see everyone just fine. 

  I'm sure it's a permissions issue, just not sure where to look/what to do.  I've read something about database permission chaining, not enough to understand.

  Any takers?

  
  Student of SQL and Golf, Master of Neither

• Michelle-164472

  SSCommitted

  Points: 1715

  More actions

  August 16, 2006 at 7:12 am

  #655245

  Maybe granting "db_datareader" role will solve your problem...

  http://www.sqlservercentral.com/columnists/bkelley/sqlserversecurityfixeddatabaseroles.asp