February 11, 2010

When was my SQL Server Installed?

I was going through the SQL Server Security Catalog view and stumbled upon the sys.server_principals view. This view contains a row for every server-level principal. When you install SQL Server 2005/2008, the NT AUTHORITY\SYSTEM login gets created. Querying this view, we can easily retrieve the creation date. Here’s how:

SQL Server 2005/2008

SELECT create_date
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM'

SQL Server 2000

SELECT createdate
FROM sys.syslogins
where name='NT AUTHORITY\SYSTEM'

OUTPUT

image

If you know of a simpler way to retrieve the SQL Server installation date, make sure to drop a comment.

Note: This query will not give correct results, if in between your master database became corrupt and you had to rebuild it. I will update this post with other such exceptions(as and when I learn about them) when this query may not return expected results.


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

No comments: