How to give access to a user to a SQL Server Instance and to a SQL Server Database in an instance?

Problem: How to give access to a user to a SQL Server Instance and to a SQL Server Database in an instance?

Solution: U​SE​R

For giving access to a user to SQL Server Instance we must create a login for the user or user group in the master database and we can authenticate the login.

1    1.     Windows can authenticate the login when connected to the server.

Windows-authenticated SQL Server logins are instance logins in which the operating system handles authentication.

•         login Name for a local account can be Server name\username
•          login Name for a local security group can be Server name\group name
•          login Name for a Domain account can be Domain Name\username
•          login Name for a Domain security group can be Domain Name\group name 

2.    SQL Server Authentication

SQL Server–authenticated logins are authenticated by the Database Engine instance rather

than through the host operating system or a domain controller. SQL Server Authentication gives the flexibility to create a password authenticated login.

2   3.     Certificate

You can create a certificate to be associated with a login if a database master key is present

by using the CREATE CERTIFICATE statement

     4.     ASymmetric Key

You can also use an asymmetric key to authenticate SQL logins. Unlike certificates, asymmetric

keys contain both a public key and a private key.

Example:

In this example, I would like to show we can create a login via SSMS and T-SQL.

1.     Connect to the SQL Server using from SQL Server Management Studio by database engine.

2.     Go to the Security folder and right click on the logins and then click on new.

  

3.     You would see a screen like one below. Search for the logins by clicking the search and which can also help to check names.

Similarly using T-SQL like below.

USE [master]

GO

CREATE LOGIN [TROPPLTMSAMBU\Guest] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

To verify a login whether login is created we can verify it by going to the security node and clicking the logins folder or checking in the System catalog views for server credentials/ logins.

select * from sys.server_principals

where name ='TROPPLTMSAMBU\Guest'

OUTPUT:

name	principal_id	sid	type	type_desc	is_disabled	create_date
TROPPLTMSAMBU\Guest	372	XXXX	U	WINDOWS_LOGIN	0	2017-04-19 23:17:53.480

In the later articles let’s see how to use certificate and asymmetric keys for logins.