TSQL - Who is a sysadmin on this server anyhow?

SELECT  p.name AS [Name] ,
        r.type_desc ,
        r.is_disabled ,
        r.create_date ,
        r.modify_date ,
        r.default_database_name
FROM    sys.server_principals r
        INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
        INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE   r.type = 'R'
        AND r.name = N'sysadmin'

Which is a heck of a lot easier than faffing about clicking things...

What if you've been a fool with log shipping?

And managed to set your primary database as your secondary database, because, when you scripted out the T-SQL to create log shipping, you forgot to change server half way through... (yes, I did it twice today in a bit of a DR scenario).

First, disable the Log Shipping Copy and Log Shipping Restore Jobs on the Primary Server. You won't be able to do this by right clicking on the SQL Agent job and chosing 'disable'. You will if you go into the properties for the job. You won't, at this point, be able to delete the job.

Then, check the msdb secondary log shipping tables.

SELECT *
FROM msdb..log_shipping_secondary_databases

SELECT *
FROM msdb..log_shipping_monitor_secondary

SELECT *
FROM msdb..log_shipping_secondary

Verify that the database(s) is indeed in the list here.

Run the following SQL to delete the erroneous secondary databases

DELETE
FROM msdb..log_shipping_secondary_databases
WHERE secondary_database = 'DBName'

DELETE
FROM msdb..log_shipping_monitor_secondary
WHERE secondary_database = 'DBName'

DELETE
FROM msdb..log_shipping_secondary
WHERE primary_database = 'DBName'

After you have run this script for each database, the LSAlert job will stop erroring.

You will also, now, be able to delete the erroneous LSCopy and LSRestore jobs from SQL Agent.

As always, use at your own risk.

Collation

Collation can be set at all different levels in SQL, from the server, down to the column level. And it's a pain when it does't match up. Fortunately, there is a fast workaround for T-SQL queries.

SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid = b.employeeid

If the collation on employeeid in table a does not match the collation for employeeid in table b, there will be a nasty error, along the lines of "Cannot resolve collation conflict for equal to operation"

Easy enough to deal with - tell SQL to use what *should* be the default for the database(s) in question. This worked when I was dealing with two databases with totally different collations on the same server - the default server collation didn't trickle down to a database imported from another, older, server.

SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid COLLATE DATABASE_DEFAULT = b.employeeid COLLATE DATABASE_DEFAULT

This is a lot simpler to implement than any solution that involves trying to re-collate one of the columns to the other table/database/column's collation. It's vastly more generic.

T-SQL to give domain accounts sysadmin access

For those moments where you can only get access via osql.exe....

CREATE LOGIN [Domain\UserName] FROM WINDOWS;
GO


EXEC master..sp_addsrvrolemember [Domain\UserName],'sysadmin'

GO

Why yes. I did just lock myself out of a server accidentally this morning. Where Management Studio wasn't installed. Go figure.

Execute xp_cmdshell

Now: we don't enable xp_cmdshell lightly, as it allows a degree of control over the operating system that renders the SQL server vulnerable. However, when we do enable it, it can only be executed by members of the sysadmin role. Sometimes, this isn't quite what we want: we only want the account to be able to execute xp_cmdshell, not to be able to muck about with other SQL system settings.

So.

First, make sure that you have SA access on the server yourself. Then make sure the account that you want to use has access to the relevant databases on the SQL server. It can be a SQL user account or a Windows account: it doesn't matter. For the purpose of this snippet, I'm using a SQL account called 'user'. I'm going to go out on a limb, and guess that you already know how to create an SQL user account.

Then, grant the user execute rights on xp_cmdshell

GRANT EXEC ON xp_cmdshell TO [user];

Next, we need a proxy account which does have sysadmin access, and we'll use this account to run xp_cmdshell to run calls by users who are not members of the sysadmin role. This account must be a windows account. Give it access only to those drives and folders which it requires for the purposes of executing anticipated xp_cmdshell calls.

The proxy created is ##xp_cmdshell_proxy_account##, and in order to create it, you need both the Windows Login which has sysadmin access and its password.

EXEC sp_xp_cmdshell_proxy_account ‘domain\sysadminlogin’, ‘password’;

Check that this has created by querying the sys.credentials table

SELECT * FROM sys.credentials

Finally, verify that the SQL user has access to execute xp_cmdshell with the following code

EXECUTE AS LOGIN = 'user'
GO
EXEC xp_cmdshell 'whoami.exe'
REVERT

The result should be the Domain\Sysadmin account specified earlier.

Code Snippet

To execute sp_helprotect on all databases on a server, saving the results into a temporary table, and then displaying the results, including the database name. Makes use of sp_msforeachdb, an undocumented stored procedure which is very nifty for cycling through each database on a server.

It took me a while to work out how to do what I wanted to do...

This code is provided as-is, with no warranties. Test it before using it in earnest: and if you want to share the joy, please credit me. I wouldn't have managed it without the code here.

CREATE TABLE #temptable

(

[Owner] VARCHAR(50),

[Object] VARCHAR(100),

grantee VARCHAR(50),

grantor VARCHAR(50),

protecttype VARCHAR(50),

[Action] VARCHAR(50),

[Column] VARCHAR(50),

dbname VARCHAR(50)

)



DECLARE @STATEMENT VARCHAR(500)



SET @STATEMENT = 'Use [' + '?' + ']
INSERT INTO #temptable
([Owner],
[Object],
Grantee,
Grantor,
ProtectType,
[Action],
[Column]
)
EXEC (''sp_helprotect'')
UPDATE #temptable SET dbname = db_name()
WHERE dbname IS NULL'



EXEC MASTER.dbo.Sp_msforeachdb @command1=@STATEMENT



GO



SELECT *

FROM #temptable

ORDER BY dbname



GO



DROP TABLE #temptable
GO

Self-Blocking SPIDs

My nightly DBCC CheckDB job is failing on the CRM Server, when it checks the MSCRM database. It fails because the SPID is blocking itself: and there's no point in trying to kill the SPID, because that just hangs. The self-blocking SPID also blocks everything else, rendering it almost impossible to stop the Scheduled Job via SSMS, as SQL Agent doesn't load. The self-blocking SPID also renders it impossible to stop SQL via SSMS or SQL Configuration Manager: the only way to stop SQL seems to be by killing the process in Task Manager (and that can't possibly be healthy).

There is, if one checks the sys.dm_os_waiting_tasks DMV, a lot of action going on in tempdb while this process is failing: the blocked resource is blocked on a LCK_M_IX wait_type, which is an intent exclusive lock. The memory involved in the process seems to be rather intensive (the memory used by SQL suddenly skyrockets). The executing SQL that is involved is really quite interesting: and not many people seem to have come across it based on a quick google about the place.

DECLARE @BlobEater VARBINARY(8000)
SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)
FROM { IRowset 0xE093909D00000000 }
GROUP BY ROWSET_COLUMN_FACT_KEY
>> WITH ORDER BY
ROWSET_COLUMN_FACT_KEY,
ROWSET_COLUMN_SLOT_ID,
ROWSET_COLUMN_COMBINED_ID,
ROWSET_COLUMN_FACT_BLOB
OPTION (ORDER GROUP)

So far, I have drawn a complete blank. However, I've decided that trying to increase the size of TempDB might be a sensible thing to do, simply because so much that is being blocked involves tempdb, and tempdb itself was about 8MB in size.

"I can't take this database offline!"

An anguished cry, which I have heard twice today. The problem is, if you try to take a database offline, it will not actually complete until the current transaction has ended (or been forcibly killed), as there is a lock on the database. Killing a transaction is a bit untidy. As we find here "The ALTER DATABASE statement waits indefinitely if there is any lock on the database." One generally ends up with 'Database is in transition...Error 952' when trying to ascertain what the databases is up to, and very little seems to have an effect, including restarting SQL in some cases: and that assumes that restarting SQL is even an option.

Fortunately, if one uses a bit of T-SQL, one can persuade the last transaction to rollback neatly, unlocking everything, and reducing hassle. It's a lot nicer than going KILL SPID.


USE master

GO

ALTER DATABASE databasename

SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

And there we have it. Neatly and tidily takes the database offline, it doesn't hang around in a transactional

OSQL -E

Sometimes, you get a black-box installation by a third party supplier: and, when you politely enquire as to whether you can install SSMS on the server so that you can pull out pertinent information for your SQL Estate Audit (because, naturally, the installation on the server is such that you cannot connect remotely: you just know that the server is there), the answer is no. Grrr.

But not the end of the world. Via the command prompt, we can open a connection to that instance of SQL, and run whatever queries we fancy with a local administrator's account (because, let's face it, if they haven't installed SSMS, and they're a third party supplier, the likelihood is that they haven't disabled the BuiltIn\Administrators group....).

Start off by checking the Services on the Server, to get an indication of what you've got: also check SQL Server Configuration. This will let you know how many instances of SQL are on the server, and whether they have a name other than the default servername. On the server I'm looking at, I've got two instances of SQL Express. I can connect to one by typing the following in the command prompt:

OSQL -E

For the second, I need to be a bit more specific, and type

OSQL -E -S ServerName\InstanceName

Once I've got OSQL running, I can query merrily. Every T-SQL statement that I can run in SSMS or Query Analyzer can be run in OSQL. I can execute stored procedures. I can change databases. I just need to remember that instead of hitting 'F5' to run my statement, I need to end my statement with 'GO', and press enter. It's worthwhile not running 'SELECT *', since the formatting leaves something to be desired, and it's a bit tricky....

So, what might my useful statements be? I've used a selection of the following today

To list my databases
SELECT name
FROM sys.databases
GO

To check authentication mode

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
ELSE 'Unknown'
END
GO

To check recovery mode

SELECT recovery_model_desc
FROM sys.databases
GO

To check collation

SELECT name, collation_name
FROM sys.databases
GO

To check database owner

SELECT name, SUSER_SNAME(owner_sid)FROM sys.databases
GO

To check SQL Version, Windows Version and SQL Service Pack

SELECT @@VERSION
GO
SELECT ServerProperty('ProductLevel')
GO

To check database size

USE dbname
GO
EXEC sp_spaceused
GO

It's pretty easy to find out what you need by searching for the criteria, with an additional search of 'T-SQL' slapped at the front of the string. More additions will be made here as I come to need them, however, this got me what I wanted for my spreadsheet....

Just because

Just because you can use spaces, and inverted commas, and all sorts of other rubbish in your tablenames, doesn't mean that you should.

But what if the FTS won't?

When building a Full Text Search Catalog and Index, it is a Good Idea to check that the search works properly at the end of it, by using something along the following lines:

SELECT *

FROM tablename

WHERE Freetext (COLUMN, 'some text')

Now, I knew the create script worked, as I'd used it to create my Very First Full Text Catalog and Index only a week ago. I knew the select would work, because, again, I'd tested it. SQL 2008 SP1 was about to have a bit of a disagreement with me about what was, and what wasn't, going to work.

Not so.

SQL Server encountered error 0x80070005 while communicating with full-textfilter daemon host (FDHost) process. Make sure that the FDHost process isrunning. To re-start the FDHost process, run the sp_fulltext_service 'restart_all_fdhosts' command or restart the SQL Server instance.


and in the Logs:


SQL Server failed to communicate with filter daemon launch service (Windows error: Windows Error: hr = 0x80070005(failed to retrieve text for this error)). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.

Running sp_fulltext_service 'restart_all_fdhosts' did not improve matters: the command ran happily, but the error persisted. Restarting SQL didn't help either (heck, it's a DEV box, and not yet ready for the Developers).

Searching for 0x80070005 told me that the problem was to do with permissions: and the solution turned out to be setting the SQL Full Text Filter Daemon Launcher Service to run under the same account as the SQL Server Service.

The SELECT statement then deigned to execute: but did not return any results. Rebuilding the index did not help matters: however, dropping the FULLTEXT index, the index, and the Full Text catalog did work. T-SQL here for posterity for next time I have this problem.



DROP FULLTEXT CATALOG CatalogName

DROP FULLTEXT INDEX ON dbo.TableName

DROP INDEX IndexName ON dbo.TableName

Scrap of SQL

How to identify duplicates in a table....


SELECT email,

Count(email) AS numoccurrences

FROM users

GROUP BY email

HAVING (Count(email) > 1)

Of course, what to do with all those duplicates is up to you!