Database Security is most important to every organisation with any elevated access should be disabled to product the database. 

Below script is quick check to understand the User and its database roles in specific database. Make sure to grant or revoke  appropriate database level access to end users/Group. Keep checking/auditing the role regularly.

USE [Your Database];

SELECT u.[name] AS [UserName], r.[name] AS RoleName 

FROM sys.database_principals u 

JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id 

JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id

where  u.[name] = 'dbo'  OR  u.[name] = 'db_owner'

Image from Microsoft

Create SQL Login and SQL User on your Azure SQL DB

/*1: Create SQL Login on master database (connect with admin account to master database)*/
CREATE LOGIN MyLogin WITH PASSWORD = '';


/*2: Create SQL user on the master database (this is necessary for login attempt to the database, as with Azure SQL you cannot set the DEFAULT_DATABASE property of the login so it always will be [master] database.)*/
CREATE USER MyUser FROM LOGIN MyLogin;


/*3: Create SQL User on the user database (connect with admin account to user database)*/
CREATE USER MyUser FROM LOGIN MyLogin;


/*4. Grant permissions to the user by assign him to a database role*/
ALTER ROLE db_datareader ADD MEMBER MyUser;


/*Login with the newly created login:

With SSMS  use login name and password to connect directly to user database \ master \

When using contained database used, you must set the here to be able to connect.
*/

SQL Server Encryption

Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve access control problems. However, it enhances security by limiting data loss even if access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it is encrypted.

First of all run below T-SQL statment to get current value
select * from sys.dm_exec_connections
To find Encript_option column value ( It should be FALSE)

1. Open the SQL server Configuration Manager
2. SQL Server Network Configuration
   ( Protocols for MSSQLSERVER)  Right click to see propertise
3. You will see three tabs(Flags,Certificate,Advanced)
4. click Flags tab
Force encryption ( default is No)
 change to Yes

Then it is required to restart SQL Server service
Run the following  T-SQL statment
select * from sys.dm_exec_connections
To find encript_option column value ( It should be TRUE)

Important Note
Although encryption is a valuable tool to help ensure security, it should not be considered for all data or connections. When you are deciding whether to implement encryption, consider how users will access data. If users access data over a public network, data encryption might be required to increase security. However, if all access involves a secure intranet configuration, encryption might not be required. Any use of encryption should also include a maintenance strategy for passwords, keys, and certificates.
Source : http://technet.microsoft.com/en-us/library/bb510663.aspx

Login invalid for Orphand User

When you try to fix Orphand users in newly restored database the following error will come if the login id is not match with current server



Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Login name 'prd_myUser' is absent or invalid.

Use user_dbname
go


EXEC sp_change_users_login 'REPORT'
go
--prd_myUser -- Orphand users

EXEC sp_change_users_login 'UPDATE_ONE','prd_myUser','prd_myUser'
go


-- error messageMsg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137


Terminating this procedure. The Login name 'prd_myUser' is absent or invalid.

--Solution
Find the restored database
Expand the Database  Tree
Expand Security
Select  Users to locate 'prd_myUser'  delete it.

Fix Orphand Users in SQL Server

-- Select the user database name
Use  dbname
go


-- Find the Orphand Users
EXEC sp_change_users_login 'REPORT'
go

-- Fix the Orphand Users
EXEC sp_change_users_login 'UPDATE_ONE',
'User_Name','User_Name'

SQL Server default listening port 1433

Please make sure that SQL Server running on default listiening port is 1433.
For the security reason you can change port 1433 to other numbers to protect your servers.

sp_change_users_login to fix Orphaned SQL Server users

The stored procedure sp_change_users_login used  to fix Orphaned SQL Server users.

Microsoft says this feature will be removed in a future version of Microsoft SQL Server.  so instead of using this sp start using  ALTER USER command.

Login failed for user 'DomainName\SQLAdmin'. (.Net SqlClient Data Provider)

Login failed for user 'DomainName\SQLAdmin'. (.Net SqlClient Data Provider)


Go to Start, Programs, SQL Management Studio -- Right Click on that and say Run as Administrator. Now pass your credentials.
This will allow to you open SSMS for your login(SQLAdmin).

But if you need to fix permanently The user SQLAdmin must be part of Local administrator group of the server and should have full rights to access SQL server installation path like C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER

You Cannot drop the guest user from master or tempdb

---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 15182: Cannot drop the guest user from master or tempdb.
---------------------------
OK
---------------------------

Difference between sysxlogins and syslogins in SQL 2000 and 2005

In SQL 2005
System table sysxlogins(master database) is removed from SQL 2005.
and SysObjects and SysUsers are changed from System table to View



-- In SQL 2000
use master
go
select * from sysobjects where name in ('sysxlogins' ,'syslogins') order by xtype
select * from sysobjects where name in ('sysobjects','sysusers') order by xtype


sp_help sysxlogins --system table
go
sp_help syslogins-- view

sp_help sysobjects --system table
go
sp_help sysusers --system table
----------------------------------------------------------

-- In SQL 2005
use master
go
select * from sysobjects where name in ('sysxlogins' ,'syslogins') order by xtype
select * from sysobjects where name in ('sysobjects','sysusers') order by xtype


sp_help sysxlogins --system table is REMOVED no longer exists
go
sp_help syslogins-- view


sp_help sysobjects --VIEW
go
sp_help sysusers --VIEW