77 votes
Accepted

Is it still best practice to avoid using the default ports for SQL Server?

Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice. Which was asinine then and still asinine now. Security through ...
Sean Gallardy's user avatar
73 votes
Accepted

DBA first day in a new job - check backups and security - How? what else should be checked?

I wrote the free (and open source) sp_Blitz for this exact reason. People kept handing me SQL Servers and going, "You're the DBA, you manage this thing." I needed something that could quickly analyze ...
Brent Ozar's user avatar
38 votes
Accepted

SQL Server injection - how much damage in 26 chars?

Easy: GRANT EXECUTE TO LowlyDBA Or, I guess in this case it'd be grant execute to lowlydba Take your pick of variations on this. In all likelihood you may be able to test this now against your ...
LowlyDBA - John M's user avatar
27 votes
Accepted

Why storing users passwords at all?

Because for many applications, they don't want individual user accounts connecting to the database. The users/passwords/rights/permissions is all handled at the application layer, and a single ...
BradC's user avatar
  • 9,964
22 votes

SQL Server injection - how much damage in 26 chars?

The SHUTDOWN command or KILL Command (pick a random number over 50) both take significantly less than 26 characters, though the account executing the application queries hopefully doesn't have ...
Martin Smith's user avatar
  • 84.7k
22 votes

Can ransomware embed itself in to a SQL backup file?

Never say never, but since backups aren't executable files and contain no directly executable code (they're about the data, not the SQL Server software itself) I would think the risk is very, very low....
pmdba's user avatar
  • 3,104
21 votes
Accepted

SQL Server : which role grants permission to execute all stored procedures?

If you are using schemas other than the default dbo schema, create a database role per schema and grant EXECUTE on the schema to the role. e.g. For the default dbo schema: CREATE ROLE role_exec_dbo ...
Richard Armstrong-Finnerty's user avatar
18 votes

Determine whether mixed mode auth is enabled without logging in?

You can check it in the registry: HKLM\Software\Microsoft\Microsoft SQL Server\ [instancename] \MSSqlServer The current mode is recorded in the LoginMode key. Values (DWORD) can be: 1 = Windows ...
Julien Vavasseur's user avatar
15 votes
Accepted

Why is PL/Python untrusted?

It's to do with Python's object model - there's always a way to get a reference to objects that could be unsafe. See the rexec module documentation and the restricted execution chapter of the docs for ...
Craig Ringer's user avatar
  • 56.4k
15 votes
Accepted

Determine whether mixed mode auth is enabled without logging in?

Another approach is to try logging in, using SQL authentication, with an obviously fake account that won't succeed. You can then use notepad to open up the ERRORLOG file in C:\Program Files\Microsoft ...
Aaron Bertrand's user avatar
15 votes
Accepted

Why does user not have access to database?

It could be that the database was originally restored from a different server, which also has a ls_readonly user. I guess then that the user identification is not based on the username? Normally, ...
sepupic's user avatar
  • 11k
15 votes
Accepted

What function quotes an identifier in dynamic-sql with SQL Server?

The function that you're looking for is QUOTENAME! Through the practical use of square bracket technology, you can safely encapsulate strings to aid in the prevention of hot SQL injection attacks. ...
Erik Darling's user avatar
  • 40.8k
15 votes
Accepted

Does changing "sa" password require a SQL restart (in mixed mode)?

Q1: Does changing the sa password require a SQL restart? No, but changing the authentication mode does. Since you're just changing the password and the authentication mode is already set to mixed, ...
Sean Gallardy's user avatar
15 votes
Accepted

How to deny writes in all SQL Server databases (including new ones & restored ones)

If you could live with a finite time (minimum 10-seconds) between executions to catch new/restored databases, you could create a scheduled SQL Server Agent Job and put something like this in a T-SQL ...
Rob Dalzell's user avatar
15 votes
Accepted

Is this SQL Procedure "injection proof"?

SQL injection requires that the malicious content be added to (injected into) an SQL query string, which is then parsed by the database engine and executed. As a result, anything that looks like a ...
mustaccio's user avatar
  • 26k
14 votes

