Right from AD user

• gperuch

  Valued Member

  Points: 55

  More actions

  July 22, 2009 at 2:12 am

  #134454

  Hi everybody

  I would like to know wich right get a user from AD

  for exemple

  the user greg in the AD is data reader on DBmydata because in on "DBmydata_reader" group

  is there a procedure like this?

  sp_get_right 'greg'

  result :

  right db group

  data reader DbMydata DBmydata_reader

  data writer Dbtoto DBtoto_reader

  thank

• Animal Magic

  SSChampion

  Points: 13964

  More actions

  July 22, 2009 at 3:13 am

  #1028159

  Take a look at this script, its a bit rough and ready but will show you at a high level what you need. you could amend it and put params in etc

  print 'Server Access'

  SELECT name, type_desc, is_disabled

  FROM sys.server_principals

  print 'Database Access'

  SELECT

  UserName = dp.name, UserType = dp.type_desc, LoginName = sp.name, LoginType = sp.type_desc

  FROM sys.database_principals dp

  JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id

  print 'Server Roles'

  select p.name, p.type_desc, pp.name, pp.type_desc

  from sys.server_role_members roles

  join sys.server_principals p on roles.member_principal_id = p.principal_id

  join sys.server_principals pp on roles.role_principal_id = pp.principal_id

  print 'Database Roles'

  SELECT p.name, p.type_desc, pp.name, pp.type_desc, pp.is_fixed_role

  FROM sys.database_role_members roles

  JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

  JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

  print 'Object Permissions'

  SELECT dp.class_desc, dp.permission_name, dp.state_desc,

  ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name

  FROM sys.database_permissions dp

  JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id

  JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id