Logins and Server Roles for a DB

• manus4u

  Ten Centuries

  Points: 1238

  More actions

  September 14, 2008 at 2:01 am

  #190765

  Hi,

  I have an SP with me which provides me with all the DB Logins and Roles associated. I need to get Server Roles too along with it.

  Can someone provide me with a script which will give me all the DB & Server roles for all logins?

  OR

  Pls provide me with a script to get all Logins and their associated Server Roles for a DB.

  Thnx in Adv

  Any help is really appreciated!

  Manu

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  September 14, 2008 at 8:42 am

  #871545

  Logins don't have database roles, only users.

  This information is easily stored in security system catalog views.

  http://msdn.microsoft.com/en-us/library/ms188786.aspx

  http://msdn.microsoft.com/en-us/library/ms190331.aspx

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  September 15, 2008 at 7:39 am

  #871730

  the following reverse engineers the code to re-apply all server roles granted to logins:

  set quoted_identifier off

  set nocount on

  select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

  +"'sysadmin'"

  from syslogins where sysadmin = 1

  UNION ALL

  select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

  +"'securityadmin'"

  from syslogins where securityadmin = 1

  UNION ALL

  select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

  +"'serveradmin'"

  from syslogins where serveradmin = 1

  UNION ALL

  select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

  +"'setupadmin'"

  from syslogins where setupadmin = 1

  UNION ALL

  select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

  +"'processadmin'"

  from syslogins where processadmin = 1

  UNION ALL

  select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

  +"'diskadmin'"

  from syslogins where diskadmin = 1

  UNION ALL

  select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

  +"'dbcreator'"

  from syslogins where dbcreator = 1

  UNION ALL

  select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

  +"'bulkadmin'"

  from syslogins where bulkadmin = 1

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

• kjplreddy

  SSC Veteran

  Points: 216

  More actions

  July 17, 2010 at 12:45 am

  #1194478

  Hi,

  for db logins try it:sp_helprevlogin