not able to get data for user info from sysusers and database_role_members system views

• SumLasty

  SSCommitted

  Points: 1923

  More actions

  March 26, 2010 at 6:30 am

  #220452

  Hi,

  I have created a login test with public role and select privileges on system views sys.sysusers, sys.database_principals and sys.database_role_members. When i login with login test' in the user db context, below query not resulting any output. Nut when I make login test as part od db_securityadmin it is giving the results. please advice if there is any way to get the results with out having db_securityadmin role assigned to login test.

  query:

  SELECT P.name FROM sys.database_principals AS P inner join

  sys.database_role_members AS RM ON RM.role_principal_id = P.principal_id inner join

  sys.sysusers AS U on RM.member_principal_id = U.uid and

  U.name = 'test'

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  March 26, 2010 at 7:44 am

  #1140342

  Your query is not showing any result as "TEST" login is not db_owner (as your query will only work for db_owner)

  and you can use use youydb

  go

  SELECT P.name

  FROM sys.database_principals AS P

  --inner join sys.database_role_members AS RM

  --ON RM.role_principal_id = P.principal_id

  inner join sys.sysusers AS U

  on p.principal_id = U.uid

  and U.name = 'test'

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)