need script to get new sql logins for every month.

• hanuv21

  SSC Enthusiast

  Points: 190

  More actions

  March 24, 2014 at 8:10 am

  #289724

  Hi DBA Team,

  I need to get new logins for every month and whether they are having sys admin permissions or not, but i have the script to get all the logins irrespective of months

  ------For all login members:

  IF exists (SELECT * from tempdb.sys.all_objects where name like '%#Login_Audit%')

  drop table #Login_Audit

  create table #Login_Audit (A nvarchar(500), B nvarchar(500) default(''), C nvarchar(500) default(''), D nvarchar(500) default(''))

  go

  insert into #Login_Audit

  select a,b,c,d from

  (select COUNT(name)a from sys.syslogins where name not like '%#%') a,

  (select COUNT(name)b from sys.syslogins where name not like '%#%'and isntuser=1) b,

  (select COUNT(name)c from sys.syslogins where name not like '%#%'and isntname=0) c,

  (select COUNT(name)d from sys.syslogins where name not like '%#%'and isntgroup=1) d

  go

  insert into #Login_Audit

  SELECT [Sys Admin Role]='Login_name','Type','Loginstaus',''

  go

  insert into #Login_Audit(A,B,C)

  SELECT a.name as Logins, a.type_desc, Case a.is_disabled

  when 1 then 'Disable'

  when 0 then 'enable'

  End

  From sys.server_principals a where a.type_desc IN ('WINDOWS_LOGIN','sql_login')--not in( 'SQL','server_role')

  --LEFT JOIN sys.server_role_members b ON a.principal_id=b.member_principal_id where role_principal_id=3

  order by a.name

  go

  SELECT * from #Login_Audit

  Fixed server role details:

  IF exists (SELECT * from tempdb.sys.all_objects where name like '%#Login_Audit%')

  drop table #Login_Audit

  create table #Login_Audit (A nvarchar(500), B nvarchar(500) default(''), C nvarchar(500) default(''), D nvarchar(500) default(''))

  go

  insert into #Login_Audit (A,B,C,D)

  SELECT

  [Fixed_server role] = '-- FIXED SERVER ROLE DETAILS --',' ----- ',' ----- ',' ----- '

  go

  insert into #Login_Audit (A,B,C,D)

  SELECT

  [Fixed_server role] = 'ROLE name',' Members ',' Type ',''

  go

  insert into #Login_Audit (A,B,C)

  SELECT c.name as Fixed_roleName, a.name as logins ,a.type_desc

  FROM sys.server_principals a

  INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id

  INNER JOIN sys.server_principals c ON c.principal_id = b.role_principal_id

  --WHERE a.principal_id > 250

  ORDER BY c.name

  go

  SELECT * from #Login_Audit

  can any one assist me on this.

  Thank you.

• Lowell

  SSC Guru

  Points: 323507

  More actions

  March 24, 2014 at 8:27 am

  #1699546

  assuming you run a job on the same day once a month, cna't you simply get anything created or modified in -1 months?

  select * from master.sys.database_principals

  WHERE create_date > dateadd(m,-1,getdate())

  or modify_date > dateadd(m,-1,getdate())

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!