Showing posts with label mssql 2005. Show all posts
Showing posts with label mssql 2005. Show all posts

Monday, November 10, 2014

How To Enable CLR on a Microsoft SQL 2005 Server

A while back I worked for a small hosting firm that focused on Microsoft products. As part of my responsibilities I wrote a great deal of documentation for them for a variety of tasks - some basic, some more advanced and problematic.

Anyway I was pleased to see today that these tutorials are still published on their site. Follow this link, for instance, to read an instructional guide on how to enable CLR with MSSQL 2005.

Thursday, January 3, 2013

Your Daily MSSQL Tips - Display Table Size and Disable Maintenance Plans for MSSQL 2005, MSSQL 2008 and MSSQL 2012

Yeeesh posts have been MSSQL-heavy. I've just been working on this stuff for quite a bit lately - by no means is this my favorite database. Anyway, you're not here for that. You're here because you need to fix something that's broken and Google says I have it. Here you go:

Find Database Table Size

Your database is growing and you need to know why. First step would be to find out which table is responsible for the growth - but how? Right click the database in question and run one of the following queries:

Displays the size of every table - exec sp_Msforeachtable 'exec sp_spaceused ''?'''
Displays a specific table - exec sp_spaceused TableName

Disable a Maintenance Plan

Let's say you want to temporarily disable a maintenance plan. You are trying to troubleshoot it, but you don't want to delete the entire thing and re-create it. You may have noticed SQL Management Studio's GUI has not straight forward way of doing this. First, you need to find the Job ID for the maintenance plan. This can be retrieved from the SQL Server Agent Job Properties window. Alternatively you can run the following query:

SELECT
job_id,name,description,enabled
FROM
msdb.dbo.sysjobs
ORDER BY
name ASC

Select the correct job ID and add it to this query,replacing @PutYourJobIDHere with the ID of your plan : 

DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = 'PutYourJobIDHere'
EXEC msdb.dbo.sp_update_job @job_id = @my_job_id , @enabled = 0

So a correct query would look like this:

DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '8AE8F423-C40F-453B-9B7D-9F632FBAC2FC'
EXEC msdb.dbo.sp_update_job @job_id = @my_job_id , @enabled = 0

When you are ready to enable your plan again, use the same query, changing @enabled = 0 to @enabled = 1

Saturday, December 29, 2012

Remove Single User Mode From Your MSSQL Database

***This guide is valid for MSSQL 2000, MSSQL 2005, MSSQL 2008 and MSSQL 2012. 

Is your database stuck in single user mode? Irritating isn't it? Use the following command to restore it to normal service, where DBNAME is the name of the database:

exec sp_dboption 'DBNAME', 'single user', 'FALSE'
go

MSSQL .NET sysobjects Cheatsheet - Viewing and Creating Sysobjects

The following in formation should be valid for MSSQL 2005 and 2008. Sysobject creation is best handled using the GUI available at $WINDOWS\Microsoft.NET\Framework\$version_number\aspnet_regsql.exe. Under some circumstances, this may not be possible or preferable. In those circumstances, the following queries can assist you.

To view all installed sysobjects for the database, run the following query, replacing DBNAME with the name of the relevant database:

use DBNAME
go
select * from sysobjects where xtype ='P' and name like 'aspnet_%'

Sysobject creation scripts are specific to the object. The script below will create aspnet_SchemaVersions:

CREATE PROCEDURE [dbo].aspnet_CheckSchemaVersion   
@Feature                   nvarchar(128),   
@CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN IF (EXISTS( SELECT  *               
FROM dbo.aspnet_SchemaVersions               
WHERE   Feature = LOWER( @Feature ) AND                       
CompatibleSchemaVersion = @CompatibleSchemaVersion ))       
RETURN 0
RETURN 1
END
GO

Thursday, December 13, 2012

How to Fix MSSQL Error 15023: "User already exists in current database" and Stored Procedures for Preventing MSSQL Orphan Users

Whether you are using MSSQL 2005, MSSQL 2008 or MSSQL 2012, orphaned users are common when restoring a database. It happens like this: you restore the database successfully. Being a thorough and awesome database administrator, you check the user mappings for the owner/primary database user assigned to the database, and see that the user exists in your server instance, but is no longer assigned the correct mappings. You attempt to restore the mappings, but when you do, the MSSQL server responds with the error below

 Error 15023: "User already exists in current database"

Below are a series or commands that can be used to immediately resolve errors like this, followed by a series of stored procedures that can be used in a number of ways to keep your MSSQL instance orphan-free on a more long-term basis.

The scripts below assume a number of variables. Keep these in mind and replace them with the correct values before executing the respective queries. The $ character is here intended as a variable notation only and is not to be included in our queries. These are used to distinguish them from alternative notation such as @fn that will appear later in the article

$DATABASE = the name of your database
$USERNAME = username for $DATABASE
$PASSWORD = password for $USERNAME

Option 1) Right click the database instance name in Object Explorer and hit Run Query. The script below will return all the existing users in the database so that you can confirm the existence of orphans as needed. 

USE $DATABASE
GO
EXEC sp_change_users_login 'Report'
GO

Run the following to associate the database login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist correctly already. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.

USE $DATABASE
GO
EXEC sp_change_users_login 'Auto_Fix', '$USERNAME', NULL, '$PASSWORD'
GO


