PowerShell – Execute Batch files & Executable file, Exes – Dynamically passing Parameters

In PowerShell you can execute a executable in 3 ways

  1. Using Invoke-Expression
  2. Call operator ‘&’  “invocation operator”
  3. System.Diagnostics.ProcessStartInfo

 

Original command c:\Progra~1\NetApp\snapdrive\sdcli.exe snap mount -r  -k -d X -s sqlsnap__sql_08-14-2014_10.15.41

1) Invoke-Expression

$snap= “snap mount -r  -k -d X -s sqlsnap__sql_08-14-2014_10.15.41”

Invoke-Expression -Command “c:\Progra~1\NetApp\snapdrive\sdcli.exe $snap”

2)Call operator

& “c:\Progra~1\NetApp\snapdrive\sdcli.exe ” $snap

3)

$psi = New-Object System.Diagnostics.ProcessStartInfo “c:\Progra~1\NetApp\snapdrive\sdcli.exe”
$psi.Arguments = $snap
[System.Diagnostics.Process]::Start($psi)

please refer to this documentation on http://msdn.microsoft.com/en-us/library/system.diagnostics.processstartinfo.aspx to customize

http://social.technet.microsoft.com/Forums/windowsserver/en-US/b9e0f4f3-ae5d-4c8f-8f80-1600e8b63446/run-executable-from-powershell-passing-parameters-and-return-result?forum=winserverpowershell

 

 

 

 

 

 

Advertisements

How to FIX: Manage Orphaned users in SQL SERVER with a simple script

Using below scripts

 

  • Fixes all orphaned users in the current database if a login exists
  • If a login does not exist in thesql server instance
    •  User will be removed  — @fix = 0
      • If a user is a schema owner then the schema is transferred to DBO and then user is removed from DB
    •  or A login will be created  — @fix = 1

 


DECLARE @t TABLE (

id INT IDENTITY(1, 1)

,username SYSNAME

,usersid VARBINARY(85)

);

DECLARE @username SYSNAME = ;

DECLARE @loopid INT = 0D

ECLARE @DeleteCmd NVARCHAR(1000) = 

DECLARE @fix BIT = 0; — if it is set 0 then all the orphans will be removed 

— if its is set 1 orphans will be created and mapped to the user with a default passwordINSERT INTO @t (

username

,usersid

)

EXEC sp_change_users_login @Action = ‘Report’;SET @loopid = @@IDENTITY;WHILE @loopid > 0

BEGIN

SET @username = (

SELECT username

FROM @t

WHERE id = @loopid

)

IF EXISTS (

SELECT 1

FROM master.dbo.syslogins

WHERE NAME = @username

)

BEGIN

EXEC sp_change_users_login @Action = ‘update_one’

,@UserNamePattern = @username

,@LoginName = @username;

PRINT CONVERT(VARCHAR, @username) + ‘User updated succesfully ‘

END

ELSE

BEGIN

IF @fix = 0

BEGIN

SELECT @DeleteCmd = @DeleteCmd + (

CASE

WHEN s.NAME IS NULL

THEN 

ELSE ‘ALTER AUTHORIZATION ON SCHEMA::’ + ‘[‘ + s.NAME + ‘] TO dbo’ + ‘;’

END

) + ‘DROP USER ‘ + ‘[‘ + dp.NAME + ‘]’ + ‘;’

FROM sys.database_principals dp

LEFT OUTER JOIN sys.schemas s ON s.principal_id = dp.principal_id

WHERE type IN (

‘s’

,‘G’

,‘U’

)

AND dp.principal_id > 4

AND dp.NAME = @username

PRINT CONVERT(VARCHAR, @username) + ‘User Removed succesfully ‘

END

ELSE

BEGIN

EXEC sp_change_users_login ‘Auto_Fix’

,@username

,NULL

,‘Auto_Fix_Pass’;

PRINT CONVERT(VARCHAR, @username) + ‘ User has added to sql server with password Auto_Fix_Pass succesfully ‘

END

END

SET @loopid = @loopid  1;

ENDPRINT @DeleteCmdEXEC sp_executesql @DeleteCmd

— Print un resolved db accounts EXEC sp_change_users_login @Action = ‘Report’;

How to fix: Access to the remote server is denied because the current security context is not trusted SQL Server

You are seeing this error because you are trying to impersonate or  switching the execution context using EXECUTE AS clause and Executing a RPC on a linked server

 

