SQL Server has a HAS_DBACCESS() function that returns information about whether the user has access to a specified database.
Tag: security
Get the Number of Failed Login Attempts for a Login due to a Wrong Password in SQL Server (T-SQL)
In SQL Server, you can use the LOGINPROPERTY() to return information about login policy settings.
This includes being able to return data for bad password attempts, as well as the time of the last failed login attempt due to a bad password.
Continue readingChange a Login’s Default Language in SQL Server
In SQL Server, you can use the ALTER LOGIN statement to change the default language for a specific login.
Doing this will ensure that whenever that login connects to SQL Server, all date/time formats and system messages are presented in the correct format and language.
Continue readingGet the Current Login ID in SQL Server (T-SQL)
You can use the SUSER_ID() function to return the login identification number of the current user.
You can also use it to return the login ID of another user.
This is similar to returning the current login name, except here we’re returning the ID instead.
Continue reading2 Ways to Get the Default Language of a Login in SQL Server (T-SQL)
There are (at least) a couple of ways you can use T-SQL to return the default language of a specified login in SQL Server.
In particular, you can use the following methods:
- Query the
LOGINPROPERTY()function. - Query the
sys.server_principalssystem catalog view in themasterdatabase.
Examples of these are below.
Continue readingChange the Password for a SQL Server Login
This article demonstrates how to use T-SQL to change the password for a SQL Server login.
You can change the password for another login (assuming you have the appropriate permissions), or you can change it for your own login. This article outlines both of these scenarios.
Continue readingReturn the Current Login Name in SQL Server (T-SQL)
You can use the SUSER_NAME() function to see the login name that you’re currently using to access SQL Server.
This function returns returns the login identification name of the user. It also allows you to get the login name of any other user, based on their login identification number.
Continue readingChange the Password on the SA Login in SQL Server (T-SQL Example)
Below is an example of the T-SQL code you can use to change the password of the sa login account in SQL Server.
The sa account’s password can be changed with the ALTER LOGIN statement.
Rename the SA Account in SQL Server (T-SQL Example)
A common security precaution you should consider when administering SQL Server is to rename the sa login.
In addition to renaming this account, you should also disable it altogether and then create other admin accounts to be used instead of sa.
The sa account is widely known as “THE” admin account and therefore it’s a perfect target for hackers. Disabling this login prevents hackers from being able to use it to hack your system, and renaming the account can provide an extra layer of protection.
Disable the SA Account in SQL Server (T-SQL Example)
The sa login is the most famous login account in SQL Server. Every SQL Server administrator knows about it (if they don’t, they should).
Due to its administrative nature, any compromise of the sa account could have catastrophic consequences, including the complete loss of control over SQL Server.
It is therefore widely considered good security practice to disable the sa account altogether. And as an extra precaution, rename the account and change its password. Other admin accounts can be created as required.