SELECT SERVERPROPERTY('EngineEdition');
Database Engine edition of the instance of SQL Server installed on the server.
1 = Personal or Desktop Engine (Not available in SQL Server 2005 and later versions.)
2 = Standard (This is returned for Standard, Web, and Business Intelligence.)
3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.)
4 = Express (This is returned for Express, Express with Tools and Express with Advanced Services)
5 = SQL Database
SELECT percent_complete ,* FROM sys.dm_exec_requests WHERE command IN ( 'RESTORE DATABASE' ,'BACKUP DATABASE' )
Elapsed time in Hours to complete the Job
SELECT command ,percent_complete ,'elapsed' = total_elapsed_time / 3600000.0 ,'remaining' = estimated_completion_time / 3600000.0 FROM sys.dm_exec_requests WHERE command LIKE 'BACKUP%'
EXEC sp_cycle_errorlog
The more often we run this, the smaller error log files will have and will keep it to 6 error log files.
| Permission name | Description |
| ALTER | Permission to modify the object's definition |
CONNECT |
Permission to access the database or connect to the endpoint |
DELETE |
Permission to delete the object |
EXECUTE |
Permission to execute the stored procedure or the function |
IMPERSONATE |
Permission to take the identity of a principal, by the means of an EXECUTE AS command |
INSERT |
Permission to insert data into the table or view |
REFERENCES |
Permission to reference the object in a foreign key definition, or to declare a view or function WITH SCHEMABINDING referencing the object |
SELECT |
Permission to issue a SELECT command against the object or column |
TAKE OWNERSHIP |
Permission to become the owner of the object |
UPDATE |
Permission to update the data |
VIEW DEFINITION |
Permission to view the definition (structure) of the object |
SELECT * FROM sys.fn_builtin_permissions(DEFAULT) ORDER BY class_desc
SELECT NAME ,is_disabled FROM sys.sql_logins WHERE is_policy_checked = 0 ORDER BY NAME;All the logins listed in the output might have a weak password.
ALTER LOGIN MyLogin WITH CHECK_POLICY = ON ,CHECK_EXPIRATION = ON;
This code modifies the MyLogin login to enforce password policy and expiration.SELECT LOGINPROPERTY('MyLogin', 'DaysUntilExpiration');
ALTER LOGIN MyLogin WITH PASSWORD = 'ch@nge y0ur Pa$$word !' MUST_ CHANGE ,CHECK_POLICY = ON ,CHECK_EXPIRATION = ON;
SELECT 'ALTER LOGIN ' + QUOTENAME(NAME) + ' WITH PASSWORD = ''ch@nge y0ur Pa$$word !'' MUST_CHANGE, CHECK_POLICY = ON, CHECK_ EXPIRATION = ON; ' FROM sys.sql_logins WHERE is_policy_checked = 0 ORDER BY NAME;
Note:The best way to protect passwords against brute-force attacks is to enforce Windows
password policies and expiration, because this will guarantee passwords are strong enough from being guessed. This attacks leaves the traces in SQL Server Error log.
sp_configure 'contained database authentication' ,1 GO RECONFIGURE
USE [master] GO ALTER DATABASE [marketing] SET CONTAINMENT = PARTIAL;
SELECT 'EXEC sp_migrate_user_to_contained @username = N''' + dp.NAME + ''', @rename = N''keep_name'', @disablelogin = N''do_not_disable_login'' ;' FROM sys.database_principals AS dp INNER JOIN sys.server_principals AS sp ON dp.sid = sp.sid WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;
This code returns execute statements copy it and executeUSE master;
GO
REVOKE VIEW ANY DATABASE
TO PUBLIC;
USE master; CREATE SERVER ROLE [DBViewer]; GO GRANT VIEW ANY DATABASE TO [DBViewer]; ALTER SERVER ROLE [DBViewer] ADD MEMBER [MyLogin];