User's With Access

• bellefso

  SSC-Addicted

  Points: 492

  More actions

  July 14, 2008 at 8:15 am

  #92271

  Hello,

  I would like to see a list of users that have access to only a certain database, but not access to other databases. I am curious if anyone has a way to do this? This is a SQL 2000 server.

  I guess the query would be similar to what is used to populate the GUI display when looking at a users properties to show what databases they have access to.

  Thanks,

  Brian

• bellefso

  SSC-Addicted

  Points: 492

  More actions

  July 14, 2008 at 8:26 am

  #842414

  A little more information...

  This appears to be the query running in the background...for each database

  SELECT u.name AS [Name],

  CAST(u.hasdbaccess AS bit) AS [HasDBAccess]

  FROM dbo.sysusers AS u

  WHERE (((u.issqlrole != 1 and u.isapprole != 1 )

  or (u.sid=0x00))

  and u.isaliased != 1

  and u.hasdbaccess != 0)

  and(ISNULL(suser_sname(u.sid),N'')=N'USERNAME')

  What would be a good way to get this into a query that only returns users with access to one db and no others?

  Thanks