Monthly Archives: June 2020

Write a VB Script to test connection to your sql server

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)

objConnection.Open _
“Provider=SQLOLEDB;Data Source=localhost;” & _
“Trusted_Connection=Yes;Initial Catalog=TestDB;” & _
“User ID=testUser;Password=password;”

objRecordSet.Open “SELECT * FROM Customers”, _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo objRecordSet.RecordCount

how to check user privileges on a table in SQL Server

SELECT SUSER_NAME(), USER_NAME();

SELECT
HAS_PERMS_BY_NAME
(
N’dbo.QTest’,
N’OBJECT’,
N’DELETE’
);

EXECUTE AS LOGIN = ‘TestUser’;

–Verify the execution context is now login1.
SELECT SUSER_NAME(), USER_NAME();
SELECT
HAS_PERMS_BY_NAME
(
N’dbo.QTest’,
N’OBJECT’,
N’DELETE’
);
REVERT;
–Display current execution context.
SELECT SUSER_NAME(), USER_NAME();