SQL Server Security Part I

SQL Server has ways of accessing, we will understand one by one. Access to SQL Server can be provided using Authentication and Authorization.

Authentication is a process of logging into SQL Server using login credentials. 

Authorization is a process of determining resource access for the login.


SQL Server supports two authentication modes 1) Windows authentication mode and 2) mixed mode.

Windows Authentication is the default mode in SQL Server, whoever has access to windows access those directly can connect to SQL Server without specifying credentials again because SQL Security model is tightly integrated with Windows.

Windows account has different types, Local windows account, Domain Account, Domain Group.
Local windows account is defined account in the SQL Server machine.
Domain account is individual account defined at Active directory level
Domain group is defined at Active directory which contains multiple domain accounts.

SQL Server Authentication - The SQL Server account is account defined within the SQL Server, it is unknown to Active directory, and while creating account at SQL Server level we need to provide login name and password for that account. This is the difference between Windows and SQL account, windows account password will be maintained by Active directory and SQL account password will maintained by SQL Server.

SQL Server uses role-based security for assigning permissions to users. There are fixed server roles and fixed database roles, Server roles will be defined at server level and database roles will be defined at database level.

SYSADMIN server level role is the super user in SQL Server it has every right on SQL Server.

We need to understand about database roles and default user accounts in the database. In both user and system databases there will be public role by default, we cannot remove this role from the database, and permissions which are assigned to public role will get inherited to all other users in the database. 

The DBO user account

The dbo or database owner has all permissions on the database. Users of sysadmin are automatically mapped to dbo.

The Guest user account

 This is a default account gets created automatically when new database is created and it was in disable state, If you enable this account then whoever has access on the server can able to access the database, (of course they are not able to view any information but if guest is assigned with any database role like db_owner those permissions will automatically inherited to all other logins on the server). So we should not enable guest account.

To enable guest account

Grant connect to guest;

To disable guest account

Revoke connect from guest;