Transfering Login Permissions

• MarvinTheAndriod

  SSCarpal Tunnel

  Points: 4972

  More actions

  February 4, 2014 at 10:43 am

  #303989

  I am aware of the EXEC sp_help_revlogin script which lists out permissions and the ability of SQL SERVER to script permissions. But is there a way of transfering the permissions assocaited with those logins as well

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  February 4, 2014 at 10:51 am

  #1685948

  if you mean database permissions they go across with the database, or did you mean server level permissions?

  sp_help_revlogin list out logins, their sids, passwords and default databases.

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

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  February 4, 2014 at 11:24 am

  #1685959

  this gets server role permissions

  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

  SELECT A.state_desc + ' '+A.permission_name + ' TO [' + NAME COLLATE DATABASE_DEFAULT

  +']'

  FROM sys.server_permissions A (NOLOCK)

  inner join sys.server_principals B

  on A.grantee_principal_id = B.principal_id

  WHERE b.type IN ( 'S', 'U', 'G' )

  ---ORDER BY USER_NAME(grantee_principal_id)

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