Dev: We are getting an error: SELECT permission was denied on the object MyObject.
Me: Ok, what instance, database, and account is getting the error?
Dev: The instance is InstanceA and we think the database is DatabaseA but we have no idea what the service account is.

Now, watch this.

-- Source: https://www.sqlservercentral.com/blogs/identifying-failed-queries-with-extended-events
--- Create Xevent
------ Be sure to change the filename and metadatafile
CREATE EVENT SESSION [FailedQueries] ON SERVER 
ADD EVENT sqlserver.error_reported 
	(ACTION(sqlserver.client_app_name, sqlserver.client_hostname,  
		sqlserver.database_name, sqlserver.sql_text, sqlserver.username
		--, sqlserver.tsql_stack /*include this if needed*/
		) 
	WHERE ([package0].[greater_than_int64]([severity], (10)))) 
ADD TARGET package0.event_file (SET 
	filename = N'E:\Sql_EE_FailedQueries.xel'
	,metadatafile = N'E:\Sql_EE_FailedQueries.xem'
	,max_file_size = (5)
	,max_rollover_files = (10))
WITH (STARTUP_STATE = OFF) -- Change to ON if you want this to start when SQL starts.
GO
--- Start Xevent
ALTER EVENT SESSION [FailedQueries] ON SERVER 
STATE = START;
GO

Now I open up a window to watch live data and have the user attempt to run their code:

And now I have confirmed that the database name is Kenneth_Test, the user is UserA and I even have the exact command they ran that is giving them an error. More than enough to solve the problem.

Of course there are a lot of other possible problems you might use this for, and on a stable production system I might even consider setting this up with an alert to send me any errors that are occurring. That way I can easily see if some new code has been installed that is having a problem, or even possibly if someone is trying to work out how to do something malicious.

I’ve got some additional pieces of code related to this on my GitHub. A script to turn off the session and another one to read information that’s already in the file the session is writing to. And of course you can get most if not all of this from the original poster (link above).

DECLARE @IntA INT = 1, @IntB INT = 2;
SELECT 1.0*@IntA/@IntB;

With the expectation that you would get back a decimal. Did you wonder why it converted the integers to decimals rather than the other way around? If not you probably should have. I’ll give you a hint. It’s not because it’s the first value in the equation, although that does make a difference.

Thought about it? In case you didn’t figure it out T-SQL (and again, I’m going to say all programing languages where implicit conversions are allowed) has a conversion precedence order. In other words in any calculation where an implicit conversion is required the datatype with a value higher on the list is converted to the datatype with the lower value on the list. So for example:

8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint

A tinyint will be converted to any of the other types of int datatypes and of course int will be converted to decimal. Will time will be converted to date? No, because that particular implicit conversion isn’t allowed. This list only applies to implicit conversions that are possible.

You probably don’t need to memorize the full list (found in the link above) but you should probably know the more common conversions.

Now, where I said above that the decimal being first in the equation does matter. Why does it matter? The implicit conversion happens with the order of operations. 1.0*@IntA is 1.0 then divide that by @IntB gives you 0.5. If you reverse it and do @IntA/@IntB*1.0 then you get @IntA/@IntB is 0 then multiply by 1.0 is 0.0. Not hard on a simple calculation but if you have something more complex you might need to think it through a bit.

Last but not least I want to point out a few outliers.

1. user-defined data types (highest)
2. sql_variant

• …..

30. binary

Nothing will be implicitly converted to binary. It’s last on the list. sql_varient is weird and I’ll handle it in it’s own post. And first but not least, user-defined data types took me a minute to understand. If you are just aliasing a default data type it just uses the default data type. Like this:

CREATE TYPE IntPrecedence FROM int NOT NULL; 
GO
DECLARE @Test IntPrecedence = 3;
PRINT @Test/2.0;

This still returns 1.5. However if you if you create a true user defined data type using CLR then it will probably act like it is truly the top of the list. Unfortunately I don’t have the skill to create a datatype like this, and I couldn’t find an example to share either. So I’m just making an assumption but let’s call it an educated guess.

In this particular case the command that caused the interesting results was this:

CREATE SERVER ROLE [ThisIsAServerRole];

In this case SQL created a server principal (a login). I’m not going to demo this since I’ve done similar demos in the other two posts. What I do want to talk about is why this keeps happening. If I had to guess it’s very similar to some user processes I’ve worked on. So for example, let’s say you are importing sales data. The first thing you do is get the SalesPersonId for each of the sales. But there is a new salesperson listed that you don’t have in your SalesPeople table. So you have a side path in the process that says Load any missing salespeople first. Now you can get SalesPersonId, then load the sales data. This is basically the same thing. When you create the server role (or in the previous cases a table, or an audit) and you don’t have sufficient information (in this case a principal_id to own the role) SQL has to create one. An easy way to prove this is if you do this a principal won’t be created.

CREATE SERVER ROLE [ThisIsAServerRole] AUTHORIZATION [MyADGroup];

