What you Will Learn
What server principals are
What types of server principals there are
What schema to query to access the "principals" list!
The Low Down on SQL Server Security
An average Windows Joe needs to know something about databases, and SQL Server databases in particular. But you can't claim to be a database know-it-all without knowing "un peu" of SQL database security, in particular the concepts of Principals and Securables.
A great book to accelerate your learning about SQL Server Security is "SQL Server (2008 R2) Unleashed" from SAMS Publishing (famous for their "Unleashed" series and "How To" books). The concepts are well-presented and can save you time before diving into specific details strewn across the MSDN jungle.
Speaking of concepts, let's introduce the most basic ones; requestors of resources, resources and permissions. Turns out, these concepts have a rather different terminology in the SQL Server worldspace.
1. A Requestor of a SQL Server Resource is henceforth known as a Principal. These principals may be Windows users, SQL Server users and so forth.
2. A SQL Server resource is henceforth known as a Securable.
3. Permissions link Principals with Securables.
But you can't know-it-all about SQL Server databases without having some tools to practice with. For this, the SQL Server 2008 R2 Management Studio is an excellent choice.
Some simple queries to get you "synced" with the SQL Serve Security spirit:
1. select * from sys.server_principals
This so-called "catalog view" in SQL Server (nothing more than a "window on metadata") identifies all "server-level" principals and what type they are; an example would be the "sa" account which has type "SQL_LOGIN" (and incidentally cannot be removed). Other types include WINDOWS_LOGIN (self explanatory!), WINDOWS_GROUP and SERVER_ROLE. All are types of PRINCIPALS in a SQL Server database.
However, there is another, less documented type of "server-level" principal, the CERTIFICATE_MAPPED_LOGIN. Some systems use digital certificates as an additional means of authenticating users.
What server principals are
What types of server principals there are
What schema to query to access the "principals" list!
The Low Down on SQL Server Security
An average Windows Joe needs to know something about databases, and SQL Server databases in particular. But you can't claim to be a database know-it-all without knowing "un peu" of SQL database security, in particular the concepts of Principals and Securables.
A great book to accelerate your learning about SQL Server Security is "SQL Server (2008 R2) Unleashed" from SAMS Publishing (famous for their "Unleashed" series and "How To" books). The concepts are well-presented and can save you time before diving into specific details strewn across the MSDN jungle.
Speaking of concepts, let's introduce the most basic ones; requestors of resources, resources and permissions. Turns out, these concepts have a rather different terminology in the SQL Server worldspace.
1. A Requestor of a SQL Server Resource is henceforth known as a Principal. These principals may be Windows users, SQL Server users and so forth.
2. A SQL Server resource is henceforth known as a Securable.
3. Permissions link Principals with Securables.
But you can't know-it-all about SQL Server databases without having some tools to practice with. For this, the SQL Server 2008 R2 Management Studio is an excellent choice.
Some simple queries to get you "synced" with the SQL Serve Security spirit:
1. select * from sys.server_principals
This so-called "catalog view" in SQL Server (nothing more than a "window on metadata") identifies all "server-level" principals and what type they are; an example would be the "sa" account which has type "SQL_LOGIN" (and incidentally cannot be removed). Other types include WINDOWS_LOGIN (self explanatory!), WINDOWS_GROUP and SERVER_ROLE. All are types of PRINCIPALS in a SQL Server database.
However, there is another, less documented type of "server-level" principal, the CERTIFICATE_MAPPED_LOGIN. Some systems use digital certificates as an additional means of authenticating users.
No comments:
Post a Comment