Query to get all logins and access info

• dba_user

  Default port

  Points: 1458

  More actions

  May 27, 2003 at 3:11 pm

  #101495

  We have several logins created that have 'select' rights on certain tables. I have to go back and list the login and the tables that it has 'select' rights. Is there a system table that I can query to give me this information?

  Thanks!!

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  May 28, 2003 at 12:31 am

  #458757

  Hi JMeyers32,

  sp_helpprotect should do what you need

  This is taken from BOL:

  A. List the permissions for a table

  This example lists the permissions for the titles table.

  EXEC sp_helprotect 'titles'

  B. List the permissions for a user

  This example lists all permissions that user Judy has in the current database.

  EXEC sp_helprotect NULL, 'Judy'

  Cheers,

  Frank

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Antares686

  SSC Guru

  Points: 125444

  More actions

  May 28, 2003 at 4:15 am

  #458791

  If you use any roles then you may not get those listed. Take a look at this script to help in that area.

  http://qa.sqlservercentral.com/scripts/contributions/268.asp