Forum Widgets
Latest Discussions
Cleanup of auto created statistics
Hello everyone, during annual database checkup i found that there were a lot of auto created statistics, which weren't used. It seems that DBMS doesn't cleanup unused auto created statistic on his own. i guess i can drop them with the following query: DROP STATISTICS Schema.TableName._WA_Sys_XXXXXXXXXXXXXXXXXXXX But i am still thinking whether it is safe, about impact on the performance. Has anybody had any experience with that?SolvedKarolFerekDec 13, 2024Copper Contributor337Views0likes8CommentsCatch Error in a SQL Server Logon Trigger
I have written a Logon trigger in a SQL Server to control logon authorization. ALTER TRIGGER [LOG_TRG_01] ON ALL SERVER WITH EXECUTE AS 'SA' FOR LOGON AS BEGIN if ORIGINAL_LOGIN() = 'sa' begin return; end; if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1' begin -- not allowed to logon rollback; end; -- Insert Trail EXEC [OmegaCoreAudit].[OMEGACA].[P_ACC_UNF_TRAIL] 0, 'trail_details', 'ip', 'server', 'db', 0 ; END GO It does (as expected): OMEGACAEVDEV1 is not allowed to logon OMEGACAEVDEV2 is allowed to logon An audit event is inserted by proc P_ACC_UNF_TRAIL in a table for both users. All three above I want to stay this way ! But I need to have error handling in it, so that in case of whatever error the login is allowed to login - but keeping a record on another table named SYS_ERROR_LOG (with error details). In this trigger I have intentionally provoked an error by "select 10/0" The new trigger is: ALTER TRIGGER [LOG_TRG_02] ON ALL SERVER WITH EXECUTE AS 'SA' FOR LOGON AS BEGIN BEGIN TRY if ORIGINAL_LOGIN() = 'sa' begin return; end; --provoke error select 10/0; if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1' begin -- not allowed to logon rollback; end; -- Insert Trail EXEC [OmegaCoreAudit].[OMEGACA].[P_ACC_UNF_TRAIL] 0, 'trail_details', 'ip', 'server', 'db', 0 ; END TRY BEGIN CATCH Insert into OmegaCoreAudit.OMEGACA.SYS_ERROR_LOG ([LOGIN_USER_NAME], [DB_USER_NAME], [USERHOST], [IP_ADDRESS], [OS_USER], [BG_JOB_ID], [ERROR_DATA]) values ('LOGIN_NAME', 'USER_NAME', 'USER_HOST', 'IP', NULL, NULL, 'ERROR_MESSAGE'); END CATCH END GO In the above code "if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1'" represents a simplified version of a wider authorization process. Problem: CATCH is not working. Both users are not allowed to logon ("Logon failed for login '[user]' due to trigger execution") No record is written on table SYS_ERROR_LOG. I was expecting one for each user. What can I do to fix this problem? best regards AltinSolvedakaraulliDec 05, 2024Brass Contributor383Views0likes13Comments
SQL Server Management Studio
Hello, I am new to Sql Express. (2022) So I have a few questions that would help me along, thank you. I am programming in Visual Studio 2022. 1. Is it imperitive that I use SQL Server Management Studio to create MDF in SQLEXPRESS DATABASE ENGINE? OR, just use VS2022? 2. Do I need to utilise SQL Server Management Studio to create a Report Server Project (and have 'report run' feature functionality) OR, just use Visual Studio 2022? 3. Do I need to 'buy' a Server Certificate? I have been using localdb and creating reports using a Report Server Project. But of course I can not get them to run under localdb. I have read and tried everything that I can. Besides creating a SqlExpress database server, I only managed to allow open the correct port in Microsoft Firewall Defender. Regarding permissions, certificates ect..., I am lost. Any help and guidance is appreciated. Many thanks, Shane1961SolvedShane1961Jun 25, 2024Copper Contributor812Views0likes4Comments
login failed for user sa sql server 2022 on windows server 2022
i got the login failed error for user sa, but not always so I went to server properties - security - server authentication change to windows authentication mode and change again to sql server and windows authentication mode, and restart sql server. this happened when i upgraded to sql 2022, I have Service Pack 13 installed, can anyone help?SolvedAman_SusantoJun 24, 2024Copper Contributor2.7KViews0likes4Comments- HarshaChilakalaMay 06, 2024Copper Contributor499Views0likes3Comments
Select from sys.fn_get_audit_file without CONTROL SERVER
Hi We are pulling MS SQL Server audit records using sys.fn_get_audit_file Server Login MYUSER Not mapped to any non-system database Related DB User on database "master" is: myuser Granted CONTROL SERVER and (of course) Connect Can execute: select * from sys.fn_get_audit_file(<path>, default, default) Problem: Granting CONTROL SERVER to a user that needs to only SELECT the audit records - and doing nothing else - looks too much. So we tried: Revoke CONTROL SERVER form Server Login MYUSER On database "master", grant DB User “myuser” SELECT on sys.fn_get_audit_file Now the Server Login MYUSER cannot execute: select * from sys.fn_get_audit_file(<path>, default, default) Error Message: Msg 300, Level 14, State 1, Line 1 CONTROL SERVER permission was denied on object 'server', database 'master'. Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action. Question: Is it possible to SELECT from sys.fn_get_audit_file without having the CONTROL SERVER ? best regards AltinSolvedakaraulliFeb 29, 2024Brass Contributor1.1KViews1like6Comments
Table Hint Position in From Clause
The documentation (https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver16) for the FROM clause syntax shows the table hints after the table alias. I have recently come across a few queries in which the developer put a nolock hint between the table name and the alias, e.g., FROM dbo.Customer (nolock) cst. No error is thrown. I do not have access to view query plans. Do table hints placed between the table name and alias do anything? Thank you.SolvedMister_GregoryJan 02, 2024Copper Contributor504Views0likes2Comments
is there a view i can use if my sql agent job history doesnt go back far enough
hi we know we gad a sql agent job go down but our view history doesnt go back far enough. is there a view i can use as an alternative that might tell me about the error, date etc.Solveddb042190_foradfDec 05, 2023Copper Contributor470Views0likes1CommentQuestion on creating SQL objects "without using the built in tools".
Hi everyone! I am preparing for to take the SQL exam 70-461. In the study guide, I find the following phrase very confusing: "Create tables (and views) without using the built in tools". Could anyone kindly clarify what this means? I intrepret this to mean creating tables and views using SQL scripts. An https://www.youtube.com/watch?v=XbLwmUJMbPc, however, shows creating tables and views using SQL Server's menu options. Any help would be very much appreciated!SolvedDaniel SchperbergAug 30, 2017Copper Contributor2.5KViews0likes2Comments
Resources
Tags
- Business Apps6 Topics
- sql server3 Topics
- Sql Enterprise1 Topic
- Question Format1 Topic
- change tracking1 Topic
- sql 20161 Topic
- ssis1 Topic
- partitioning1 Topic
- SQL Server 2019 Express1 Topic
- Install1 Topic