SQL Server principals – Server Roles

[MSSQLFUN & Decode ITeS becomes a family of 1700 individuals across the globe. Please join us on
Facebook 
& YouTube.]

Server Roles: Server roles are security principals to control access of other principals. Server roles are server scoped and provide server-level permissions to logins.

  • Fixed / Built-in / Default Server Roles
  • User-Defined / Customized Server Roles
  1. Fixed / Built-in / Default Server Roles: Microsoft SQL Server comes up with 9 fixed or default server roles. DBA or Database security admin can use these logins to provide logins created on the server.
  • sysadmin
  • bulkadmin
  • dbcreator
  • diskadmin
  • processadmin
  • securityadmin
  • serveradmin
  • setupadmin
  • public

SYSADMIN:

  • SYSADMIN is the most powerful server role in SQL Server. This role gives to login a server owner level permissions and login with this role free to perform any operation or activity.
  • SYSADMIN role detrudes the security checks and restrictions made at login levels.
  • SYSADMIN role can manage other server roles both fixed or user-defined.
  • SYSADMIN can perform all actions other roles can perform and more.
  • SYSADMIN should be controlled and provided to restrict people only. I have seen none other than DBA have SYSADMIN roles in production environment also in some of the restricted environments even only high skills senior DBAs have SYSADMIN permissions rest of junior DBAs will have lower privileges.
  • Login with SYSADMIN not needed direct access on the database, SYSADMIN role super seed DB level access. Login can manage DB level users & objects.

BULKADMIN:

  • Bulk insert operations are inserting data from files to SQL server tables and vice versa. Bulk admin had the privilege to deals with files laying outside of SQL Server.
  • Bulk admin access provides permission to execute bulk command further access on tables & files at the operating system will also be required.
  • Bulk admin roles play important while working with ETL (Extract, Transform, and Load) tools.
  • BCP is a utility given by Microsoft to use Bulk insert.

DBCREATOR:

  • dbcreator as the name implies, it gives the privilege to create a database on SQL instance.
  • Useful for an application that needs to create new DB on a timely basis or for developers to create new DBs in the NON PROD server.
  • Login who creates a database will become the owner of the database by default. Login will be able to perform any operation DB as being DB Owner.

DISKADMIN

  • Legacy server role and still available but used very rarely
  • Login with diskadmin role can manage backup device

PROCESSADMIN

  • One more rarely used server role
  • Login with this role can manage sessions running at the SQL server. Login can terminate any user sessions.
  • DBA should think before providing this role as untrusted or fewer experience individuals can cause issues with currently running processes at SQL Server

SECURITYADMIN

  • Logins with securityadmin server role can manage logins (Alter \ Disable \ Drop \ Change Password for SQL Logins)
  • Logins with this role can grant Control Server Permissions
  • These logins can manage database users and securable only in case having direct access to DB.
  • Rarely used but need to be careful while assigning
  • Cannot change permissions or password of sysadmin role logins

SERVERADMIN

  • Powerful role nearer to SYSADMIN
  • Login with serveradmin server role can manage server-level configurations
  • Usually, DBA with sysadmin role manage server-level configuration so seen very rarely in used
  • Login with this role can SHUTDOWN the SQL Server, so double check before assigning this role
  • Members of this role can create \ alter endpoints

SETUPADMIN

  • Logins with setupadmin can create\alter\drop linked servers
  • Very rarely used

PUBLIC

  • Every login is part of public server role by default
  • You cannot add or remove any login to public server role
  • Permission assigned to public will automatically be inherited to logins assigned
  • You can change permissions of public role
  1. User-Defined / Customized Server Roles: User-Defined Server roles were introduced in SQL Server 2012. In case a fixed /default server roles are not sufficient for your environment, you can create a user-defined server role. User-defined server roles/customer server roles can be created using SQL Server management studio or TSQL.
  • What you can grant to User-Defined Server Roles?

