March 24, 2014 at 8:10 am
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.
March 24, 2014 at 8:27 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply