Security

The security category includes three child items: Logins, Server Roles, and Credentials.

There are two types of logins to SQL Server, with respect to authentication. One is managed by Windows (typically within Active Directory, but also local to the server) and the other is managed by SQL Server (mixed mode). Both types are shown in the screenshot above. As a general rule, login names with one word or without slashes are indicative of being MSSQL accounts. The “sa” account (analogous to SYS and SYSTEM) is an example of this type of account.

Administrators get a free account; one of the several built-in groups in Windows is the Administrators group. Other built-in and local, but Windows authenticated, accounts include the NT AUTHORITY accounts. The NT AUTHORITY\SYSTEM account is granted a login along with the built-in Administrator account. It's a common practice is to remove both of these accounts and rely upon AD authentication. If you right-click the account, then go to the Server Roles page to look at the properties, you can see how the SYSTEM account has been granted the sysadmin fixed server role. In Oracle terms, this account has SYS/SYSTEM level privileges).

A common question in Oracle, with respect to licensing, has to do with how many users have access to the database. Letting Oracle manage user security makes the DBA_USERS data dictionary view a fairly valid source of what the count is. With practically all SQL Server logins (from human users) being managed via Active Directory, the Logins folder is not going to be quite as useful, and if a user doesn’t appear under Logins, he won’t appear in a table either. This problem is compounded when DOMAIN\Authenticated Users is created as a login. Any and all domain authenticated users can logon with this login account.

View article

Back to DBAsupport.com