You can grant only server-level (ENDPOINT, LOGIN, SERVER, AVAILABILITY GROUP, SERVER ROLE) to user-defined server roles. You can execute sys.fn_builtin_permission to list down all permissions. You can create a role with extra permissions over fixed role or can create a role with no fixed role permissions and only specific permissions

SELECT * FROM SYS.FN_BUILTIN_PERMISSIONS(DEFAULT)

WHERE CLASS_DESC IN (‘ENDPOINT’,’LOGIN’,’SERVER’,’AVAILABILITY GROUP’,’SERVER ROLE’)

ORDER BY CLASS_DESC, PERMISSION_NAME

  • How to create a Server Role using SQL Server Management Studio?
Connect to SQL Server Database Engine > Security > Server Roles > Right Click > New Server Role
  1. Enter Server Role Name
  2. Select permissions you want to give to new role from securable
Add Login to whom you want add under this new server role
Select if you want to give permissions of any fixed role to new role, click OK and Server Role is created.
New User-Defined Server role is listed with fixed server role
  • How to create a Server Role using T-SQL?

USE [master]

GO

–CREATE USER-DEFINED SERVER ROLE

CREATE SERVER ROLE [TestServerRole]

GO

–ADD LOGIN AS A MEMBER OF THIS SERVER ROLE

ALTER SERVER ROLE [TestServerRole] ADD MEMBER [Test]

GO

–ASSIGN PERMISIONS

GRANT ALTER ON ENDPOINT::[Dedicated Admin Connection] TO [TestServerRole]

GO

GRANT ALTER ON LOGIN::[sa] TO [TestServerRole]

GO

–ADD PERMISSIONS OF FIXED ROLE TO NEW ROLE

ALTER SERVER ROLE [serveradmin] ADD MEMBER [TestServerRole]

  • Script to list permissions of customized roles
SELECT SYS.SERVER_ROLE_MEMBERS.ROLE_PRINCIPAL_ID, ROLE.NAME AS ROLENAME,

SYS.SERVER_ROLE_MEMBERS.MEMBER_PRINCIPAL_ID, MEMBER.NAME AS MEMBERNAME,MEMBER.TYPE_DESC,PERMISSION_NAME,STATE_DESC,CLASS_DESC

FROM SYS.SERVER_ROLE_MEMBERS

FULL JOIN SYS.SERVER_PRINCIPALS AS ROLE

ON SYS.SERVER_ROLE_MEMBERS.ROLE_PRINCIPAL_ID = ROLE.PRINCIPAL_ID

FULL JOIN SYS.SERVER_PRINCIPALS AS MEMBER

ON SYS.SERVER_ROLE_MEMBERS.MEMBER_PRINCIPAL_ID = MEMBER.PRINCIPAL_ID

FULL JOIN SYS.SERVER_PERMISSIONS PERMISSIONS

ON PERMISSIONS.GRANTEE_PRINCIPAL_ID = MEMBER.PRINCIPAL_ID

WHERE MEMBER.NAME=’TESTSERVERROLE’

SQL Server Authentication

[MSSQLFUN & Decode ITeS becomes a family of 1500 individuals across the globe. Please join us on
Facebook 
& YouTube.]

We will be discussion SQL Server Authentication and below topics:-

  • What is Authentication?
  • Type of authentication in SQL Server (SQL & Windows)
  • Windows Authentication
  • What is Windows authentication?
  • Advantages & Disadvantages

SQL Authentication

  • What is SQL authentication?
  • Advantages & Disadvantages

SQL Server Authentication Modes

  1. What is Authentication?

Authentication, The process of identity verification of the user. In Laymen terms, Attention is the process to check “Who are you?”. It can be based on user-id & password or token-based or certificate-based. Authentication is the key to allow only authorized users can connect to access the data and system.

Only user with correct authentication details able to connect.

  1. Type of authentication in SQL Server (SQL & Windows)

Microsoft supports 2 types of authentications, SQL, and Windows authentication. For each login created on SQL Server, it should be part of either authentication.

  1. Windows Authentication

Windows Authentication depends on Active Directory (AD) to authenticate users before they connect to SQL. Windows auth use series of encryption to ensure no leak of sensitive data. It is the recommended authentication mode because AD is the best way to manage password policies and user and group access to applications in your organization. User Id and password stored in Active Directory. You can run SQL Server only on Windows Authentication called Windows Authentication Mode.

Advantages of Windows Authentication:

ü All in One Domain: When your environment is on one domain, both DB and application are hosting on the same domain. Windows authentication is the best and secure way of DB communication.

ü Domain Controller: Here, we had Domain Controller to manage logins creation \ modification and authentications.

ü Password Policies: Windows logins must bear with password policies. Windows logins can not skip it. This makes the user password more secure and updated.

ü DBA relief: DBA no more needed to manage users and passwords. This task is completely managed by domain admins in windows auth.

ü Windows Group: Windows give functionality to create groups and that group can be added to SQL server to give access to all users in the group in one go.

Disadvantages of Windows Authentication:

ü No Control: DBA has no control over logins, it completely managed by domain admins.

ü Windows Group: Windows groups has a disadvantage as well because membership of the group is hidden to DBA. DBA will not be aware of who is part of groups or when users added or removed from the group.

  1. SQL Authentication

SQL Server Authentication works by storing usernames and passwords in the “Master” database server. It can be used in situations where Active Directory is not available., but, whenever possible, use Windows Authentication exclusively. You can use SQL Server and Windows Authentication at the same time called Mixed Mode.

You can create multiple users under SQL authentication to provided different users different access as per their requirement.

Advantages of SQL Server Authentication

ü Legacy Application Support: You can keep using your legacy application with support of SQL authentication.

ü Vendor Application Support: Vendor or 3rd Party application which are not built specifically to use SQL Server can be used with SQL Server using SQL authentication

ü Cross Operating System: SQL authentication is best to use when the environment had multiple types of operating systems like Windows, Linux, etc.

ü No Domain \ Workgroup \ Non-Trusted Domain Environment: SQL authentication is best to use while working in No Domain, Workgroup, Non-Trusted Domain Environment where users cannot be authenticated using Windows domain controller.

Disadvantages of SQL Server Authentication:

ü Multiple User Name and Password for Users: Let’s consider a user who needs access to multiple database instances. In SQL authentication, the User will have separate login and password for each instance. This will be difficult to manage and annoying.

ü Multiple User Name and Password for DBAs to Manage: Let consider, You are a team of 5 DBAs and using 50 SQL Server instances. This lead to 250 user ID & Password management for DBA.

ü Enforcing Password Policy: SQL Server logins can be skipped from enforcing password policy. These lead to week passwords and no regular change of password.

ü Application or user pass SQL login and password to connect. This communication can be hacked and user id & passwords can be compromised.

  1. SQL Server Authentication Modes

Based on available authentication types, SQL Server supports 2 types of authentication modes.

  • Windows Authentication Mode:- This is the default one and preferred from Microsoft.
  • Mixed Mode: Mixed mode supported Windows authentication along with SQL authentication. Both types (windows & SQL) logins can work under this model.

How to check the current authentication mode?

Right Click on Server Instance Name in Object Explorer > go to Properties > Security Tab

To change authentication mode, you can change the selection from this screen and click ok. This change needs SQL Service to restart to take into effect.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Subscribe YouTube Channel Decode ITES

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg

https://www.linkedin.com/in/rohitgarg1986/

Introduction to Microsoft SQL Server Management Studio (SSMS)

[MSSQLFUN & Decode ITeS becomes a family of 1500 individuals across the globe. Please join us on
Facebook 
& YouTube.]

