Users and logins in MSSQL

Login - grants access to the server

User grants a login access to the database List users in SQL Server database

Query

select sp.name as login,
       sp.type_desc as login_type,
       sl.password_hash,
       sp.create_date,
       sp.modify_date,
       case when sp.is_disabled = 1 then 'Disabled'
            else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
          on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R')
order by sp.name;

Columns

• login - user name
• login_type - principal type:
  • SQL_LOGIN - SQL login
  • WINDOWS_LOGIN - Windows login
  • CERTIFICATE_MAPPED_LOGIN - Login mapped to a certificate
  • ASYMMETRIC_KEY_MAPPED_LOGIN - Login mapped to an asymmetric key
• password_hash - for SQL logins hashed password with SHA-512
• create_date - date the login was added
• modify_date - date the login was last updated
• status - status of the login
  • Enabled
  • Disabled

Rows

• One row represents one user in the database
• Scope of rows: all users in the database
• Ordered by user name

Query

select name as username,
       create_date,
       modify_date,
       type_desc as type,
       authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
      and sid is not null
      and name != 'guest'
order by username;

Columns

• username - user name
• create_date - date the account was added
• modify_date - date the account was last updated
• type_desc - principal type:
  • CERTIFICATE_MAPPED_USER - User mapped to a certificate
  • EXTERNAL_USER - External user from Azure Active Directory
  • ASYMMETRIC_KEY_MAPPED_USER - User mapped to an asymmetric key
  • SQL_USER - SQL user
  • WINDOWS_USER - Windows user
• authentication_type - type of user authentication
  • NONE : No authentication
  • INSTANCE : Instance authentication
  • DATABASE : Database authentication
  • WINDOWS : Windows Authentication

Rows

• One row represents one user in the database
• Scope of rows: all users in the database
• Ordered by user name

Change SQL Server Password Using SQL Script

1. Open the SQL Server Management Studio.
2. Open a New Query.

3. Copy, paste, and execute the following:

   GO
   ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
   GO
   USE [master]
   GO
   ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword' MUST_CHANGE
   GO

   where NewPassword is the password you wish to use for the sa account.

Check Disk Space Usage on CentOS

Most commonly used commands for checking disk spaces or usage or free spaces are df, du and free. 

df command is used to get the details disk spaces information of the file system. DF - stands for disk filesystem.

–help is the common option for any command to get all information for using the command.

-h option is used with df command to display the disk space in human readable format. Disk space value will be shown in GB and MB.

-m option is used with df command to show the disk space in MB.

-T option is used with df command to show the file type.

You can show the disk space information of any particular folder in human readable format by using above command. The output will show the disk space information of Public folder.

You can use two options -hT together to get the file types in human readable format.

Another useful command to find out the usage information is all files and folders. Here, du stands for disk usage. This command retrieves information of folders and sub-folders and files recursively. So, this command can be used to get more detail information of disk usage. 

The output will show the size information of files and folders in more human readable format.

The output will show the size of Downloads folder.

The output will show the size of Downloads folder with last modification time.

The output will show the size of Downloads folder in MB.

free command is used to get the detail used and unused information of the computer memory and swap. 

The output shows the memory and swap usage information in byes.

The output shows the memory and swap usage information in GB and MB.

The output shows the memory and swap usage information in MB.

One of the mostly used built-in tool to monitor the partition of the disk is fdisk. Using this tool you can not only monitor the disk partition but also create, move, copy and delete disk partitions. You must have root privilege to run this command. 

-l option is used to get information about all available partitions on your operating system. You have to provide root password to run this command.

You have to mention the device name with –l and fdisk command for getting the information of specific partition or device.

You can print the output of any device by executing fdisk command with device name. When you will run the command, a prompt will appear to take the next command if the device name exists. Type ‘p’ to print the output.