{"id":113198,"date":"2026-09-22T11:51:29","date_gmt":"2026-09-22T11:51:29","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?post_type=collection&p=113198"},"modified":"2026-09-22T13:12:00","modified_gmt":"2026-09-22T13:12:00","slug":"sql-server-security-basics-complete-guide","status":"publish","type":"collection","link":"https:\/\/www.red-gate.com\/simple-talk\/collections\/sql-server-security-basics-complete-guide\/","title":{"rendered":"SQL Server security basics (complete guide)"},"content":{"rendered":"\n

SQL Server security spans seven connected areas: authentication and the principals\/permissions model that controls access, contained databases for portability, built-in accounts like sa<\/code> and dbo<\/code>, data encryption, the catalog views and functions used to audit security state, and tools like SQL Vulnerability Assessment for finding weaknesses.<\/strong> <\/p>\n\n\n\n

This guide collects Robert Sheldon’s series on all seven, each with working T-SQL examples, in the order below \u2014 start with the overview if you’re new to SQL Server security, or jump to the topic you need.<\/p>\n\n\n\n

These guides were originally written and published in 2018\/2019.<\/em><\/p>\n\n\n\n

\n
\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tSQL Server<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Robert Sheldon<\/span>\n <\/a>\n\n \n in<\/span>\n \n SQL Server <\/a>\n <\/span>\n<\/div>\n\n
\n

\n SQL Server Security Tools: Vulnerability Assessment & Config <\/h1>\n

\n How to use SQL Server Configuration Manager, sp_configure, Vulnerability Assessment, and Data Discovery & Classification to harden an instance.… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tSQL Server<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Robert Sheldon<\/span>\n <\/a>\n\n \n in<\/span>\n \n SQL Server <\/a>\n <\/span>\n<\/div>\n\n
\n

\n SQL Server Security Catalog Views, DMVs & Functions <\/h1>\n

\n The key SQL Server catalog views, DMVs, and system functions for auditing permissions, encryption, and logins \u2014 with T-SQL query… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tSQL Server<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Robert Sheldon<\/span>\n <\/a>\n\n \n in<\/span>\n \n SQL Server <\/a>\n <\/span>\n<\/div>\n\n
\n

\n SQL Server Built-In Accounts: sa, dbo, guest & sys <\/h1>\n

\n What SQL Server’s built-in principals (sa, public, dbo, guest, sys, INFORMATION_SCHEMA) do by default, and how to check or change… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tDatabases<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Robert Sheldon<\/span>\n <\/a>\n\n \n in<\/span>\n \n Databases <\/a>\n <\/span>\n<\/div>\n\n
\n

\n SQL Server Contained Databases: Users & Portability <\/h1>\n

\n How SQL Server’s partially contained databases work \u2014 creating contained users, duplicating them across databases, and unlinking users from logins.… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tSQL Server<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Robert Sheldon<\/span>\n <\/a>\n\n \n in<\/span>\n \n SQL Server <\/a>\n <\/span>\n<\/div>\n\n
\n

\n SQL Server Access Control: Logins, Roles & Permissions <\/h1>\n

\n How to create SQL Server logins, roles, and database users, then grant, deny, and revoke permissions on them with T-SQL… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tSQL Server<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Robert Sheldon<\/span>\n <\/a>\n\n \n in<\/span>\n \n SQL Server <\/a>\n <\/span>\n<\/div>\n\n
\n

\n SQL Server Security Overview: Authentication to Encryption <\/h1>\n

\n An overview of SQL Server’s security toolkit \u2014 authentication modes, principals\/permissions, encryption, auditing, and vulnerability tools.… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tSQL Server<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Robert Sheldon<\/span>\n <\/a>\n\n \n in<\/span>\n \n SQL Server <\/a>\n <\/span>\n<\/div>\n\n
\n

\n SQL Server Permissions: Principals, Securables & Roles <\/h1>\n

\n How SQL Server principals, securables, and permissions work together \u2014 logins, users, roles, and GRANT\/DENY\/REVOKE \u2014 with T-SQL examples.… <\/p>\n <\/div>\n

\n