Microsoft SQL Server Management Studio IDE (Integrated Development Environment) is a powerful GUI tool with a long list of features and options. SSMS is very useful and user friendly for all users like DBA, Developers, Testers, or Students.

Top Action You can do using SSMS:-

  • Connect to Local \ Remote SQL Server Database Engine \ Analysis Services \ SSIS (Integration Services) \ SSRS (Reporting Services) Instance
  • Explore Connected Server Properties and Objects like Logins \ Linked Servers
  • Explore Database list, Database properties & objects
  • Open New Query \ Query Parsing \ Execution \ Results (Grid \ Text \ File) and view execution plans
  • Activity Monitor
  • Lunch other SQL Server tools like SQL Profiler \ DTA
  • Manage SQL Server Agent and Jobs
  • Connect \ Create \ Manage Registered Server
  1. Connect to Local \ Remote SQL Server Database Engine \ Analysis Services (SSAS) \ Integration Services (SSIS) \ Reporting Services (SSRS) Instances using SSMS
  • When you lunch SQL Server Management Studio (SSMS), “Connect to Server” window pop up for server and credentials details by default for Database engine Server Type. In case, if don’t or you had closed or you want to connect other server types, You can open it from Object Explorer > Connect > Select Desired Server Type “Database Engine” as below.

  • Enter Server Name: SSMS prompts you to enter the SQL instance name to connect. It can be a local or remote SQL instance. In a live environment, one SSMS can be used to monitor and manage remote SQL instance for the complete connecting environment.
  • Select authentication Type and Enter User Details: After mentioning the SQL instance name, you need to select the authentication type. Windows authentication or SQL authentication. In windows, your currently used windows AD access will be used to connect or in SQL authentication, you will provide SQL login details. Once you fill all details, It will connect you with the mentioned instance.

Sample Connection:-

  1. Explore Connected Server Properties and Objects like Logins \ Linked Servers
  • To explore Server properties, Right Click on Instance Name and Click Properties. It will open windows with the list of server-level configuration and properties. You can review and make the required changes. Some Major and mostly used configuration from server properties:-
General Tab for Server Edition\ Server Version \ OS Version \ RAM \ Processors details
Min \ Max Memory Configuration in Memory Tab
SQL Server Authentication Mode in Security Tab
Database files and Backup default location in the Database Settings tab
MaxDop (Max Degree of Parallelism) and CTP (Cost Threshold for Parallelism) in advance tab
  • Server Objects like Logins \ Triggers \ Linked servers be browed and reviewed.

  1. Explore Database list, Database properties & objects
  • To Explore a list of available databases and Database objects, You need to browse like below:-

  • To review and Configure Database properties, Right Click on Database Name & go to properties. Some of the major database properties are:-
General Tab for Database Owner, Creation Date, Total & Free Space, Last backup timestamp details
Files Tab for Files details and modifications
Options Tab for Checking and changing Collation, Recovery Model, Compatibility Level, etc.
  1. Open New Query \ Query Parsing \ Execution \ Results (Grid \ Text \ File) and view execution plans

  • Open New Query \ Query Parsing \ Execution \ Results (Grid \ Text \ File)
Results in Grid
Results in Text
Results in File (You will prompt to select a location to save the file with execution results)
  • Intellisense: SSMS IntelliSense is an interesting feature, this provides runtime suggestions for object names and reduce query-writing time. Intellisense populates all objects in the buffer and shows then as an option.

  • Estimated & Actual Execution plan of queries
  1. Lunch other SQL Server tools like SQL Profiler \ DTA

  1. Activity Monitor: Activity Monitor can be started by right click on instance name. It’s a GUI monitoring wizard for activities & sessions running on the server.

You can drill down multiple options on Activity Monitor Page for details of each segment.

List of features and option in SSSMS is countless, I suggest to download and install it to explore.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Subscribe YouTube Channel Decode ITES

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg

https://www.linkedin.com/in/rohitgarg1986/

%d bloggers like this: