Security

I have a SQL Server login that's been locked out. I went to unlock it, but it's telling me I have to change the password to do so. I can't change the password and I don't know what it is. How can I unlock the account without changing the password? Check

I've recently been asked to identify and report on any SQL Servers listening on my network. I need to try and find all SQL Servers, not just the ones in my domain. We know there are a couple of appliances that are potentially running SQL Server and we wan

Recently I was supporting a third party application. It queries to determine what tables it has permissions to before it proceeds with the rest of its functionality. We had implemented permissions based on the best practice of creating roles, assigning th

Ever get tired of assigning server level permissions by adding a login to a predefined server fixed role and then assigning multiple permissions to that login? In SQL Server 2012 you now have the ability to create a User Defined Server Level Role.

By default, SQL Server does not keep track of login password changes. When the question initially came up with a user, I thought that perhaps it might be in the default trace or in the system_health extended event session. No such luck. So I was in search

My application doesn't use the dbo schema and I need to set the users coming into the database to use the correct schema automatically. We're using Windows groups and whenever I go to set a default schema, SQL Server gives me an error. How can I set the d

Recently I was trying to find out how someone had access to a particular database in SQL Server. When I looked at the list of users at the database level, I didn't find any that matched up either with the user's Windows account or with any of the Windows

Recently I experienced a situation where a SQL Server login failed with this error message: Error: 18456, Severity: 14, State: 10. Login failed for user 'SQL_Login'. Reason: Password validation failed with an infrastructure error. Check for previous e

Suppose you are a DBA responsible for managing all of the SQL Server instances in your environment. For the sake of security you lock down your environment by removing the BUILTIN Administrators group, disable sa the login, change the port number, removed

My network admins have tightened down the network and while we were once able to connect to our named instances via MySQLServer MyNamedInstance, we're now only able to connect if we specify the port. What happened and what do I need to tell them to fix th

Someone, while locking down the SQL Server, removed the permissions by which the DBAs came in and administered the server. As a result, we cannot get back into SQL Server. How can we restore our access to SQL Server? Check out this tip to find out.

This tip will take a look at the performance impact of implementing SQL Server Transparent Data Encryption using a few basic database operations.

Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to drop the user and it failed with the below error messages: Msg 15421, Level 16, State 1, Line 1 The database principal owns a database role and cannot be dropp

I've been tasked with securing my SQL Servers and I need to write scripts to audit them periodically to ensure they are all right. Recently I learned that I need to audit for CONTROL SERVER rights on any SQL Servers I have that are 2005 or above. In this

In a recent MSSQLTips.com question, a user asked how they could get an alert whenever a login failed due to the account being locked out. In this tip I provide different options to handle this need.

How do you grant a SQL Server developer truncate table permission without giving them the ability to alter the table? Check out the examples and explanation in this tip.

I need to create a stored procedure that queries data that are not in the current database. What are my options?

I need to keep some old SQL data and log files in a backup folder for DR purposes. What is the best way to protect these files? In this tip we look at using TDE to safeguard your files and the steps necessary to move a TDE database to another SQL Server

I'm looking to install SQL Server and I know it will require a service account. In this tip we cover what should be done to configure the SQL Server service accounts securely.

This tip includes a script that will identify and move all objects to a new SQL Server schema.

While looking through the new features and improvements in SQL Server Denali, we found a potentially interesting feature which can be used to Create a User Defined Server Level Role. This is a very interesting feature as this helps DBAs to Create a User D

This tip shows how to identify who the local administrators are on each SQL Servers. This uses PowerShell and an input file to determine which SQL Servers to check.

I'm trying to set up SQL Server and I know that I'm not supposed to use an account that's an administrator on the system, as per best practices. I see that the System account is a possibility, but I've been advised not to use it. Why not? Check out this

I'm a developer and in a given database I can't see a table I know is there. The reason I know it's there is I can execute a stored procedure which queries the table and I never get an error executing it. What's going on?