listing the users and logins

• middletree

  Hall of Fame

  Points: 3316

  More actions

  October 21, 2009 at 10:30 am

  #227283

  I want to get a list of all users and logins for my SQL Server 2005 instance. I have, I think, 8 databases on it.

  I have Googled and checked BOL, and have seen stuff about how to transfer logins from one to another, but I want to simply output a list of usernames and, if possible, what permissions they have. Is this doable/

• ps.

  One Orange Chip

  Points: 29423

  More actions

  October 21, 2009 at 10:40 am

  #1069003

  http://www.mssqltips.com/tip.asp?tip=1818

  This site gives excellant output on what you require.

  Though i use the below mentioned script (dont remember the source). It was written by Norm Eager.

  SELECT

  name AS Login,

  sysadmin =

  CASE

  WHEN sysadmin = 1 THEN 'X'

  ELSE ''

  END,

  securityadmin =

  CASE

  WHEN securityadmin = 1 THEN 'X'

  ELSE ''

  END,

  serveradmin =

  CASE

  WHEN serveradmin = 1 THEN 'X'

  ELSE ''

  END,

  setupadmin =

  CASE

  WHEN setupadmin = 1 THEN 'X'

  ELSE ''

  END,

  processadmin =

  CASE

  WHEN processadmin = 1 THEN 'X'

  ELSE ''

  END,

  diskadmin =

  CASE

  WHEN diskadmin = 1 THEN 'X'

  ELSE ''

  END,

  dbcreator =

  CASE

  WHEN dbcreator = 1 THEN 'X'

  ELSE ''

  END,

  bulkadmin =

  CASE

  WHEN bulkadmin = 1 THEN 'X'

  ELSE ''

  END,

  CONVERT(CHAR(16),createdate,120) AS 'DateCreated'

  FROM master.dbo.syslogins

  WHERE

  sysadmin = 1

  ORDER BY NAME

  GO

  ---

  
  Pradeep Singh

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  October 21, 2009 at 12:10 pm

  #1069034

  This will not give you a simple list, but rather everything you wanted to know about users and their permissions. It produces a HTML document. The actual T-SQL is too long to post on this forum but search for:

  [spAuditUsersPermissions]

  /* Written By: Michelle Gutzait -- 8/18/2009

  MSSQLTips.com

  brought to you by Edgewood Solutions

  I have used it and it works just great.

  http://www.mssqltips.com/tip.asp?tip=1818

  Edited 2:16 PM added link

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• middletree

  Hall of Fame

  Points: 3316

  More actions

  October 21, 2009 at 12:14 pm

  #1069037

  that is a good start, but it only gives me a resultset of 10, when I can see many more listed when I expand the Logins tree.

  this was in reference to the first reply.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  October 21, 2009 at 12:18 pm

  #1069040

  that is a good start,

  Are you commenting on the MSSQL Tips posting?

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Ignacio A. Salom Rangel

  SSC-Insane

  Points: 20443

  More actions

  October 21, 2009 at 2:15 pm

  #1069115

  Handy scripts!

  ---

  
  
  My blog
  

• middletree

  Hall of Fame

  Points: 3316

  More actions

  October 21, 2009 at 2:42 pm

  #1069133

  bitbucket-25253 (10/21/2009)

  ---

  that is a good start,

  Are you commenting on the MSSQL Tips posting?

  No, you posted that as I was writing my reply.

  As for the one you posted, I ran it and it output a lot of info. Probably all I need. Thanks!