Security

Security

Introduction:

          I would like to discuss here Overview of Implementing Security and most important interview questions in both Server level and Database level security.

In Server level consists of

·        Configuration Network

·        Logins

     -Windows logins

     -SQL Server logins

·        Roles (It consists of diff. roles but top level role is SYSADMIN)

      -Custom Roles

·        Auditing

·        Endpoint

In Data Base level consists of

·        Users

·        Permissions (DB level, Schema level, Object level)

·        Roles(It consists of diff. roles but top level role is DB_OWNER)

·        Auditing

Security Road Map:

Step 1:  Creating Login

            Ex: Create login Arjun with password=’pavan@123’

In Windows level

            Ex: Create login [arjunpavan\Arjun] from windows

Note: Here ‘arjunpavan’ is domain account name and ‘Arjun’ is Windows User

Step 2: Creating User

            Ex: use Adventureworks

                        Go

                        Create user Pavan for login Arjun

Note: Here ‘Adventureworks’ is database name and ‘Pavan’ is User name

Step 3: Granting Permissions

            Ex: Use Adventureworks

                        Go

                        Grant select, insert on person.adress to Pavan  à Object level Permission

                        Grant select on schema : : sales to Pavan àSchema level

                        Grant backup database employee to Pavan à Data base level

• SQL Server supports two types of authentication modes

                                      i.    Windows Authentication

                                    ii.      Mixed Mode Authentication(Windows + SQL Server)

Frequently Asked Questions

1: What is 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

2: What is Orphan login?

            The windows login for which there is no mapping user in domain or windows is called orphan login. We can find out orphan logins using

                                    Sp_validatelogins

3: What is 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.

4: When will you get Error: 18456 How to resolve it?

            This error is getting when we are trying to connect to SQL Server with wrong credentials or server is not running in mixed mode. i.e.

• Verify the login and password
• Check whether the server is running in Mixed mode or not.

5: How to Display Server level logins?

To displaying logins:

            Use master

            Go

            Select * from syslogins (or) select * from sys.syslogins

To display all logins and also a particular login:

                        Sp_helplogins    à  for all logins

                        Sp_helplogins  à for particular login

To display all roles and respective members:

                        Sp_helpsrvrolemember

                        Sp_helpsrvrolemember  à for particular role

6: Where logins are stored in server level?

            All the logins are stored in Master syslogin table.

7: What is guest user account? What login is it mapped to? Does it make sense to drop the guest user account?

            The guest user account is created by default in all db’s and it is used when explicit permissions are not granted to accesses an object. It is not mapped directly to any login but can be used by any login depending on your security needs; it may make sense to disable the guest user account in all db’s.

8: Once we create a user by default what permissions we get?

            Every user on database by default mapped under public role with this role the user can accesses system defines objects.

9: Difference between Login and User?

Login:

• It can be created at server level.
• It is used to authenticate with the server.
• One login can be associated with many db’s on the server (one per database).

User:

• User can be created at database level.
• It can be used to authenticate with database.
• One user per database which are mapped to corresponding login.

10: What is orphan user? How to find it?

            The user for which there is no mapping login is called orphan user. User becomes Orphan in the following scenarios

• When we restore database into diff. instance.
•   In log shipping and mirroring in secondary server user becomes Orphan.
• If the login was deleted, We can check orphan users using

                        User

                        Go

                        Sp_change_users_login ‘report’

11: What are the advantages and disadvantages of not dropping the SQL Server BUILTIN\Administartor group in 2005?

Advantages:

1. Any windows login in windows admin group is by default a SQL Server system admin.
2. This single group can be used to manage administrators from a windows and SQL Server perspective.

Disadvantages:

1. Any windows login is by default a SQL Server system administrator, which may not be a desired situation.
2. SQL Server BUILTIN\Adminstrators group has administrator rights by default.
3. SQL Server itself doesn’t need to be tracked to gain accesses to your data, if the windows local administrator group is compromised then it is possible to accesses SQL Server as a system administrator.

12: Which operation takes highest priority?

            DENY operation takes highest priority than other operations.

13: How to check out all role members in db level?

            Sp_helprolemember <’rolename’>

            Ex: sp_helprolemember ’db_owner’

14: Where is the user information stored?

            User info stored in sysusers table

15: What are the scenarios where we have to run server in single_user_mode?

• To restore master database.
• To connect to server using windows admin credentials.
• To detach system databases.
• For scheduled maintenance.
• When SQL server completely locked out we have to start the server in single_user_mode to unlock the accesses.

16: Whenever user wants to get SQL server agent what permissions you have to give?

• After creating login create user in MSDB database
• Add user to ‘SQLAgentOperatorRole’

17: How to check if SQL Server is suing Kerberos authentication?

      Select net_transport,auth_scheme from sys.dm_exec_connections where session_id=@@spid