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!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s