SQL Server injection - how much damage in 26 chars?

You could create a table that you then fill up until the end of time or disk space runs out whichever comes first. declare @S char(26); set @S = 'create table t(c char(99))'; exec (@S); set @S = '...
Mikael Eriksson's user avatar
14 votes
Accepted

Should I drop dbo from the database role db_owner?

Don't worry about it. In fact, you cannot drop dbo from db_owner. There's a hard-wired blocker in the engine prohibiting you to do so. Try and you get the error: Msg 15405, Level 16, State 1, Line 1 ...
Tibor Karaszi's user avatar
13 votes

Create table permission for a user in specific schema

I want to give Create , alter and drop permission to user A on a schema1 of a database The safe way to do this is to make A owner of that Schema. Granting a user the ability to alter another user'...
David Browne - Microsoft's user avatar
13 votes
Accepted

Logon failed for login due to trigger execution

According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error. You might have previously had a logon trigger that evaluated if you were ...
John K. N.'s user avatar
  • 17.8k
13 votes

Is it still best practice to avoid using the default ports for SQL Server?

Even though security through obscurity isn't actual security I won't say there aren't any cases where it helps. If an attacker wants to know where your service is listening they can easily find out, ...
Tom V's user avatar
  • 15.7k
12 votes

Access view based on table in another database without account in that other database

This is easy to accomplish in a very secure way using Module Signing. This will be similar to the following two answers of mine, also here on DBA.StackExchange, that give examples of doing just this: ...
Solomon Rutzky's user avatar
12 votes

Can ransomware embed itself in to a SQL backup file?

What you typically have to worry about is ransomware finding your backups on the network and encrypting them. When ransomware hits your network, it typically propagates quickly, and locks down ...
Brendan McCaffrey's user avatar
11 votes
Accepted

Preventing SQL injection in dynamic SQL

In your example code, you are passing three categories of "things" into your dynamic SQL. You pass @OrderDir, which is a keyword to signify ASC or DESC. You pass @OrderBy, which is a column name (or ...
AMtwo's user avatar
  • 16.2k
11 votes
Accepted

Minimum permissions required to run sp_Blitz

Yes, it requires sa permissions. But since it's a stored procedure, you can get permissions via a certificate See the section How to Grant Permissions to Non-DBAs USE master; GO CREATE CERTIFICATE ...
Erik Darling's user avatar
  • 40.8k
11 votes
Accepted

Locked my sa user out

There are two ways to handle this. One option is to login to the windows machine as a local administrator, start SQL Server in single user mode (-m startup switch) and you will now be able to get ...
Tibor Karaszi's user avatar
11 votes

Is the data exchange on connection from client to server sent as plain text over the wire?

are the sql authentication credentials exposed over the wire? No, login traffic is always encrypted over the wire. From this documentation page: SQL Server always encrypts network packets associated ...
Dan Guzman's user avatar
  • 28.2k
11 votes
Accepted

SQL Server with multiple databases (one per client) - what is the best security practice in terms of logins/users/permissions?

Should we create a separate login for each client and have the application connect using separate login credentials? Short answer: Yes. You've segregated each client's data into a separate database, ...
Phill  W.'s user avatar
  • 8,741
10 votes
Accepted

list of PostgreSQL trusted languages?

Is it generally true that any PL can be both set up to be trusted and untrusted? No. Any PL can be marked as trusted or untrusted, but if you park a PL that doesn't support running trusted as trusted,...
Craig Ringer's user avatar
  • 56.4k
10 votes
Accepted

Giving Special Permissions to a Stored Procedure in SQL Server

This is fairly easy to accomplish (including handling multiple databases and Dynamic SQL) without any Impersonation (IMPERSONATE permission), cross-database ownership chaining (Server / Instance or ...
Solomon Rutzky's user avatar
10 votes
Accepted

Can the account name of 'distributor_admin' be changed?

No it can't be renamed. The good news is that the account only exists on the distributor, which doesn't really contain any user data. You can safely disable the account when you aren't setting up ...
mrdenny's user avatar
  • 27k

Only top scored, non community-wiki answers of a minimum length are eligible