$cs="Server=[server.local],1433;Database=DBName_;User ID=[username];Password=[password];"
$c=New-Object System.Data.SqlClient.SqlConnection $cs
$c.Open()
$q="EXEC ('SELECT SYSTEM_USER AS LoginName, IS_SRVROLEMEMBER(''sysadmin'') AS IsSysAdmin;') AT DBACentral;"
$cmd=$c.CreateCommand();$cmd.CommandText=$q
$r=$cmd.ExecuteReader()
while($r.Read()){Write-Host "$($r[0]) $($r[1]) $($r[2])"}
12:05 25/11/2025
What Stored Procs Can I Execute?
$q="SELECT name,type_desc FROM sys.objects WHERE type='P' AND HAS_PERMS_BY_NAME(name,'OBJECT','EXECUTE')=1;"
Stored Procedure Contents for executable ones
$q="SELECT o.name,m.definition FROM sys.objects o JOIN sys.sql_modules m ON o.object_id=m.object_id WHERE o.type='P' AND o.name IN ('usr_init', 'wizsp001', 'SecGetWinUserORAccess ');"
Are we System Admin?
$q="SELECT IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin"
Do we have agent Job Access?
$q="USE msdb;SELECT IS_MEMBER('SQLAgentOperatorRole') AS IsOperator,IS_MEMBER('SQLAgentReaderRole') AS IsReader,IS_MEMBER('SQLAgentUserRole') AS IsUser;"
Is cmdexec being used in jobs?
$q="SELECT * FROM msdb.dbo.sysjobsteps WHERE command LIKE '%cmd%';"
Any stored procs using xp_cmdshell?
$q="SELECT o.name,m.definition FROM sys.objects o JOIN sys.sql_modules m ON o.object_id=m.object_id WHERE o.type='P' AND (m.definition LIKE '%xp_cmdshell%' OR m.definition LIKE '%sp_OA%' OR m.definition LIKE '%CmdExec%');"
Any stored procs that have sp_OA functions?
$q="SELECT o.name FROM sys.objects o JOIN sys.sql_modules m ON o.object_id=m.object_id WHERE o.type='P' AND HAS_PERMS_BY_NAME(o.name,'OBJECT','EXECUTE')=1 AND m.definition LIKE '%sp_OA%';"
Check what dt_ procedures we can ALTER
$q="SELECT name, HAS_PERMS_BY_NAME('dbo.' + name, 'OBJECT', 'ALTER') AS CanModify FROM sys.objects WHERE type='P' AND name LIKE 'dt[_]%';"
Check last execution time procs - View server state required
$q="SELECT OBJECT_NAME(object_id, database_id) AS ProcName, last_execution_time FROM sys.dm_exec_procedure_stats WHERE OBJECT_NAME(object_id, database_id) LIKE 'dt[_]%';"
Create stored procs?
SELECT HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'CREATE PROCEDURE') AS CanCreateProc;
Ole Automation Enabled? CLR?
SELECT value_in_use AS OLEAutomationEnabled FROM sys.configurations WHERE name = 'Ole Automation Procedures';
SELECT value_in_use AS CLRIntegrationEnabled FROM sys.configurations WHERE name = 'clr enabled';
CLR conditions?
SELECT value_in_use AS CLRStrictSecurityEnabled FROM sys.configurations WHERE name = 'clr strict security';
SELECT HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'CREATE ASSEMBLY') AS CanCreateAssembly;
SELECT name, is_trustworthy_on FROM sys.databases WHERE name = DB_NAME();
Any safe assemblies already there?
SELECT name, permission_set_desc FROM sys.assemblies WHERE is_user_defined = 0;
Linked servers?
SELECT name, product, provider FROM sys.servers WHERE is_linked=1;
Is my user allowed remote access?
SELECT ls.name AS LinkedServer, rm.remote_name AS RemoteLogin FROM sys.servers ls INNER JOIN sys.linked_logins rm ON ls.server_id = rm.server_id WHERE ls.is_linked = 1 AND rm.local_principal_id = USER_ID();
Any users allowed?
SELECT ls.name AS LinkedServer, rm.local_principal_id, rm.remote_name, rm.uses_self_credential, rm.is_self_mapping FROM sys.servers ls LEFT JOIN sys.linked_logins rm ON ls.server_id = rm.server_id WHERE ls.is_linked = 1;
Looking for keyword strings in procs
SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE definition LIKE '%system_param%' AND definition LIKE '%set text%' AND definition LIKE '%key_code%' AND OBJECTPROPERTY(object_id,'IsProcedure')=1 AND HAS_PERMS_BY_NAME(OBJECT_NAME(object_id),'OBJECT','EXECUTE')=1;
Am I DB owner?
SELECT IS_ROLEMEMBER('db_owner');
Can we impersonate anyone?
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') WHERE permission_name LIKE '%IMPERSONATE%';
Any keys/certs mapped to me?
SELECT ak.name AS AsymmetricKeyName, c.name AS CertificateName FROM sys.asymmetric_keys ak FULL JOIN sys.certificates c ON ak.sid = c.sid WHERE ak.sid = USER_SID() OR c.sid = USER_SID();
EXEC ('SELECT SYSTEM_USER AS CurrentLogin, USER_NAME() AS CurrentUser;') AT DBACentral;