Frequently used Security Functions

SQL Server documentation specified the following security functions.

• CERTENCODED
• PWDCOMPARE
• CERTPRIVATEKEY
• PWDENCRYPT
• CURRENT_USER
• SCHEMA_ID
• DATABASE_PRINCIPAL_ID
• SCHEMA_NAME
• sys.fn_builtin_permissions
• SESSION_USER
• sys.fn_get_audit_file
• SUSER_ID
• sys.fn_my_permissions
• SUSER_SID
• HAS_PERMS_BY_NAME
• SUSER_SNAME
• IS_MEMBER
• SYSTEM_USER
• IS_ROLEMEMBER
• SUSER_NAME
• IS_SRVROLEMEMBER
• USER_ID
• LOGINPROPERTY
• USER_NAME
• ORIGINAL_LOGIN
• PERMISSIONS

Few of them are widely used in administration tasks.

The below command will help in identifying the service name
SELECT @@SERVICENAME

The below command will help in identifying the server name (if local instance then it will display the machine name)
SELECT @@SERVERNAME

The below displays the version along with its patch and the copyright information.
SELECT @@VERSION

The below displays the schema name.
SELECT SCHEMA_NAME();

The below displays the current user.
SELECT CURRENT_USER

The below displays the current user.
SELECT USER_NAME (1)

The below displays the sysadmin user.
SELECT SUSER_NAME(1);

The below displays the current user.
DECLARE @SessionUser NVARCHAR(20)
SET @SessionUser= SESSION_USER
SELECT @SessionUser

The below displays the actual user who logged into the system
DECLARE @sys_usr CHAR(30);
SET @sys_usr = SYSTEM_USER;
SELECT @sys_usr;
GO

Hope you find this article helpful.

Happy learning!!