This should get you started:

SELECT 'REVOKE '

+ P.[permission_name] COLLATE DATABASE_DEFAULT + ' ON '

+ QUOTENAME(S.name COLLATE DATABASE_DEFAULT)

+ '.' + QUOTENAME(C.name COLLATE DATABASE_DEFAULT)

+ ' TO ' + QUOTENAME(U.name COLLATE DATABASE_DEFAULT) +...

This will not help the OP but he may want to consider it in future.

I have a non-production server where the users have a habit of dropping DBs!

I have saved...

Despite using this forum for some time you still do not seem to know how to ask a question.

Please read the following:

http://www.sqlservercentral.com/blogs/spaghettidba/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

You have been asked a number of questions and...

I am currently blocked from looking at the query plan but would make the following points.

The basic problem with queries involving remote tables is that the full table will be...

This may or may not help.

We have an ancient third party application which was updated a few months ago. The update managed to get rid of column permissions.

Running the following...

1. You do do not want a conditional join as it will kill the query plan.

You could look into creating a VIEW of the first query and then joining the...

If you need to send an image to HTML, XML etc you have to use some sort of string encoding. Here is an example of Base64:

http://blog.falafel.com/t-sql-easy-base64-encoding-and-decoding/

Just found this link:

http://db-pub.com/forum-80545268/sql-server-replication-from-sql-server-2008-to-sql-server-2014.html

My experience is that setting the compatabilty level usually works.

If you are not updating the 2008R2 DBs from 2014 you may also want to look at transactional replication as linked...

All those outer joins in sub-queries are enough to confuse anyone.

Try something like:

SELECT C.ReportName, E.ShortName AS Consultant

FROM Clients C

LEFT JOIN Consultants E

ON C.ConsultantRef = E.ConsultantRef

WHERE NOT EXISTS

(

SELECT 1

FROM Policies P

WHERE...

It sounds as though the SAN backup knows nothing about SQL Server so will not be able to backup a database in a consistant state.

I suspect you will probably need...

I suspect your SAN backup is only taking incremental changes.

1. The backups might work especially if you can see a device_type of 7 in msdb.dbo.backupmediafamily.

2. How often are the SAN...

It might also be worth checking the dbo.backup... and dbo.restore... tables in MSDB.

Glad your problem is sorted.

As Joe mentioned, general lookup tables are a bad, if seemingly common, idea although I have never been unfortunate enough to come across a recursive general...

Your test data should be consumable. (ie CREATE TABLE... INSERT ...)

I suspect your specific problem can be resolved by checking the parent is a folder:

FROM

dbCodeLookup AS dcl

INNER JOIN Folders AS...