sysusers (SQL 2000) vs sys.sysusers or sys.database_principals (SQL 2005)

• Carl B.

  SSCertifiable

  Points: 7931

  More actions

  December 6, 2006 at 11:55 am

  #115202

  Hello everyone,

  We are working hard to ugrade our dev. environnement to use SQL Server 2005.

  We were using 2000 and 7.0 for a while.

  There is an issue that we don't really understand:

  In SQL Server 2000 if I am a user in a database (role public) I can query the sysusers table and see all other users that are defined in that database.

  In SQL Server 2005 if I am a user in a database (role public) I can only see a subset of what is in sys.sysusers or sys.database_principals... The roles, guest, sys, dbo, INFORMATION_SCHEMA and myself but no one elses.

  Here is an example:

  Connected as user jos:

  select name from sys.database_principals

  name

  --------------------------------------------------------------------------------------------------------------------------------

  public

  dbo

  guest

  INFORMATION_SCHEMA

  sys

  jos

  db_owner

  db_accessadmin

  db_securityadmin

  db_ddladmin

  db_backupoperator

  db_datareader

  db_datawriter

  db_denydatareader

  db_denydatawriter

  (15 row(s) affected)

  Connected as the dbo:

  select name from sys.database_principals

  name

  --------------------------------------------------------------------------------------------------------------------------------

  public

  dbo

  guest

  INFORMATION_SCHEMA

  sys

  sp

  beaucath

  denism

  dionmoni

  huguette

  isabel

  jos

  lyne

  martinel

  toupdomi

  turcmary

  db_owner

  db_accessadmin

  db_securityadmin

  db_ddladmin

  db_backupoperator

  db_datareader

  db_datawriter

  db_denydatareader

  db_denydatawriter

  (25 row(s) affected)

  Something known?

  Best regards.

  Carl

• Carl B.

  SSCertifiable

  Points: 7931

  More actions

  December 6, 2006 at 1:06 pm

  #676388

  Forget it we found:

  http://www.microsoft.com/technet/technetmag/issues/2006/01/ProtectMetaData/?topics=y

  That explain this behavior.

  Regards.

  Carl