This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What do you know about the Change Data Capture (CDC)?
Change Data Capture (CDC) is used to track the changed data caused by common DML operations (INSERT/UPDATE/DEKLETE). The capture process reads the transaction log to pull out the data change information and then load the information into the associated change table.
How to disable change data capture (CDC) on a table?
|
1 2 3 4 5 6 7 |
USE Your_Database_Name GO EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'YourTableName', @capture_instance = N'dbo_YourTableName' GO |
How to check if Change Data Capture is enabled or disabled on a SQL Server Database?
|
1 2 3 4 5 6 |
SELECT [name], is_cdc_enabled FROM sys.databases where name = 'Database_Name' /* is_cdc_enabled = 0 -- CDC is disabled is_cdc_enabled = 1 -- CDC is enabled */ |
How to Restore Database On Which Change Data Capture (CDC) Is Enabled?
When we restore a database from a CDC enabled database backup, it does not restore the CDC object automatically. Also if you try to look for some options for Restore with CDC object etc. in Database Restore Wizard, you would not find anything.
If we restore the database on the same SQL Server instance with same name, CDC objects will be restored and it will start working fine as CDC jobs are available on the server already.
If you try to restore with a different name or restore to different SQL Server instance, It will not restore CDC objects by default. In Restore Database statement, we have to include Keep_CDC.
What is the Guest user account in SQL Server? What login is it mapped to it?
The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object. It is not mapped directly to any login, but can be used by any login.
How can SQL Server instances be hidden?
To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties.
After selecting properties you will just set Hide Instance to “Yes” and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.
Is it possible to disable SA, how?
Disable the SA Login
Disabling the SA account is a good option to prevent its use. When it is disabled no one can use it in any circumstance until it is enabled.
What is the difference between Authenticate and Authorization?
When you log on to a PC with a user name and password you are authenticating. Authorization is the process of verifying that you have access to something. Gaining access to a resource (e.g. directory on a hard disk) because the permissions configured on it allow you access is authorization.
Which one is highest privileged role out of SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole?
SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole andSQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.
What is the password policy for SQL Server Accounts?
- The password must contain uppercase & lowercase letters.
- The password must contain numbers & alphanumeric characters.
- The password must contain non-alphanumeric characters such as &, ^,%,*,$ etc.
- Do not use common known passwords that are easy to guess such as: admin, password, sa, administrator, sysadmin etc.
- Passwords should be long enough (at least 8 characters long).
- It does not allows blank password for the SA account.
What are the disadvantages of SQL Server Authentication Mode?
If a user is a Windows domain user who has a login and password for Windows, he must still provide another (SQL Server) login and password to connect. Keeping track of multiple names and passwords is difficult for many users. Having to provide SQL Server credentials every time that one connects to the database can be annoying.
SQL Server Authentication cannot use Kerberos security protocol.
Windows offers additional password policies that are not available for SQL Server logins.
The encrypted SQL Server Authentication login password, must be passed over the network at the time of the connection. Some applications that connect automatically will store the password at the client. These are additional attack points.
When CHECK_POLICY is ON, what kind of behavior occur?
CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.
The password history is initialized with the value of the current password hash.
Account lockout duration, account lockout threshold, and reset account lockout counter after are also enabled.
What is What is TRUSTWORTHY database?
Applies To: SQL Server 2016.
The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement.
This property can be used to reduce certain threats that can exist as a result of attaching a database.
Where are the places that I should not use the sa login?
- Embedded in management tools or application development tools
- Hard coded in application connection strings
- DTS or SSIS connection objects
- UDL files
- Batch files
- SQLCMD, OSQL or ISQL T-SQL scripts
- In casual conversation
- On sticky notes on your desk or monitor
What is the use of BUILTIN\Administrators Group in SQL Server?
Any Windows login in BUILTIN\Administrators group is by default a SQL Server system administrator. This single group can be used to manage administrators from a Windows and SQL Server perspective.
How to detect Orphaned Users in SQL Server?
|
1 2 3 4 5 6 |
SELECT dp.type_desc, dp.SID, dp.name AS user_name FROM sys.database_principals AS dp LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID WHERE sp.SID IS NULL AND authentication_type_desc = 'INSTANCE'; |
