Skip to main content

Posts

Powershell manage sql server role

The following script check if some AD  users are a member of local group, what sqlserver agent group do they have, and grant the group the role. foreach ($sqlserver in "servername1","servername2") { echo $sqlserver invoke-command -computername $sqlserver  -scriptblock {get-localgroupmember -name TCCSDBwriter*|format-list -property name|findstr /I "vaketi amohamme dmorad"} &sqlcmd -S $sqlserver -i query_agentjob_role.sql -Y 40 &sqlcmd -S $sqlserver -i grant_agentjob_role.sql } The query_agentjob_role.sql: SELECT DP1.name AS DatabaseRoleName,       isnull (DP2.name, 'No members') AS DatabaseUserName     FROM msdb.sys.database_role_members AS DRM    RIGHT OUTER JOIN msdb.sys.database_principals AS DP1      ON DRM.role_principal_id = DP1.principal_id    LEFT OUTER JOIN msdb.sys.database_principals AS DP2      ON DRM.member_principal_id = DP2.principal_...

If you forget your wifi password

 C:\>netsh wlan show profile YourWifiNetwordName(SSID name) key=clear Profile YourWifiNetwordName  on interface Wi-Fi: ======================================================================= Applied: All User Profile Profile information -------------------     Version                : 1     Type                   : Wireless LAN     Name                   : YourWifiNetwordName      Control options        :         Connection mode    : Connect automatically         Network broadcast  : Connect only if this network is broadcasting         AutoSwitch         : Do not switch to other networks         MAC Randomization  : Disabled Connectivity settings ---...

Case study of SQL Server plan guide with SQL and Procedure

A application run a query which sometimes slow on one literal but fast on the other, this is the case study of SQL Server plan guide feature: Slow query : use 255 as literal, run duration is ~ 1 minute SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 255 AND md.functionnum = 45000 and rh.remark like 'http%'))  Fast query : use 167 or 66 as literal, complete in less than 1 second SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 167 AND md.functionnum = 45000 and rh.remark like 'http%'))  Thanks to this book : ou...