How can list all the users of a particular database.

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  July 18, 2011 at 8:28 am

  #244465

  Hi,

  How can list all the users of a particular database.

  I want to list only those users coming under the "Users" in "Security".

  Thanks.

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  July 18, 2011 at 8:38 am

  #1354263

  Joy Smith San (7/18/2011)

  ---

  Hi,

  How can list all the users of a particular database.

  I want to list only those users coming under the "Users" in "Security".

  Thanks.

  This should get you started, it also pulls any user created database roles

  USE [YourDB]

  select suser_sname(SID) as ServerLevelLogin,

  name as DatabaseLevelUser,

  principal_id,

  type_desc,

  create_date,

  modify_date

  from sys.database_principals

  where principal_id >= 5 and is_fixed_role <> 1

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

  "Ya can't make an omelette without breaking just a few eggs" 😉

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  July 19, 2011 at 2:16 am

  #1354648

  Thanks Perry.

  Will this work for SQL 2000 as well.? If not whats the equivalent.?

  Thanks again.

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  July 19, 2011 at 4:04 am

  #1354689

  Joy Smith San (7/19/2011)

  ---

  Thanks Perry.

  Will this work for SQL 2000 as well.? If not whats the equivalent.?

  Thanks again.

  No, the following will work for SQL Server 2000.

  select ISNULL(suser_sname(SID), 'No Mapping') as ServerLevelLogin,

  name as DatabaseLevelUser,

  case

  when islogin = 1 then 'Login'

  else 'Role'

  end as type_desc,

  case

  when issqluser = 1 then 'SQL User'

  else 'Windows User\Group'

  end as type_desc2,

  createdate,

  updatedate

  from sysusers

  where name not like '[dpg][bu][_obe]%'

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

  "Ya can't make an omelette without breaking just a few eggs" 😉

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  July 20, 2011 at 12:32 am

  #1355379

  Thanks a lot.