If you need to reset your saved your credentials to access the share folder from your PC you can delete those credentials by opening a command prompt running as administrator.
Then type the command:
rundll32.exe keymgr.dll, KRShowKeyMgr
You can then delete the stored credentials used to authenticate the share folder path on your local pc.
You can then try to access that share folder path and it should prompt you for the account user name and password again.
Problem: SSIS Support Developer could not access SSIS packages in SQL Server 2008R2 Instance.
- Type in “Component Services” in search bar
- Open Component Services
- Expand Component Services -> Computers -> My Computer -> DCOM Config
- Scroll down to MsDtsserver100 and right-click Properties
Select the Security tab
Click edit button for Launch and Activation Permissions and add permissions, same for Access Permissions
Next, Right-click My Computer>Manage>Configuration>Local Users & Groups
Look for the Distributed COM Users group and add the account.
Finally, restart the SSIS service
Download and install LogParser from here:
Then create a text file query.sql containing your query
replace_str(replace_str(to_lowercase(cs-username),'0#.f|ldapmember|',''),'0#.w|','') as username
WHERE cs-username > ''
AND date >= '2017-01-01'
Then execute the following command
C:\Program Files (x86)\Log Parser 2.2\logparser file:"query.sql" -i:IISW3C -o:CSV -recurse > c:\temp\usernames.csv
(note: path to LogParser may vary on your system, it may also be handy to add it to the %PATH%)
Learn more about LogParser here:
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to ‘LogonUserW’ failed with error code: ‘1385’.
1385 = Logon failure: the user has not been granted the requested logon type at this computer.
- Look up the Windows user that is assigned to credential [##xp_cmdshell_proxy_account##]
- Run secpol.msc
- Navigate to “Local Policies” -> “User Rights Assignment” -> “Log on as a batch job”
- Add the user found from step 1 to “Log on as a batch job”
- Test by executing sql stored procedure: “EXEC master..xp_cmdshell ‘whoami'”
Troubleshooting xp_cmdshell failures
SQL “PRINT” statements getting buffered and not displaying until buffer is flushed with batch is done or gets full.
Using a RAISERROR with severity of 0 and “WITH NOWAIT” will not interrupt the batch, but will immediately display the output.
Here’s an example keeping it to one line and including a timestamp…
–for first msg in batch
DECLARE @msg nvarchar(2044) = convert(varchar(20),current_timestamp,120) + ‘ – Your First Message Here’; RAISERROR(@msg, 0, 1) WITH NOWAIT;
–for rest of msgs in batch
SET @msg = convert(varchar(20),current_timestamp,120) + ‘ – Subsequent Messages Here’; RAISERROR(@msg, 0, 1) WITH NOWAIT;
If you have SSRS files filling your temp drive, look for the RSTempFiles.
C:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVERReporting ServicesRSTempFiles
You can move this folder to a different drive by copying the RSTempFiles folder to the target drive (for this example “R:RSTempFiles”).
Make sure that the SSRS service account alias has full permissions on the folder
Default local machine service account alias:
Locate the rsreportserver.config file
C:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVERReporting ServicesReportServer
In this file Located the <Service> tag and add entry for FileShareStorageLocation with new path:
After saving the file restart the SSRS instance. It should start using the new location.
For more info about the files stored in this folder:
IF OBJECT_ID(‘dbo.sp_hadr_isprimary’,’P’) IS NULL
EXEC (‘CREATE PROCEDURE dbo.sp_hadr_isprimary AS PRINT 1;’);
–return 1=current instance is primary, 0=current not primary
ALTER PROCEDURE dbo.sp_hadr_isprimary
( @agname sysname = ‘AG_GROUP_NAME’ )
DECLARE @PrimaryReplica sysname
, @ThisReplica sysname
, @retval int = 0;
SET NOCOUNT ON;
SET @ThisReplica = cast(ServerProperty(‘ServerName’) as sysname);
SELECT @PrimaryReplica = hags.primary_replica
FROM sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag
ON ag.group_id = hags.group_id
WHERE ag.name = @agname;
IF UPPER(@PrimaryReplica) = UPPER(@ThisReplica)
SET @retval = 1
SELECT @retval “IsPrimary”
, @agname “AG_Name”
, @PrimaryReplica “PrimaryReplica”
, @ThisReplica “ThisReplica”
declare @retval int
exec @retval = master.dbo.sp_hadr_isprimary
SELECT @retval “return_value”
To start SQL Server and have it only recover the master database and leave all others offline, can use this command:
SQLServr.exe -T3608 -T3609
Also, if you have a named instance, will need to include the -s parameter with the instance name.
SQLServr.exe -T3608 -T3609 -sINSTNAME
–execute as login = ‘sa’;
–execute as user = ‘guest’;
–current context / execute as
, user “user” –same as user_name()
, user_name() “user_name”
, current_user “current_user”
, session_user “session_user”
–current login unless execute as
, system_user “system_user”
, suser_name() “suser_name”
, ORIGINAL_LOGIN() “original_login”
Most of the time it is better to have separate insert/update/delete triggers if you need to do different things based on the DML type. If you did want to have common code then could call a stored procedure with the DML type as a parameter.
In the rare instances where it may be more practical to have one trigger to rule them all, here is an example of how to check for the type of DML activity of the transaction executing the trigger.
ALTER TRIGGER dbo.tr_test_iud
DECLARE @trgtype varchar(10)
SET NOCOUNT ON;
IF EXISTS (SELECT TOP 1 1 FROM inserted)
IF EXISTS (SELECT TOP 1 1 FROM deleted) SET @trgtype = ‘UPDATE’
ELSE SET @trgtype = ‘INSERT’
ELSE SET @trgtype = ‘DELETE’
Keep in mind also, it is best practice to always code for there being more than one record involved in the transaction resulting in multiple records in the insert/deleted tables.