Open In App

Finding Login Counts and Creation Dates in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

A SQL login is another credential that allows you to access SQL Server. When you log on to Windows or even your e-mail account, for example, you enter your username and password. The logins are made up of such a username and password. As a result, SQL logins are just a username and password.

A SQL login is a security principal that applies to the whole SQL Server instance, which can contain many databases. Windows authenticated login and SQL Server authenticated login are the two basic forms of login.

We usually require access to a specific database once we have joined SQL Server. This login must be mapped to a database user in order to gain access to the database. A login is mapped and identified to a user using a security identifier within SQL Server (SID).

There are four different types of logins available in SQL Server:

  • Windows credentials are used to log in.
  • A SQL Server-specific login.
  • A login that is associated with a certificate.
  • A symmetric key is mapped to a logon.

Refer Create Login in SQL Server to find out how to create a login. 

How can I find out how many logins and when they were built in a SQL Server database?
Several SQL Server queries that return information about logins are mentioned below. You may use the following question to determine the number of logins on your SQL Server:

SELECT COUNT(*)
  FROM master..syslogins
GO

Running the above query in SQL Server Management Studio : 

To get a list of logins on your server as well as the date they were created, run the following query:

SELECT name, createdate
  FROM master..syslogins
GO

Running the above query in SQL Server Management Studio : 

Similarly, you can use the following SQL queries to get the number of users in a database, a list of user names, and the dates the users were created:

USE <database_name>
GO
SELECT COUNT(*)
  FROM sysusers
GO
SELECT name, createdate
  FROM sysusers
GO

The above-mentioned queries should provide you with the required username and user details.

Example – 

USE SQL_DBA
GO
SELECT COUNT(*)
FROM sysusers
SELECT name, createdate
FROM sysusers

Running the above query in SQL Server Management Studio : 


Last Updated : 11 Jun, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads