T-SQL Scripts -

10 Oct, 2022

SQL Server: T-SQL Script to find object level permission of specific table 0 (0)

MSSQL, T-SQL Scripts database access, SQL server, table level permission

Below query can be used to find object level permission of specific table in SQL Server.

use <DB-- replace database name here
go
select @@servername as Server, db_name() as 'Database', su.name as 'UserName', so.name as Object, permission_name as Permission

… Read the rest

7 Oct, 2022

SQL Server: T-SQL Script to find object level permission of user database 0 (0)

MSSQL, T-SQL Scripts database access, object level permission, SQL server, table level permission, TSQL

Below query can be used to find object level permission of user database in SQL Server.

use <DB-- replace database name here
go
select @@servername as Server, db_name() as 'Database', su.name as 'UserName', so.name as Object, permission_name as Permission

… Read the rest

3 Oct, 2022

T-SQL script to get list of logins having Sysadmin permission 0 (0)

MSSQL, T-SQL Scripts bulkadmin, dbcreator, diskadmin, processadmin, securityadmin, setupadmin, SQL server, sysadmin, TSQL

Below query can be used to check Sysadmin Server role in SQL Server.

select @@servername as Server,sp.name as LoginName,
type_desc as LoginType
from sys.server_principals sp
inner join sys.syslogins sl
on sp.sid=sl.sid
where Sl.sysadmin=1

Sample Output:

… Read the rest

3 Oct, 2022

T-SQL query to check failed SQL Agent Jobs in the Last 24 Hour 0 (0)

MSSQL, T-SQL Scripts SQL agent job, SQL server, SQL Server Agent

Below query can be used to check SQL Agent failed jobs in last 24 hours. This query can be used in the monitoring script to find out failed job in SQL server agent.

SELECT sj.[name] AS "Failed Job Name", jh.run_date,jh.[message]

… Read the rest

2 Oct, 2022

T-SQL script to check server level permission in SQL Server 5 (1)

MSSQL, T-SQL Scripts SQL server, SQL server access, view server state

Below query script can be used to check server level access for specified user in SQL Server.

SELECT spr.name as grantee_name, sp.state_desc, sp.permission_name FROM
sys.server_permissions AS sp JOIN sys.server_principals AS spr ON
sp.grantee_principal_id = spr.principal_id
where spr.name not like '%##%'

… Read the rest

29 Sep, 2022

SQL Server: T-SQL script to get list of logins with server roles 0 (0)

MSSQL, T-SQL Scripts bulkadmin, dbcreator, diskadmin, processadmin, securityadmin, serveradmin, setupadmin, SQL Server role, sysadmin

Below query can be used to get SQL Server logins with server role in SQL Server.

SELECT @@servername as Server, sp.name Login_Name
,Account_Type = CASE sp.type_desc
WHEN 'Windows_Group'
THEN 'Windows Group'
WHEN 'WINDOWS_LOGIN'
THEN 'Windows Login'
ELSE 'SQL Login'
END

… Read the rest

27 Sep, 2022

T-SQL query to check last Full, Differential and Log Backup Times 0 (0)

MSSQL, T-SQL Scripts Differential Backup, Full Backup, Log backup, SQL server, TSQL

Below query can be used to check last full, differential and transaction log backup times in SQL Server.

SELECT sd.[Name] AS DatabaseName,
LastFullBackUpTime=(SELECT MAX(bs.backup_finish_date) FROM msdb.dbo.backupset bs WHERE
bs.[database_name]=sd.[name] AND bs.[type]='D'),
LastDiffBackUpTime=(SELECT MAX(bs.backup_finish_date) FROM msdb.dbo.backupset bs WHERE
bs.[database_name]=sd.[name] AND bs.[type]='I'),

… Read the rest

26 Sep, 2022

T-SQL script to get list of SQL Server Agent job 0 (0)

MSSQL, T-SQL Scripts SQL server, SQL Server Agent, SQL Server Agent Job

Below query can be used to get list of all the SQL Agent Job details in SQL Server.

use msdb;
go
SELECT Convert(varchar(22),SERVERPROPERTY('ServerName')) AS 'ServerName',
convert(varchar(35),j.name) as 'JobName',
j.enabled,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS 'JobStatus',

… Read the rest

25 Sep, 2022

SQL Server: T-SQL Script to find object level permission for specific user 5 (1)

MSSQL, T-SQL Scripts database access, object level permission, SQL server, table level permission, TSQL

Below query can be used to find object level permission for specific user in SQL Server database.

use <DB-- replace database name here
go
select @@servername as Server, db_name() as 'Database', su.name as 'UserName', so.name as Object, permission_name as

… Read the rest

23 Sep, 2022

T-SQL query to get list of logins with bulkadmin role in SQL Server 0 (0)

MSSQL, T-SQL Scripts bulkadmin, dbcreator, diskadmin, processadmin, securityadmin, ServerRole, setupadmin, SQL server, sysadmin, TSQL

Below query can be used to check bulkadmin server role in SQL Server.

select @@servername as Server,sp.name as LoginName,
type_desc as LoginType
from sys.server_principals sp
inner join sys.syslogins sl
on sp.sid=sl.sid
where Sl.bulkadmin=1

Sample Output:

… Read the rest

Category Archives: T-SQL Scripts