SQL Security:
Login: Those who are want to privileges for login into SQL server called login.
Windows Authentication mode:
It allows only active directory users or windows users to connect and work with SQL Server with mapping logins. It provides high security.
SQL login:
It allows non active directory users to connect and work with SQL Server. It provides less security. Both login and password of SQL Server is maintained by SQL Server itself only.
Difference between Login and User:
Login:
- It can be created at server
- It is used to authenticate with the
- One login can be associated with many db’s on the server (one per database).
User:
- User can be created at database
- It can be used to authenticate with
- One user per database which are mapped to corresponding
Built-in administrator: Windows operating system (OS),the built-in administrator account is
the first account created when the operating system is installed.
Service Accounts: A service account is a user account that is created explicitly to provide a security context for services running on Microsoft® Windows® Server
Domain Account: Windows domain is a form of a computer network in which all user accounts, computers, printers and other security principals, are registered with a central database (called Active directory ) located on one or cluster of central computers known as domain controllers.
Active Directory (AD): Is a directory service that Microsoft developed for Windows domain networks. It is included in most Windows Server operating systems as a set of processes and services. Initially, Active Directory was only in charge of centralized domain management.
System Administrator (sa):By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely.
Server roles:-
Server role to perform server side operations we have 8 server roles and by default it will be public role…
Server roles provides the permissions at the server level to the logins.
Sys admin:-Top most privilege on the server, capable of doing any thing on the server…
Server admin:-Allows users to manage configurations on the server capable of start and stop services from configuration manager, server settings, memory configurations and shutdown of sql server…
Setup admin:-Setup admin capable of creating linked server. He can run any setup(utilities) like replication, mirroring…
Security admin:-Capable of creating new logins, delete logins and reset passwords.. he has ability to grant, revoke and deny permissions..
Process admin:-Monitors the process running under taskbar.. capable of killing problematic process…
Disk admin:-Manages storage.. if any new drive need to add, he has permission to add drive to the server..
Bulk admin:-Capable to performing bulk insert and update…
Db creator:-This role is capable of creating database. Taking backup and restore….
Granting of server level roles: sp_addsrvrolemember ‘<login name>’,'<server role>’.
Using GUI:-
Right click on login – go to properties – server roles – select role
To remove the granted role:
sp_dropsrvrolemember ‘<login name>’,'<server role>’
Database role :-
Database role is to perform database side operations we have 9 database roles and by default it will be public role…
Db_owner:-Dbowner is capable of doing any thing on particular database…
Db_security admin:-He is responsible for creating/deleting/modifying roles to users and manages permissions…
Db_ddladmin:-He can able to create/alter/drop the table and other objects..
Db_accessadmin:-He can not able to create (or) modify any user role. He can grant only roles and remove access to other users..
Db_datareader:-Can perform read operations means he can run select queries on all objects…
Db_datawriter:-Can perform write operations means he can perform insert/update/delete data in database
Db_denydatareader:-Cannot perform the read operations..
Db_denydatawritter:-Cannot perform the write operations
Db_backupoperator:-He can able to take backup of database…
Granting of database roles:-
sp_addrolemember ‘<dbrole name>’,'<user name>’
Right click on login — go to properties — go to user mapping — select database and grant database role
To remove the database role:-
sp_droprolemember ‘<dbrole name>’,'<user name>’
Orphan user how to resolve it.:
The user accounts that do not have their corresponding Login accounts in the destination server are called Orphaned users. While migrating a DB as a part of backup, the user accounts will be migrated but corresponding Logins will not be migrated, since Logins are stored in master DB. User account with out corresponding login account those users we called as Orphan users.
sp_change_users_login ‘report’ — to find orphan users
sp_change_users_login ‘UPDATE_ONE’,’username’,’loginname’ — to fix orphan users
To see the list of the orphan users:-sp_change_users_login @action=’report’ To fix orphan user:-
EXEC sp_change_users_login ‘Auto_Fix’, ‘user’
Sp_validatelogins
I want to give the permission to an object:
Go to users under database — go to properties — go to securables — select objects from search — object types provides list of objects tables,view etc…select them — provide permissions and click ok…
Subbu-SQL Server DBA
I want to run the job , what are the permissions are required:
Go to Logins — properties — User Mappings — MSDB
- Agent-user Role
- Agent-reader Role
- Agent-Operator Role
How to give the read permissions to the user:
Go to — db — security — users — properties — owner schema — give db_datareader
I want run the stored procedure what are the permissions are required:
Go to — db — security — users — properties — securable — stored procedure.
What are the permissions are required to perform the backup operation:
Go to Logins — properties — User Mappings — MSDB
- Agent-user Role
- Agent-reader Role
- Agent-Operator Role
What are the permissions required for creating the db:
Go to logins — properties — server roles — dbcreator
How to move logins from one server to another server:
Sp_help_revlogin