Database Security !

Q) How to view all the server principals ?
A)
select * from sys.server_principals

Q) How to view all the server permissions ?
A)
select * from sys.server_permissions

Q) How to view all the sql logins ?
A)
select * from sys.sql_logins

Q) How to find each executable system object that can be enabled or disabled by a surface area configuration component ?
A)
select * from sys.system_components_surface_area_configuration
-------------------------------------------------------------------------
Now lets talk in detail about Database security.

Q) By default how many db users exist and what are they ?
A)
dbo
guest
information_schema
sys

Script is as follows :
USE [kali]
GO
CREATE USER [sys]
GO
CREATE USER [INFORMATION_SCHEMA]
GO
CREATE USER [guest] WITH DEFAULT_SCHEMA=[guest]
GO
CREATE USER [dbo] FOR LOGIN [HOME-BIX5T88DR6\Administrator] WITH DEFAULT_SCHEMA=[dbo]
GO

Q) What are the 2 types of roles available at db level ?
A)
Database Role
Application Role

Q) How to create a user ?
A)
USE [kali]
GO
CREATE USER [cat3] FOR LOGIN [sqlcat3] WITH DEFAULT_SCHEMA=[dbo]
GO

Q) How to alter a user's schema ?
A)
ALTER USER [cat3] WITH DEFAULT_SCHEMA=[dbo11]
GO

Q) How to drop a user ?
A)
DROP USER [cat3]
GO

Q) How to create a database role ?
A)
CREATE ROLE [readview] AUTHORIZATION [dbo]
GO
this means the user dbo here owns the role readview.

Q) How to change the authorization of a db role ?
A)
ALTER ROLE [readview] AUTHORIZATION [cat]
GO

Q) How to drop a database role ?
A)
DROP ROLE [readview]

Q) How to create a application role ?
A)
CREATE APPLICATION ROLE [app_role_1] WITH DEFAULT_SCHEMA = [dbo], PASSWORD = N'Gambler1!'
GO

Q) How to change the password and default schema for a application role ?
A)
ALTER APPLICATION ROLE [app_role_1] WITH DEFAULT_SCHEMA = [dbo11], PASSWORD = N'Gambler111!'
GO

Q) How to drop a application role ?
A)
DROP APPLICATION ROLE [app_role_1]

Q) How to add a user/member to a role ?
A)
EXEC sp_addrolemember N'db_datareader', N'cat3'
GO

Q) How to create a schema ?
A)
CREATE SCHEMA [sam] AUTHORIZATION [dbo]
GO

Q) I have the schemas and its associated tables in it as follows :
schema1-tab1
schema2-tab2
Now transfer the tab1 from schema1 into schema2.
A)
ALTER SCHEMA schema2 TRANSFER schema1.tab1;
GO

Q) How to drop a schema ?
A)
DROP SCHEMA schema2

Q) What is the difference between database role and application role ?
A)
Application Roles
Application roles are the SQL Server roles created to support the security needs of an application. Often database applications enforce their own security based on the application logic. For example, you can use application role with its own password to allow the particular user to obtain and modify any data only during specific hours. So, you can realize more complex security management within the application logic.

Database Roles

A database role applies to a single database. The database roles in a database apply to only that database and the objects within that database. Database roles are not used to grant or deny administrative access to objects.
There are three kinds of the database roles:
-Fixed Database Roles;
-Public Role;
-User-Defined Database Roles.

Fixed database roles are defined at the database level and exist in each database.
You cannot add, delete or modify fixed database roles. You can only add users as a member of a fixed database roles.

Fixed database roles are:
-db_owner
-db_accessadmin
-db_datareader
-db_datawriter
-db_ddladmin
-db_securityadmin
-db_backupoperator
-db_denydatareader
-db_denydatawriter

The public role is a special database role to which every database user belongs. The public role contain default access permissions for any user who can access the database. This database role cannot
be dropped.

Although the built-in database roles handle permissions for common database management tasks, it's likely that you will want to group users who have access to perform specific database functions.

Q) How to view all database principals ?
A)
select * from sys.database_principals

Q) How to view all database permissions ?
A)
select * from sys.database_permissions

Q) How to view all database role members ?
A)
select * from sys.database_role_members

Q) How to view the default db roles in any db ?
A)
exec sp_helprole

Q)