SQL Server Security Authorization Concept

 

Authorization:

• Once user connected to server,they should have permissions to perform/operating certain tasks.
• As a DBA we need to restrict the permissions by granting the roles,roles are set of privilages for connection server and to access the database.

       We have 2 types of roles.

1. Server role
2. Database role

1.Server role:

server role to perform server side operatios.

We have 8 server roles and by default it will be public role.

      1.sysadmin:

• Top most privilage on the server,capable of doing any thing on the server.DBA'S will have this role.

      2.serveradmin:

• Allows users to manage configurations on the server.
• capable of start and stop services from configuration manager,server settings,memory configurations and shutdown of SQLServer.

     3.setupadmin:

• setupadmin capable of creating linked server.
• He can run setup(utilities) like replication,mirroring.

     4.securityadmin:

• Capable of creating new logins,delete logins and reset passwords.
• He has ability to grant,revoke and deny permissions.

     5.processadmin:

• Monitors the process running under taskbar,capable of killing problematic process.

     6.bulkadmin:

• Capable to performing bulk inserts and updates.

     7.diskadmin:

•  Manage disk files.

      8.DBcreator:

• This role is capable of creating databases.
• He can alter and drop.
• Taking backups and restore.

Using T-SQL:

     sp_addsrvrolemember 'loginname' 'privilage'

     ex:sp_addsrvrolemember 'ravi' 'sysadmin'

Using GUI:

   Right click on login-->New login-->click on server roles-->check certain privilage-->ok.

coolpad

2.Database roles:

      1.db_owner:

• Dbowner is capable of doing any thing on particular database.
• Highest privilage at database level.

       2.db_securityadmin:

• He is responsiable for creating/deleting/modifying roles to users and manages permissions.

       3.db_datareader:

• He can run select queries on all tables,only read activities.

       4.db_datawriter:

• He can perform Insert/update/delete data in the database.

        5.db_denydatareader:

• To restrict the read access on particular confidential data even through user has server level (or) high privilages at db level.
• We can prevent that user in accessing particular database using deny role.

        6.db_denydatawriter:

• He can not able to write data on restricted databases.

        7.db_ddladmin:

• HE can able to create/alter/drop the tables and other objects.

         8.db_accessadmin:

• He can not able to create (or) modify any user role.
• He can grant only roles and remove access to other users.

         9.backupoperator:

• He can able to take backup of databases.

         10.public:

• This is default role on database level.

Using T-SQL command:

         sp_addrolemember 'username' 'rolename'

         ex:sp_addrolemember 'testadmin' 'db_datawriter'

Using GUI:

    Right click on login-->go to properties-->go to user mapping-->select database and grant database role.

NOTE:SQL Server2012 introduces contained databases,there are 2 types of Authentications in this

1. Contained databases users with passwords.Stores all the passwords with in contained database.
2. Contained databases window is similar to windows login.

3.Object level:

  We need to identify 3 things in providing object level access.

1. Principals are login accounts to whom we are granting permissions.login accounts and user account comes under principal.
2. Privilages, how we granting permissions to access objects.
3. Securables,on which object we are granting tables,stored procedures and views.

Through T-SQL::

        grant/revoke permissions on securables to principal

        ex:grant insert on dbo.sqltest to empadmin

Through users:

        Go to users under database-->go to properties-->go to securables-->select objects search-->object types provides list of objects tables,view etc.

select them-->provide permissions and click ok.