LInked server

 

If you have a procedure on Server X

USE ServerXDB

Create procedure proc

execute as ‘TESTUSER’

Execute ServerY.TestDB.dbo.test

Select * from [ServerY] .TestDb.dbo.testtable

To Fix this you have two options
  1. Enable Trusty worthy on ServerXDB database on Serverx
  2. Remove the execute as clause in the procedure and grant execute privileges to user we used to set up the linked server

 

you can also try by signing a certificate on the proc procedure but I havent tried it

How to fix : Server is not configured for RPC.

.Net SqlClient Data Provider: Msg 7411, Level 16, State 1, Line 3

Server name is not configured for RPC.

 

If you receive an error something like this most likely is from the Linked server its because when you try to run a remote procedure call like stored procedures.. Linked server setup should have RPC enabled

In order to fix this change the Linked server properties

EXEC sp_serveroption ‘server_name’, ‘rpc’, ‘true’;

EXEC sp_serveroption ‘server_name’, ‘rpc out’, ‘true’;

or we can change usi SSMS GUI interface

 

Reomote procedure linked server

sp_send_dbmail XML attachment line break issue

I was initially working on creating an event notification that sends me an email alert for our dba team making it work under in a tsql procedure

so once they receive email alert they can open dead lock graph directly from the email account only if ssms is installed on the machine

 

First I tried some thing like this below then I noticed


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ — Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20’
, @attach_query_result_as_file = 1

Then I realized that output text is getting truncated to 256 charecters then I used @query_no_truncate = 1 to avoid the truncation


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ — Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20’
, @attach_query_result_as_file = 1
,@query_no_truncate = 1

Once I opened the xml attachment file I noticed that there are some weird line breaks for about every 256 characters then I realized that I might need to use @query_result_width with default of 256 with max of 32767


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ — Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20’
, @attach_query_result_as_file = 1
,@query_result_width = 32767
,@query_no_truncate = 1

seems to work as my xml output doesnt seem to exceed 32767 characters

 

dead lock graph email attachment

 

actual dead lock graph

Create Table Space and Users in 3 Steps


CREATE TABLESPACE sqljunkie 
logging 
datafile ‘C:\app\oradata\orcl\sqljunkie.dbf’ 
size 32m 
autoextend ON NEXT 32m 
maxsize 2048m extent management local;
CREATE undo TABLESPACE 
sqljunkie_undodatafile ‘C:\app\oradata\orcl\undo.dbf’
size 100M;
CREATE TEMPORARY TABLESPACE sqljunkie_temptempfile 
‘C:\app\oradata\orcl\sqljunkie_temp.dbf’ 
size 32m 
autoextend ON NEXT 32m 
maxsize 2048m extent management local;
CREATE USER sqljunkie 
IDENTIFIED BY sqljunkieDEFAULT 
TABLESPACE sqljunkie 
TEMPORARY TABLESPACE sqljunkie_temp;

Fix: invalid common user or role name ORACLE 12C

In four steps will fix the issue

Error at Command Line:16 Column:13
Error report:
SQL Error: ORA-65096: invalid common user or role name

Determine the current container

SELECT Sys_context (‘USERENV’, ‘CON_NAME’)FROM   dual;
SYS_CONTEXT(‘USERENV’,’CON_NAME’)
——————————————————
CDB$ROOT
List all the pluggable databases

select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDBORCL                        MOUNTED
PDB need to open read write atleast once to inegrate into CDB

ALTER PLUGGABLE DATABASE PDBORCL OPEN READ WRITE;
pluggable database is altered
Now PDB is ready to be used we just need to change the current session

ALTER SESSION SET CONTAINER = PDBORCL
Use this query to make sure you are in right container
SELECT Sys_context (‘USERENV’, ‘CON_NAME’)FROM dual;
 
CREATE USER sqljunkie IDENTIFIED BY sqljunkie DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

user SQLJUNKIE created.


GRANT CREATE PROCEDURE
, CREATE session
, CREATE TABLE
, CREATE type
, CREATE VIEW
, CREATE SYNONYM
, CREATE TRIGGER
, RESOURCE TO sqljunkie;
Once we opened database it runs as its own service we can verify this
lsnctl service modified
 if you are planning to connect using TNSnaming service make sure you have something like below in tnsnames.ora
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
pdb connection
there we go