SQL Services are off but can access and query from other server on the same domain. Why?

Using configuration manager, I disabled the SQL Server Services for a SQL Server 2008 R2 instance. Yet, I can still connect to the instance from its replacement server, a SQL 2019 box, and query the data! Also, the jobs which I disabled are enabled and appear to have been running, i.e. last run time column. On the SQL Server 2019 environment, I am using SSMS 18.5.1.

What is going on??? Locally, I cannot connect. Is there something else that I need to disable? I’d like to understand why this is occurring.

Go to Source
Author: LeSteelBox

How Do I See the List of Users Allowed on a MS SQL Server Database

I want to know the users that are allowed access to a MS SQL Server database. I only want a specific database. Is there a user list hidden somewhere?

What are the ways to go about doing this? Is there an app or through a SQL query?

ANSWER

Use Microsoft SQL Server Management Studio. That is the fastest way to do it. Find the database from the explorer menu on the left-hand side. Expand the folder icon. Go to the Security folder, then click on Users next. That’s it!

If you’re using a different SQL client and you can’t see Security > Users from the GUI. Then your other option is through a SQL query.

This is the query below. More details at this page here: https://www.joseyamut.xyz/2020/08/06/get-user-list-in-a-microsoft-sql-server-database/.

USE <database_name>;
SELECT name AS username,
       type_desc AS type,
       authentication_type_desc AS auth_type
FROM sys.database_principals
ORDER BY type;

Happy user hunting! =)

SSDT debugger error in visual studio by database engine SQL-SERVER 2019

In new futures of Microsoft company remove debugger in SSMS database engine 15.0 and they are called improvement. (-_-)

I tried use debugger in visual studio 2019 by this answer :

How to add the Debug button to SSMS v18?

But i’ve below error when run debugging :

enter image description here

Go to Source
Author: Amirhossein

Getting the exact and closest Search using CONTAINS/CONTAINSTABLE in SQL

I have a table that contains 30 000+ records named as ProductCodes that contains a column ‘Value‘.

I have made a query that’ll return the records matching and containing a passed String using CONTAINS.

Select TOP 5 * from ProductCodes
WHERE CONTAINS(Value,'"pepper*"')

It does return the records contains ‘Pepper’ e.g. Pepper and Bayleaf, Peppermint, Dr. Pepper etc.
I also have an exact record that only contains the exact string, ‘Pepper’ which is displayed in the mid of the results. I only need to select the TOP 5 of the records and also the exacting match of passed string.

I also tried to use the CONTAINSTABLE

SELECT Value, RANK FROM
ProductCodes PC
INNER JOIN
CONTAINSTABLE(ProductCodes,Value,N'pepper') AS KEY_TBL
ON PC.Value = KEY_TBL.[KEY]
ORDER BY RANK DESC

I tried using a WHERE rank = 1000 which I read somewhere that’s the rank of the closest match but as I figured out the rank of the record ‘Pepper’ is 128 and phrases that have more than 1 word match have a higher rank.

I am fairly new to using FREETEXT and Semantics so I am still learning on it. How can I take the exact match and ‘partial’ match in my TOP 5? TIA

edit:

I have added a UNION ALL query where it first selects the equal record and combining it with my CONTAINS query.

SELECT TOP 5 * FROM (
 SELECT TOP 5 * From ProductCodes 
 WHERE Value = 'pepper'
 UNION ALL 
 Select TOP 5 * from ProductCodes
 WHERE CONTAINS(Value,'"pepper*"')
) as Value

Now, I can get the exacting match and partial match but wonder how it’ll make my query slower.

Go to Source
Author: bish-cuit

SQL Server 2019 performance worse than 2012… am I missing something?

We have a SQL Server 2012 server which far outperforms a SQL Server 2019 database on (as far as I can see) the same infrastructure. We are hosting both databases on a cloud platform with the same SLAs. Both have 180GB RAM and 16 processors.

However there are a few key differences.

  1. The 2012 database server is Enterprise, the 2019 is Standard. As far as I know, this shouldn’t make a difference
  2. The 2012 database was restored to the 2019 server and it’s version changed to 150 (2019)
  3. MAXDOP on the 2012 server was 0, 2019 server it is set to 8 as recommended by Microsoft and others
  4. Cost threshold for parallelism = 5 on 2012 server, 20 on 2019 server

Other database settings were not changed, so the following settings are default on 2019, I believe:

  • Legacy Cardinality Estimation = OFF
  • Parameter Sniffing = ON
  • Query Optimiser Fixes = OFF

Mainly the type of queries we do are large complex multi join queries performing updates and inserts, with the occasional small selects from users. We load large files to the database and then process the data in large queries, usually one at a time. In between these large “loads” we have users doing selects on other database tables not being loaded/processed in preparation for future load/process steps. Generally we are getting between 30%-50% performance reductions in processing. I figured this was because of the MAXDOP setting, but altering it to 0 made no difference over a series of runs.

Our major symptom is we are getting lock timeouts when we try to connect to the 2019 server while it is busy processing, whereas the 2012 server still services connections, just very slowly. I was thinking of setting the connection timeout setting on the server to a high amount, however I suspect we still won’t get responses from the server. It’s like it’s blocking all new connections if its even slightly busy.

Are there other things I should try? Are those database settings worth messing around with?

I could dive in further and start looking at DMVs, however this seems to be close to a “like for like” environment upgrade with considerable drops in performance. Just checking there isn’t something else I should check before doing a bigger investigation.

Go to Source
Author: blobbles