Check Who has Server Level Access, SQL Server

Using below two SQL queries we can retrieve who has what access on the database server.
I have divided it into two-parts

1. Role access [ sp_addsrvrolemember & sp_dropsrvrolemember ]
2. Grant/Deny access [ GRANT .. & DENY .. ]

1. Role access: this SQL will show the SQL command of Add/Remove user to this role. So that you can use any to add/ or remove

select prm.name as "UserName", prr.name as "ServerRole", 'exec sp_addsrvrolemember '''+prm.name+''','''+prr.name+'''' as GrantCMD, 'exec sp_dropsrvrolemember '''+prm.name+''','''+prr.name+'''' as DenyCMD from sys.server_role_members srm left join sys.server_principals AS prm on prm.principal_id = srm.member_principal_id left join sys.server_principals AS prr on prr.principal_id = srm.role_principal_id

The output of this SQL would be like below

Role Access

2. Grant/Deny access: using below SQL, we can retrieve the user list with the access name. I have also prepared the GRANT/DENY command to use when required.

select pr.name as "UserName", sp.permission_name, sp.state_desc, sp.state_desc collate SQL_Latin1_General_CP1_CI_AS+' ' +sp.permission_name collate SQL_Latin1_General_CP1_CI_AS+' TO ' +QUOTENAME( pr.name collate SQL_Latin1_General_CP1_CI_AS) as Command from sys.server_permissions sp left join sys.server_principals AS pr on pr.principal_id = sp.grantee_principal_id where pr.name not in ('public','sa','NT AUTHORITY\SYSTEM') and pr.name not like '##%##' and sp.permission_name not in ('CONNECT SQL')

The output of this SQL would be like below:

Grant / Deny Access

Thanks.

Shrink LOG files automatically, SQL Server

Use below script to shrink T-LOG files. This script will shrink the log file which is larger than the value of the variable "@LOG_SIZE_LIMIT". You can change it to meet your requirement.

To run this script automatically, create a SQL JOB and place this script in the STEP. That's all.

See Also: SHRINK DATABASE, Check Job Status, Grant Access to SQL Agent, View Job history.

USE master; GO DECLARE @LOG_SIZE_LIMIT int = 10240 -- 10GB declare  DBName cursor for select a.DATABASE_NAME,mf. name AS LogicalFileName ,LOG_SIZE_MB from( SELECT DATABASE_NAME = DB_NAME(s.database_id)     , LOG_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(10,2))     , ROW_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(10,2))     , TOTAL_SIZE_MB = CAST(SUM(size) * 8. / 1024 AS DECIMAL(10,2)) FROM sys.databases s JOIN sys.master_files m ON s.database_id = m.database_id where s.database_id > 4 and s.state_desc= 'ONLINE' and s.recovery_model_desc = 'SIMPLE' GROUP BY s.database_id,s.name ) a inner join sys.master_files AS mf on DB_NAME(mf.database_id) = a.DATABASE_NAME and mf.type_desc = 'LOG' where LOG_SIZE_MB > @LOG_SIZE_LIMIT ORDER BY LOG_SIZE_MB desc declare @DBNAme varchar(1000), @LogicalFileName varchar(1000), @LOG_SIZE_MB int, @SQLExec varchar(4000) open DBName fetch next from DBName into @DBNAme,@LogicalFileName,@LOG_SIZE_MB WHILE @@FETCH_STATUS=0 BEGIN SET @SQLExec= 'use '+@DBNAme+'; DBCC shrinkfile ('+@LogicalFileName+')' print @SQLExec execute (@SQLExec) fetch next from DBName into @DBNAme,@LogicalFileName,@LOG_SIZE_MB END close DBName deallocate DBName

Thanks.

Backup All Databases, SQL Server

Using the below script we can take all databases backup with one command!!

You can check my other post BACKUP STATUS to know the current DB backup status.

exec sp_msforeachdb ' use [?]; IF ''?'' not in ( ''master'',''tempdb'',''model'',''msdb'',''DBAdmin'') BEGIN        use [master]        Print ''?''        ALTER DATABASE [?] SET READ_ONLY WITH ROLLBACK IMMEDIATE        Print ''Marked -- READ_ONLY''        BACKUP DATABASE [?]        to disk = ''<Disk Location/Path>\?_Backup_FULL_20190924.bak''        with COMPRESSION        Print ''Backup Done''        ALTER DATABASE [?] SET MULTI_USER WITH NO_WAIT END ELSE BEGIN        Print DB_NAME()+ '' -- IGNORED'' END'

Thanks.

Check Database backup History, SQL Server

Always I like to use below SQL to find the backup history either DB backup or LOG backup. this is very handy and useful.

By this SQL you can find the backup DB name,  date-time when the backup was taken, media to use, backup-size, logical path to take backup and many more...

By this SQL you also can find the location where to take backup last time ...

Below Query will show the result for lastest 10 days history. If you need to number of the day of history to check, please change in WHERE condition...

SELECT CONVERT(CHAR(100), Serverproperty('Servername')) AS Server,        bkset.database_name,        bkset.backup_start_date,        bkset.backup_finish_date,        bkset.expiration_date,        CASE bkset.type          WHEN 'D' THEN 'Database'          WHEN 'L' THEN 'Log'        END                    AS backup_type,        bkset.backup_size,        bkf.logical_device_name,        bkf.physical_device_name,        bkset.NAME             AS backupset_name,        bkset.description,        * FROM   msdb.dbo.backupmediafamily bkf        INNER JOIN msdb.dbo.backupset bkset                ON bkf.media_set_id = bkset.media_set_id WHERE  ( CONVERT(DATETIME, bkset.backup_start_date, 102)                   >= Getdate() - 10 ) ORDER  BY bkset.database_name,           bkset.backup_finish_date

This SQL will give output as below .. due to some restriction, I am blackout some data ...

Sample Output of the above SQL

Is Backup/Restore Database/Log running in SQL Server?

Sometimes it's very important to know whether database or log backup or restore is running or not!

in a word, if we need to check the stat/status of a database, we have to run below tSQL. This SQL will show the database names where a backup/restore is running whatever it is DB or LOG backup.

---------------

SELECT r.session_id ,DB_NAME(r.database_id) [Database]     ,r.command     ,CONVERT(NUMERIC(32, 2), r.percent_complete)  [Complete (%)]     ,GETDATE()  [Current Database Time]     ,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) [Running Time (Minute)]     ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) [Required Time (Minute)]        ,(select t.text FROM sys.dm_exec_sql_text(sql_handle) t ) 'Statement text' FROM master.sys.dm_exec_requests r WHERE command like 'RESTORE%' or  command like 'BACKUP%'