This query will also associate the login with the username. ‘Update_One’ links the specified user in the current database to the login, however in this instance the login must already exist. User and login must be specified (in this example they match - note that username and login can differ, but really should not during the course of standard administration). Password must be NULL or not specified, but can be reset later.

USE YourDB
GO
EXEC sp_change_users_login 'update_one', '$USERNAME', '$USERNAME'
GO


2) If the account you are authenticated as has the permission to drop other users, run the query below. This will just drop the user, then you can use Object Explorer to go to Security --> Logins --> username and update the user mapping using the GUI. If you're the SA, this is the easiest option.

USE @DATABASE
GO
EXEC sp_dropuser '$USERNAME'
GO

Now that we have a resolution for orphan users in the short term, let's explore some examples of stored procedures that can be used to fix orphan users. These stored procedures can be run as a scheduled task to address this issue on a regular ongoing basis withoutthe need for the DBA to take special action.

Example 1:

This Stored Procedure will fix all the Orphan users in your database by mapping them to usernames that already exist for that user on the server. This stored procedure can be used when user has been created at server level but does not show up as a user in database.

CREATE PROCEDURE dbo.spDBA_FixOrphanUsers
AS
DECLARE @username VARCHAR(25)
DECLARE GetOrphanUsers CURSOR
FOR
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND (sid IS NOT NULL
AND sid <> 0x0)
AND SUSER_SNAME(sid) IS NULL
ORDER BY name
OPEN GetOrphanUsers
FETCH NEXT
FROM GetOrphanUsers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
EXEC sp_changedbowner 'sa'
ELSE
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT
FROM GetOrphanUsers
INTO @username
END
CLOSE GetOrphanUsers
DEALLOCATE GetOrphanUsers
GO


Example 2:

This stored procedure will fix the Orphans in your database by creating a new user on your MSSQL server instance with the same password as the database orphan. Don't forget to change all the passwords once users  have been created

CREATE PROCEDURE dbo.spDBA_FixOrphanUsersPassWord
AS
DECLARE @username VARCHAR(25)
DECLARE @password VARCHAR(25)
DECLARE GetOrphanUsers CURSOR
FOR
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND (sid IS NOT NULL
AND sid <> 0x0)
AND SUSER_SNAME(sid) IS NULL
ORDER BY name
OPEN GetOrphanUsers
FETCH NEXT
FROM GetOrphanUsers
INTO @username
SET @password = @username
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
EXEC sp_changedbowner 'sa'
ELSE
EXEC sp_change_users_login 'Auto_Fix', @username, NULL, @password
FETCH NEXT
FROM GetOrphanUsers
INTO @username
END
CLOSE GetOrphanUsers
DEALLOCATE GetOrphanUsers
GO


Example 3:

Finally, this Stored Procedure will drop all the Orphan users in database. You must recreate them manually if they are needed

CREATE PROCEDURE dbo.spDBA_DropOrphanUsers
AS
DECLARE @username VARCHAR(25)
DECLARE GetOrphanUsers CURSOR
FOR
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND (sid IS NOT NULL
AND sid <> 0x0)
AND SUSER_SNAME(sid) IS NULL
ORDER BY name
OPEN GetOrphanUsers
FETCH NEXT
FROM GetOrphanUsers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
EXEC sp_changedbowner 'sa'
ELSE
EXEC sp_dropuser @username
FETCH NEXT
FROM GetOrphanUsers
INTO @username
END
CLOSE GetOrphanUsers
DEALLOCATE GetOrphanUsers
GO

(Hat tip to Pinal Dave)

Simple T-SQL Script to Kill All Connections to a MSSQL Database


When restoring an MSSQL database, it is common to receive an error similar to this one: 

"The database could not be exclusively locked to perform the operation."
Msg 5030, Level 16, State 12, Line 1 

This error can occur in MSSQL 2005, 2008 or 2012.
There are a number of workarounds for this. Some will tell you to disable the website's application pool in IIS, but this doesnt help if someone remotely is connected using SQL Management Studio. You can go into Performance Monitor and manually kill each process, but often the processes jump up faster than you can disable them by hand. 
Where that is the case, perform the following procedure. Assuming your restore job task window is properly configured and just open, keep it open. You can right click on the database server and select New Query without closing the restore job task window, which wastes time when you must recreate it. Then, enter the following script into the query menu (be sure to update the variable @DB_NAME with the actual name of your database). This script will sever all connections immediately. You can then left click on your restore job task window and click OK to retry the restore. Assuming the restore task is correctly configured, it will complete without further issue. 

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = '@DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

(hat tip to Adam Lacey)

How To Fix MSSQL Error 3219

You've done everything right with your MSSQL database restore. All of your versions match. All connections to the database have been killed. Your BAK file is valid, you have adequate permissions to both it and the proposed MDF and LDF file paths. And yet, when you attempt to restore using MSSQL 2005 or 2008, the task fails with the following:

The file or filegroup X cannot be selected for this operation. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3219)

Often the most infuriating problems are the one's with the easiest solution. To fix this, simply perform "Restore Database" instead of "Restore Files".

RAT Bastard

Earlier this week, several servers I maintain were targeted by automated attempts to upload a remote access trojan (RAT). The RAT is a simpl...