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.