Skip to content

SQLCMD and Microsoft SQL Server

SQL query Sharepoint user

select tp_ID, tp_login, tp_token, tp_IsActive, tp_Deleted
from [Sharepoint_Intranet_WSS_Content].[dbo].[UserInfo]
where tp_Login like '%viai%' order by tp_Login

SQL query remove Sharepoint user

begin tran
delete from [Sharepoint_Intranet_WSS_Content].[dbo].[UserInfo]
where tp_Login like '%viai%'
rollback
-- commit

SQL query select join tables Get Bitlocker key from MBAM DB

SELECT M.[Id],M.[Name],MV.[VolumeId],K.[RecoveryKeyId],K.[RecoveryKey]
FROM [RecoveryAndHardwareCore].[Machines] M
LEFT JOIN [RecoveryAndHardwareCore].[Machines_Volumes] MV ON M.[Id]=MV.[MachineId]
LEFT JOIN [RecoveryAndHardwareCore].[Keys] K ON MV.[VolumeId]=K.[VolumeId]
WHERE M.[Name] like '%viko%'

SQL query hours time passed since last database full backup

SELECT
bs.database_name AS db,
MAX(bs.backup_finish_date) AS d,
DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS hours
FROM    msdb.dbo.backupset AS bs
WHERE [type] = 'D' AND database_name = 'AlgolDev'
GROUP BY database_name

SQL single user access for admin access

Open SQL Server Configuration Manager and find instance you want to access as admin (f.x. SQLEXPRESS2014)
Open instance's properties -> Startup Parameters and add
```
-mSQLCMD
```
Restart SQL instance

Open CMD console and find SQLCMD utility

cd "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"

Connect to the instance in SQLCMD
```
sqlcmd -S KVA-DEVSQL\SQLEXPRESS2014 
```

Create Windows logon and assing sysadmin role to it

CREATE LOGIN [domain\adminaccount] FROM WINDOWS;  
go
ALTER SERVER ROLE sysadmin ADD MEMBER [domain\adminaccount]; 
go

Remove startup parameter and restart the instance

MS SQL sqlcmd query

sqlcmd -S SQL-DEV\MSSQL2019 -U <sql_user> -P <password> -d msdb -h -1 -W -Q "SELECT 9999"

CREATE LOGIN "DEMO\WebApplication1$"
    FROM WINDOWS
    WITH DEFAULT_DATABASE = "MusicStore"
GO

USE MusicStore
GO
CREATE USER WebApplication1 FOR LOGIN "DEMO\WebApplication1$"
GO

EXEC sp_addrolemember 'db_datareader', 'WebApplication1'
EXEC sp_addrolemember 'db_datawriter', 'WebApplication1'

MS SQL get latest backup date

./sqlcmd -S mssql.server.com -U username -P userpassword -d msdb -h -1 -W -Q "SET NOCOUNT ON; SELECT DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS hours FROM msdb.dbo.backupset AS bs WHERE [type] = 'D' AND database_name = 'databasename'"