69

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 arguably obscurity isn't security at all. Is this advice still relevant IMHO it was never relevant. It was required for some compliance purposes because the ...


64

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 stuff like: Databases that hadn't been backed up or checked for corruption Unsupported builds of SQL Server Dangerous trace flags and database settings Bad ...


39

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 current system, but any number of small changes in the database over time could invalidate your testing. The character string could change, someone could create ...


38

This code works properly because it is: Parameterized, and Not doing any Dynamic SQL In order for SQL Injection to work, you have to build a query string (which you are not doing) and not translate single apostrophes (') into escaped-apostrophes ('') (those are escaped via the input parameters). In your attempt to pass in a "compromised" value, the 'Male; ...


33

This means that the login [R2Server\AAOUser] is already mapped to a user in that database. Or, in other words, another database user is using this login. You can see what database user is using your login with the following query: use YourDB go SELECT su.name as DatabaseUser FROM sys.sysusers su join sys.syslogins sl on sl.sid = su.sid where sl.name = 'test'...


27

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 dedicated service account is used to connect to the database back-end. As a DBA, I don't want to have to manage, at the database level, the 10,000 active users of ...


26

This is explained in Extending Database Impersonation by Using EXECUTE AS. The EXECUTE AS context is trusted only in the current database and allowing it to spill over to other databases is a escalation of privilege attack vector. There are two solutions, both described in the article linked above: the easy one is to mark the database TRUSTWORTHY: ALTER ...


26

SELECT CAST(CRYPT_GEN_RANDOM(16) AS UNIQUEIDENTIFIER) Should do the trick I would have thought. CRYPT_GEN_RANDOM Returns a cryptographic random number generated by the Crypto API (CAPI).


23

This should get you what you're looking for: ;with objects_cte as ( select o.name, o.type_desc, case when o.principal_id is null then s.principal_id else o.principal_id end as principal_id from sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id where o.is_ms_shipped ...


23

Slightly different approach would be to give the schema back to the original owner/schema, instead of to dbo: ALTER AUTHORIZATION ON SCHEMA::db_owner TO db_owner;


22

You can join on the certificate thumbprint: use master; go select database_name = d.name, dek.encryptor_type, cert_name = c.name from sys.dm_database_encryption_keys dek left join sys.certificates c on dek.encryptor_thumbprint = c.thumbprint inner join sys.databases d on dek.database_id = d.database_id; My sample output: database_name ...


22

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 sufficient permissions to run these.


21

It depends on your business, but the main thing in most cases is to make sure it is not seen as an IT issue. It is a security issue and while the two overlap massively business peopel are more likely to listen if you say "security" than if you are just "moaning about general IT stuff". Do you work with any clients that have security requirements? That is a ...


21

No. While the documentation currently has the following arguably ambiguous statement about what this flag means: Password policy is checked. What it really means, and should say, is that the flag serves two purposes: The password policy might have been checked, but only if (a) the password policy was enabled at the time the password was last set, ...


20

No application needs to have SA access - ever. (Unless its sole purpose is database administration of some kind.) It is a general rule to never grant more rights to any login (application- or personal-) than the business requires that login to have. No application is completely secure. Most have some kind of SQL Injection or XSS vulnerability. If an ...


19

Microsoft has recently revealed (without a lot of fanfare) that they will be investing in TLS 1.2 and phasing out SSL. It should be relevant to all editions of SQL Server. UPDATE 2016-01-29 : Microsoft has announced official support for TLS 1.2 in 2008, 2008 R2, 2012, & 2014. Downloads and other info can be found in KB #3135244. I blogged about a few ...


18

I think you're conflating authentication and authorization. I completely agree that keeping the security model in the DB is wise, especially as LedgerSMB is designed with access from multiple clients in mind. Unless you plan on going 3-tier with a middleware layer it makes perfect sense to have users as database roles, especially for something like an ...


18

USE msdb; GO CREATE USER shims FROM LOGIN shims; GO ALTER ROLE SqlAgentUserRole ADD MEMBER shims; GO Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:


18

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 Authentication mode 2 = SQL Server and Windows Authentication mode Note: 0 is equivalent to 2 You still need access to the Registry (and the server)


16

Please find the below 4 queries and run these queries from system database. //For seeing Full user details SELECT profile FROM dba_users WHERE username = 'SYSTEM'; //This query is used to change the password life time to unlimited ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; //This query is used to chagne the default password. alter user ...


16

Your problem boils down to access control. The first defense I'd propose is to simply deny access to the untrusted users. If they can't get into the database, they can't query the database and get at the sensitive data. If they must be allowed to access the database server, you can look at either explicitly granting them read permission to the tables they ...


16

This variable controls whether binary logging should trust the stored function creators for not to create stored functions that will cause unsafe events. Eg. having UUID functions. This has been explained well in documentation: When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise,...


15

No. If a user is a Windows Administrator of a box, assume that they own everything on the box (including SQL Server). With Windows Administrator rights it is trivial to bypass any targeted protection you apply (such as a logon trigger that identifies their user name), by impersonating someone else (including NT AUTHORITY\SYSTEM, which gets de facto admin ...


15

Please see the reference on BOL for Database-Level Roles: db_owner Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database. The easiest way to see all of the permissions is to use the sys.fn_my_permissions() function. First verify you are a member of ...


15

Here's one way using dynamic SQL. There's not really any way to do this without iterating but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb. This will get a list of all online databases, the mapped user (if it exists) along with the default schema name and a comma-separated list of the roles they belong to. ...


15

This may not be popular among your users, but I believe the only way you can know for sure is to force a password change for every SQL login with CHECK_POLICY = ON. This will generate a set of ALTER LOGIN commands with blank passwords, you can update the query giving them all a common password or manually update each one with individual passwords - just make ...


15

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 GO GRANT EXECUTE ON SCHEMA::dbo to role_exec_dbo GO For a new schema: CREATE SCHEMA mySchema GO CREATE ROLE role_exec_mySchema GO GRANT EXECUTE ON SCHEMA::...


15

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 SQL Server\$instance folder$\MSSQL\Log\ and see either this error... Error: 18456, Severity: 14, State: 58. Login failed for user 'polly_wants_a_cracker'. ...


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