April 28, 2015 at 12:14 pm
I need to find the one day old(in last 24hrs) user details:
Below script i am using , but its not working as expected.
Any suggestions please.
select name,createdate,* from syslogins where isntgroup=0 and isntname=1 and name not LIKE ('%sa-%') and name not LIKE ('%NT %') and createdate > DATEADD(hh,-24,GETDATE())
April 28, 2015 at 12:31 pm
charipg (4/28/2015)
I need to find the one day old(in last 24hrs) user details:Below script i am using , but its not working as expected.
Any suggestions please.
select name,createdate,* from syslogins where isntgroup=0 and isntname=1 and name not LIKE ('%sa-%') and name not LIKE ('%NT %') and createdate > DATEADD(hh,-24,GETDATE())
What is not working about the query?
Is it just that you get no results?
No results would mean no user has been created within the past 24 hours.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 28, 2015 at 12:38 pm
there are users created before 24hrs, but result is 0 rows .
is the condition is correct ?
createdate > DATEADD(hh,-24,GETDATE())
April 28, 2015 at 12:45 pm
Yes, that is the correct expression to restrict createdate to the last 24 hours.
Your query is also restricting the results to Windows authenticated logins (isntname=1), so you wouldn't see any SQL authenticated logins that had been created.
Perhaps that is the issue?
Cheers!
April 28, 2015 at 12:57 pm
You do realize that in SQL Server 2005 and newer you should really be looking at sys.server_principals and not syslogins for this information.
April 28, 2015 at 1:07 pm
charipg (4/28/2015)
there are users created before 24hrs, but result is 0 rows .is the condition is correct ?
createdate > DATEADD(hh,-24,GETDATE())
Based on your statement that means you have users created that were created longer ago than 24 hrs. Your query is only looking at logins created within the past day (the last 24 hours).
This query is correct for only looking at the last 24 hours.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 28, 2015 at 1:08 pm
And here is a more up to date version of the query (sql 2005 and newer).
/*
WINDOWS_GROUP eliminates the isntgroup
WINDOWS_LOGIN eliminates the isntname
*/
SELECT sp.name ,
sp.principal_id,
sp.create_date ,
sp.type_desc,
sp.is_disabled
FROM sys.server_principals sp
WHERE 1 = 1
AND sp.name NOT LIKE ( '%NT Service%' )
AND sp.name NOT LIKE ('%NT Auth%')
AND sp.principal_id NOT BETWEEN 101 AND 255 --eliminate certificates
AND sp.type_desc = 'WINDOWS_LOGIN'
AND sp.create_date > DATEADD(hh, -24, GETDATE());
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply