Email Article To a Friend View Printable Version

SQL Server 2005 Login Error 18452

SQL Server 2005

If you are trying to connect to SQL Server 2005 with a SQL Server User Login and receive this error:

Login failed for user 'RptSvcUser'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

Remember to check that the servers authentication is set to "SQL Server and Windows Authentication mode". You can check this in the Security page of the Server Properties.

By default SQL Server 2005 likes to install in Windows Authentication only mode, which will prevent any SQL Logins from connecting. SQL Server will still allow you to create new SQL Logins and they will appear enabled in Management Studio, which can be a bit confusing.

MS Knowledge Base Article 555332 should be linked to from http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz




Email Article To a Friend View Printable Version

SQL Server 2005 Database Diagram Compatibility Requirements

SQL Server 2005

While producing a report for another team today, I wanted to build a database diagram to see the relationships in the source database. As I was working with a local copy of the database I decided to build a Database Diagram with Management Studio, but I received this error:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

After checking the database did indeed have a valid owner and detaching/attaching the database, I was still receiving the error. After a few minutes I remembered that the original database was on SQL Server 2000.

When restoring a SQL Server 2000 database onto a SQL Server 2005 database, the internal objects are updated, but the Compatibility Level is left at SQL Server 2000 (80). Once I'd changed the compatibility of the database to SQL Server 2005 (90) level the Database Diagram tools began to work.

In Management Studio, you can view/change the Compatibility level of your database in the the Options group of the Database Properties:

You can check the compatibility level of your databases with:

SELECT name, compatibility_level FROM sys.databases

And you can upgrade a database compatibility level with this code:


USE [master]
GO
DECLARE @dbname sysname
SET @dbname = N'your database name'
IF EXISTS (SELECT * FROM sys.databases WHERE compatibility_level != 90 AND NAME = @dbname)
EXEC dbo.sp_dbcmptlevel @dbname=@dbname, @new_cmptlevel=90
GO



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server 2005 List all IDENTITY columns

SQL Server 2005

Here is a simple bit of T-SQL that will display the names of all the IDENTITY columns in a database:

SELECT SCHEMA_NAME( OBJECTPROPERTY( OBJECT_ID, 'SCHEMAID' )) AS SCHEMA_NAME,
      OBJECT_NAME( OBJECT_ID ) AS TABLE_NAME,
      NAME AS COLUMN_NAME
FROM  SYS.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID, NAME, 'IsIdentity') = 1


Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Connection String Quick Reference

SQL Server 2005

Often When programming with different data sources, I need to manually build connection strings. With each provider having slightly different syntax, its impossible to remember them all. Rather than hunt through documentation each time I use this website: connectionstings.com

They have a good list of the most common connection strings with examples for SQL Server 2000 / 2005, Access, Excel, Text, Oracle, MySQL etc. Next time your stuck needing a connection string, try connectionstings.com first.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

More of SQL Server 2005 @ TechEd

SQL Server 2005

I just came across a neat list of SQL Server 2005 resources from the TecEd 2006 shows. You can see the list here.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Move an object to another schema (SQL Server 2005)

SQL Server 2005

To move an object from one schema to another, you can use the TRANSFER the object to its new schema using theALTER SCHEMA command.

ALTER SCHEMA destination schema TRANSFER source object

For example, if you wanted to move the dbo.EOYReport view to the Report schema:

ALTER SCHEMA Reports TRANSFER dbo.EOYReport



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Granting SQL Server 2005 Profiler Access

SQL Server 2005

If you were a DBA of SQL Server 2000, you'd be familiar with the frustration that encouraging developers to use SQL Profiler as part of performance testing meant you needed to grant the sysadmin privilages. Security best practises mean you should only grant users the minimum permissions needed, and certainly not everyone should be trusted with sysadmin privilages.

Well in SQL Server 2005 this problem has been solved. You can now give developers access to use SQL Profiler without having to make them a member of sysadmin.

To grant a permission to use SQL Profiler with a server, execute:

USE master; GRANT ALTER TRACE TO [Username];

Note that this is a system wide permission. You cannot grant the right only for a particular database.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Snippet: Drop all connections to a database (SQL 2005)

SQL Server 2005

If you want to drop all the connections to a database immediately, you can use the following commands:

USE master
GO

ALTER DATABASE database name
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE database name
SET ONLINE

Alternatively you can kill all the processes using a database with this code:

USE master
go

DECLARE @dbname sysname

SET @dbname = 'name of database you want to drop connections from'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server 2005 @ Tech Ed 2006 Webcasts

SQL Server 2005

As part of Tech Ed 2006, Microsoft has a number of live webcasts on SQL Server 2005, including Encryption, Reporting Services, Analysis Services, and Integration Services.

Check out the full list of live webcasts.

If you've missed earlier webcasts, some of them are appearing as On-Demand Webcasts that you can watch at your leisure.

You will need to register (which is free) to view the webcasts. The registration link is in the left column of the page.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server 2005 Service Pack 1 Released

SQL Server 2005

Microsoft has today released SP1 for SQL Server 2005, which includes the Database Mirroring feature not present in the RTM release.

In addition to updating your install with SP1, you should update your Books Online to the latest version: April 2006.

A summary of the updates can be read in KB 916940. The complete list of bug fixes are in KB 913090.

The readme file for SP1 is located here.

Updated samples and sample databases for SP1 are also available.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz







Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.