GCP Refresher – Intro, Free account with 300$ credit, Service Model, Cheat Sheet & Billing
[MSSQLFUN & Decode ITeS becomes a family of 1700 individuals across the globe. Please join us on
Facebook & YouTube.]
Let’s review our GCP concepts.
Refresher – Intro, Free account with 300$ credit, Service Model, Cheat Sheet & Billing
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Subscribe YouTube Channel Decode ITES
Other Linked Profiles:-
Complete Guide To “SQL Server Security & Architecture and Internals”
[MSSQLFUN & Decode ITeS becomes a family of 1700 individuals across the globe. Please join us on
Facebook & YouTube.]
Refer our Complete Guide To “SQL Server Security & Architecture and Internals” on our YouTube Channel DecodeITeS.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Subscribe YouTube Channel Decode ITES
Other Linked Profiles:-
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
- 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
- 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 |  |
|
 |
| 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 principals – Logins & How does Login Authentication work in SQL Server?
[MSSQLFUN & Decode ITeS becomes a family of 1600 individuals across the globe. Please join us on
Facebook & YouTube.]
SQL Server Level Principals
SQL Server level principals are the only way to connect to SQL Server to access data. They are the entry guard to ensure only authorized person entries and perform legitimated functions only.
SQL Server Level Principals has 2 components.
- Logins
- Server Roles.
Logins
Login is part of the Server Scoped Security Principal used to connect and access the SQL Server. In other terms, Credentials like user-id password, Token / Certification based access to access your data.
SQL Server supports 3 types of logins out of 2 (SQL & Windows Login) is mostly used in the environment and available from legacy versions of SQL Servers.
· SQL Server authentication Login
· Windows authentication login
- Individual Windows user
- Windows group
· Azure Active Directory authentication login
- Individual AD user
- AD group
Windows Logins: Here, User Id and password stored in Active Directory (AD) and AD authenticates users before they connect to SQL. Microsoft preferred to use windows authentication to ensure updated password policies are applied with a series of encryptions. This is to ensure user credentials and data are not compromised. Windows logins are managed by domain admins and DBA cannot change the password or modify the user. Windows Logins are also called NT logins. Windows logins can be assigned to SQL server as:-
- Individual Login – Individual logins are normal user credentials got direct access to SQL Server by adding it in SQL Server Logins.
- Windows Group – Windows group is a collection of windows users. Windows group will be added in SQL Server logins and on basis of that user’s part of the group will get indirect access to SQL Server. There may be a possibility that a particular user is not part of the group directly but added in the further subgroups. That means. All Users of the Windows group and all subgroups in the hierarchy will get SQL access in one go. This is very useful when access needs to be given to a large group.
SQL Logins: SQL Logins use Server Authentication. SQL Logins created at SQL Server and stores usernames and passwords in the “Master” database server. There is not the role of the domain controller and active directory in the authentication. It can be used in situations where Active Directory is not available., but, whenever possible, use Windows Authentication exclusively. You can create multiple users under SQL authentication to provided different users different access as per their requirement. You can configure SQL Server to run on SQL Server and Windows Authentication called Mixed Mode.
How to Manage (Create \ Alter \ Delete) Login in SQL using SSMS (SQL Server Management Studio)?
| Right click on Logins and click “New Login” |  |
| Select authentication type under which you want to create new login |  |
| For Windows Login, Enter Login Name with |  |
| For SQL Login, Enter Login Name with |  |
For Windows Login,
|
 |
For SQL Login,
|
 |
| Select Server role under which you want add login |  |
| Select Database and DB level permission to login. You can also assign it later. |  |
| To Delete, Right click on login and select delete |  |
| Delete Login Windows, Click OK. It will give you warning that DB users will remain orphan and you can delete it later. |  |
How does Login Authentication work in SQL Server?
- Windows Login: Windows authentication is more secured than Database authentication because it works on encryption and certificate-based security procedure. Windows login pass token in place of a user ID and password to SQL Server. This Token is provided by windows active directory \ Domain controller or local computer. This token is authentication validity pass with SID details of logins. In case login is not part of direct SQL Server login, all associated windows groups & subgroups SID will be added to the token for validation. Once SQL Server received the valid token, it compares the SID from token to SID saved sys.server_principals system view in SQL Server. Based on the result of SID Comparison access of SQL Server is granted or denied.
- SQL Login: SQL Login authentication is quite simpler than windows login. SQL Login provided user ID & password to SQL Server. User ID & password is saved in the master DB system view. SQL Server compared the user ID and corresponding password. If user ID exists and the password is correct, access is granted.
How to check logins are SQL or Windows at SQL Server?
We can use the below query to review logins that exists at SQL Server are SQL or Windows.
| SELECT SP.NAME AS LOGIN,
SP.TYPE_DESC AS LOGIN_TYPE, SL.PASSWORD_HASH, SP.CREATE_DATE, SP.MODIFY_DATE, CASE WHEN SP.IS_DISABLED = 1 THEN ‘DISABLED’ ELSE ‘ENABLED’ END AS STATUS FROM SYS.SERVER_PRINCIPALS SP LEFT JOIN SYS.SQL_LOGINS SL ON SP.PRINCIPAL_ID = SL.PRINCIPAL_ID WHERE SP.TYPE NOT IN (‘G’, ‘R’) |
Script to list permissions of all logins
| SELECT sp.NAME AS LoginName,
sp.type_desc AS LoginType, sp.is_disabled As Is_Disable, sp.default_database_name AS DefaultDBName, SL.sysadmin AS SysAdmin, SL.securityadmin AS SecurityAdmin, SL.serveradmin AS ServerAdmin, SL.setupadmin AS SetupAdmin, SL.processadmin AS ProcessAdmin, SL.diskadmin AS DiskAdmin, SL.dbcreator AS DBCreator, SL.bulkadmin AS BulkAdmin FROM sys.server_principals sp JOIN master..syslogins SL ON sp.sid = SL.sid WHERE sp.type <> ‘R’ AND sp.NAME NOT LIKE ‘##%’ |
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Subscribe YouTube Channel Decode ITES
http://www.facebook.com/mssqlfun
Other Linked Profiles:-
Principals, Securables, Permissions – SQL Server Access Components
[MSSQLFUN & Decode ITeS becomes a family of 1600 individuals across the globe. Please join us on
Facebook & YouTube.]
SQL Server Access Authentication
SQL Server Security is the primary DBA responsibility and required to secure guard your data. SQL Server database contains your users and business data and this needs security from unauthorized access. SQL Server Access Authentication is a crucial aspect of SQL Server Security.
SQL Server provides a list of mechanisms to provide access to the user. For New DBAs or other than DBAs, this process seems to be complex and confusing. These access controls help to provided exact access to each required user to ensure ZERO extra privilege or data access to anyone.
Components of SQL Server Access Authentication
SQL Server access authentication has 3 components:-
- Principals: Objects that can request SQL Server resources called Principals. Access to Principals is depended on the scope. Based on the scope, Principals can be divided into 2 types:-
- Server Level Principals
- Database Level Principals.
- Securable: Securables are Server or Database objects to which authentication can be provided. Like Table, Endpoints, etc. Securable are divided based on scope, Server Scoped, Database Scoped & Schema Scoped.
- Permissions: Permissions are giving control of securable to the principal. Principal can perform the required operation of securable as permissions assigned, Permissions at Server level managed via Logins and Server roles whereas at database-level permissions are managed using database user and roles.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Subscribe YouTube Channel Decode ITES
http://www.facebook.com/mssqlfun
Other Linked Profiles:-
