To update the statistics on the database you should either be DBO of that database or SysAdmin on SQL instance.
If you want to grant the least permission instead of server level permission you make the user DBO of the database. You can do it from database properties windows as below.
You can also make the user DBO using below command.
USE [My_Policy]
GO
EXEC dbo.sp_changedbowner @loginame = N’jugal’, @map = false
GO
ORIGINAL_LOGIN() function returns the name of the original login that connected to the instance of SQL Server and is used to identify original login in all sessions. Even though you will do the security context switch it will return the original login name.
SUSER_SNAME returns the name of user in the current security context.
--connect SQL Using LoginDEMO account SELECT ORIGINAL_LOGIN() Original_Login_func, SUSER_SNAME() Suser_Name_Login_Func --Executing query using LoginTest account execute as login = 'LoginTest' SELECT ORIGINAL_LOGIN() Original_Login_func, SUSER_SNAME() Suser_Name_Login_Func revert --Again executing query be reverting the change SELECT ORIGINAL_LOGIN() Original_Login_func, SUSER_SNAME() Suser_Name_Login_Func
Check below output image for more information. I connected SQL Server using LoginDemo account.
To get the max backup date of the database or to check whether database is backed up or not. You can execute below script to check the last backup date.
SELECT d.name,
MAX(b.backup_finish_date) AS backupfinishdate
FROM master.sys.sysdatabases d
LEFT OUTER JOIN msdb..backupset b
ON b.database_name = d.name
AND b.type = 'D'
GROUP BY d.name
ORDER BY backup_finish_date DESC
SELECT d.name,
MAX(b.backup_finish_date) AS backupfinishdate
FROM master.sys.sysdatabases d
LEFT OUTER JOIN msdb..backupset b
ON b.database_name = d.name
AND b.type = 'L'
GROUP BY d.name
ORDER BY backup_finish_date DESC
For the point time recovery or in case of failure it is essential that you should have the backup history of the database. You can execute the below query against the database to get the backup history.
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= '2013-01-08 00:04:49.000') and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= '2013-01-08 23:59:59')
and msdb.dbo.backupset.database_name = 'Mention the database name'
ORDER BY
msdb.dbo.backupset.backup_finish_date desc
You can use below script to get an idean when was the last time SA account property/password changed.
USE Master GO SELECT [name], sid, create_date, modify_date FROM sys.sql_logins WHERE [name] = 'sa' GO
aiDeeva.com 