But Ken, I hear you ask, why can’t SQL just use the AD group automatically rather than create a new, probably unwanted, server principal? Well, I just have to ask, which one? I’m a member of dozens, at least 6 of them have permissions on this instance. Which one should it use? The first one? But what if that’s the wrong one? Say the AllDevs group. Now all of the developers have access instead of just the DBAs. So yea, SQL stores metadata as if it’s data and they treat it just as carefully as you treat yours. (Probably more carefully than some of you .)

1/2 returns 0 because 1 and 2 are both integers. When you divide two integers you get back an integer. Now, we, as human beings, know that what you probably wanted was to get back 0.5. Unfortunately, computers really aren’t all that smart and we have to be very explicit with them. So far all you’ve told it is that you want back the integer portion of 0.5, or 0. If you’ve been programming for a while you’ve run across this before and have very likely solved it by doing something like this:

DECLARE @IntA INT = 1, @IntB INT = 2;
SELECT 1.0*@IntA/@IntB ;

Because you are multiplying by a decimal (1.0) SQL Server is going to implicitly change the datatype to decimal. You could do something like this instead:

DECLARE @IntA INT = 1, @IntB INT = 2;
SELECT CAST(@IntA AS decimal (10,2)) / CAST(@IntB AS decimal (10,2)) ;

In this case you are telling SQL to do an explicit datatype conversion. SQL converts 1 and 2 to 1.00 and 2.00 (the CAST command) and then divides them. Since you specified that they are both decimals your output is also decimal and you get your 0.5.

FYI, this doesn’t just happen with math. A varchar containing a number could be converted to one of the numeric data types. A char could be converted to a varchar, etc. There are lots of different datatypes and some can be converted between each other, and some can’t. There is a graphic that tells you what can be converted and what can’t.

The only thing I’m not overly thrilled with about it is that where it says the little orange circle means Implicit conversion it should say Implicit OR explicit conversion. In other words if you can do an implicit conversion, you can do it explicitly too.

If you want to read more about Data type conversion here is a link to Microsoft’s online documentation on the subject.

I want to start this by saying this is a moderately advanced book. The information in it ranges from 300 on up, but I don’t consider that unreasonable for an optimization book. If you haven’t been writing code for a while you won’t really appreciate everything in it. That said, if you are a junior when it comes to your SQL skills, you’ll still get a lot out of this, just make a point of re-reading after you get a bit more experience.

The book started with what for me was a fair amount of review material. How to get execution plans, what’s in them, STATISTICS TIME and IO, trace vs profiler, etc. I have to tell you I loved it. Reviewing the basics is a great way to start building on them and helps firm them up in your memory.

From there Benjamin moved on to collecting information in more detail using DMVs, DMFs, traces, extended events and even covered the data collector. I’ve used the data collector exactly once. It’s not overly popular. So why would Benjamin cover it? Well, I can’t speak for him, but I can tell you in my opinion it really helps to know what’s available in breadth if not in detail. That way if something comes up you know where you can start digging into and aren’t just failing around.

Benjamin also does a fair amount of internals (which is where the 300 and up comes from). He talks about how the optimizer works, different types of operators, statistics, query hints and lots more. I could wish for more information on Query Store but to be fair he does mention that he goes into more detail in his book High-Performance SQL Server.

So over all what do I think over all? Well, let’s just say, that having gotten a free copy of the e-book to review, having read the book, I’m now going to spend money (and I’m really cheap) on buying a hardcopy.

I’ll be honest, I’ve never used it for that though. My favorite use is to create that schema only database. In this case they wanted a set of staging databases, but I’ve also used it to create/re-create a testing environment with no data.

But Ken, why don’t you just script out the database and run the script in a newly created database? Well, I certainly could, and in some cases I’ve had to. There are a couple of down sides to this though. It’s a lot more labor intensive. Creating the script (usually through the GUI), creating the database, and then running it can be time consuming. And I have to tell you, in some of my older databases, that script won’t run cleanly. For example, older stored procedures (that obviously haven’t been tested recently) return an error because the table structures they reference have changed and they are referencing a column that isn’t there anymore.

At that point, unless I want to create the required columns, create the stored procedures, and then drop the columns (and then probably carefully compare everything to make sure I didn’t miss anything) I only have one other choice. I can restore the database, truncate all of the tables, and then shrink it to the size I want. Extremely time consuming if it’s a larger database and a pain in the neck regardless.

Which brings us back to DBCC CLONEDATABASE. (FYI DBATools’ Invoke-DbaDbClone is basically a PowerShell interface to DBCC CLONEDATABASE). This is incredibly easy to use, and fairly quick too.

DBCC CLONEDATABASE (SourceDBName, NewDBName) WITH VERIFY_CLONEDB; 

The documentation says you need to use VERIFY_CLONEDB clause if you plan to use this in production. I’m guessing in production means anything permanent such as a test database or even dev database. It also tells the command to exclude statistics and query store information.

Now, as much as I love this command, it doesn’t always work. If your model database has any user objects in it then it’s going to fail. Also if the source database is old enough you might get some failures as well. This is because (as far as I know) DBCC CLONEDATABASE operates by copying the model database (that’s how new databases are created in general) and then behind the scenes copying over all of the object information by copying the data in the system tables. Of course those system tables have primary keys and if there are duplicates (i.e. a object in model has the same object_id as one of the new objects) then you are going to get a primary key error.

Of course this only works if the source and destination are on the same instance, so if I need them somewhere else I’m still going to have to copy the database (either by detaching and moving, or backup and restore) but that’s still a lot faster with a database with no data in it.

EXECUTE AS User='Domain/NetworkName';
SELECT Col1, Col2 FROM SynonymName;
-- SynonymName == OtherDB.dbo.TableName

Msg 916, Level 14, State 1, Line 3
The server principal “Domain/NetworkName” is not able to access the database “OtherDB” under the current security context.

Domain/NetworkName has access to SynonymName, access to OtherDB, and access to TableName. So why are we getting an error? And it’s not really what you would expect for an access denied error either.

The problem is that they were impersonating a database level principal (a user). That means you have access to their permissions within that database.

Notice that you can go from LoginA to either DB1 or DB2, but not directly from DB1 to DB2 or back. If you are impersonating UserA you only have access to DB1. Same with UserB and DB2. However, if you are impersonating LoginA then you can access both DB1 and DB2 since LoginA maps to both UserA and UserB. So if for whatever reason you need to get to multiple databases while impersonating a different Id make sure you are using EXECUTE LOGIN. If you only need access to a single database then you can really use either. Unless of course you want it to fail when it unexpectedly tries to access another database. And yes, I’ve used EXECUTE USER for that specific reason before.

I use this all of the time. I have a solution in SSMS to store all of the queries I use on a regular basis. I open one of the queries and then pin it to the top to keep it sperate from the rest of my sometimes literally dozens of queries. Things like my query to figure out where the backups for this instance are, and my diagnostic queries are almost always up there at the top left. So I open the file, pin the file. A few days later when I end up re-opening SSMS I open the file, and I pin the file. Not hard, but rather repetitive.

The other day though I found a fantastic option.

Right there under the Options->Tabs and Windows->Pinned Tabs section is Maintain pin status if document is removed from the well. From what I can tell the well is the area were the tabs are shown. And now that I’ve checked that option any time I open up one of those files (that I’ve pinned before) it now automatically shows up in the separate row for pinned tabs. (Note that this option appears to be available in Visual Studio but I haven’t been able to find it in Azure Data Studio.)

I mean I’ll admit this isn’t some kind of revolutionary thing, but I’m sure finding it handy.

I just had someone ask me this question. It’s an interesting question and I had absolutely no clue, so I did a bit of research. It can be done. There are probably other ways to do this (all code related) but here’s the one I came up with.

CREATE TABLE #test (Id INT NOT NULL IDENTITY(1,1));
DECLARE @TableName nvarchar(4000);
BEGIN TRY
	SET IDENTITY_INSERT #test ON;
	PRINT 'Identity_Insert is not turned on.';
END TRY
BEGIN CATCH
	SET @TableName = SUBSTRING(ERROR_MESSAGE(),42,9999);
	SET @TableName = SUBSTRING(@TableName,1,CHARINDEX('''',@TableName)-1);
	PRINT @TableName;
END CATCH
DROP TABLE #test;

Basically the answer is in the error. If you don’t get an error it’s not turned on, and if you do the error says the table name it’s on on. (That sounded a bit weird hu?) A little bit of parsing and you have your answer. I created a temp table specifically for the purpose so the code is generic and can be run on any database, and so the answer is a guaranteed no if I don’t get an error.

All of that said, and it doesn’t really matter. IDENTITY_INSERT is generally used in ad hoc code and when it isn’t it probably isn’t going to be a big piece of application code. If it’s ad hoc then you’re sitting right there and can see the error. Just turn it off on the table it’s turned on on (yea, still sounds funny) and move on. If it’s part of a piece of code then the fact that you’re seeing an error means that you didn’t turn it off after using it and you need to fix your code. (If it wasn’t clear, you should turn off IDENTITY_INSERT when you are done with it.)

Also, IDENTITY_INSERT is session specific. If you’re having a problem then re-connecting your session would reset it no matter what. Also the fact that it’s session specific means it’s not possible for Bob the Jr DBA to leave IDENTITY_INSERT turned on somewhere and block Sarah the database dev from being able to get her work done. The only person it’s affecting is you.

This is the idea that you can pull metadata information from all over the place. Want to know when a database was last used but don’t have an extended events session in place? Check for audit columns (i.e. a column that tells you when the row was created). It’s not perfect, but it might get you close. Or how about the query cache? You can mine that for any non-maintenance queries applied to a database. Want to know when a user last logged in? The error log can give you that!

SQL Server has information all over the place. No, there isn’t an audit automatically created to tell you the last time Bob queried the table ISawASquirrel but with some creative use of the information you do have available you might be able to give the person asking the question some sort of answer. And a lot of times they are happier with that than nothing.