Email Article To a Friend View Printable Version

How to fix: User, group, or role '*' already exists in the current database. (Microsoft SQL Server, Error: 15023)

SQL Server 2008

 If you've restored a database onto another server, you may get the following error message when adding user logins to the database:

Create failed for User 'TMS'. (Microsoft.SqlServer.Smo)

User, group, or role 'TMS' already exists in the current database. (Microsoft SQL Server, Error: 15023)

 

To fix this there are two different methods depending on the version of SQL Server you are using. Both of these commands re-map the user's Security Identifier (SID) to match the sql server login's SID.

 

SQL Server 2008 / SQL Server 2008 R2

If you have already created the server login for the user, run this in the database with the login problem. (Change 'user' to be the database username you wish to fix, and 'serverlogin' is the sql server login to map the user to).

 ALTER USER user WITH LOGIN = serverlogin

For full details of the ALTER USER command see MSDN http://msdn.microsoft.com/en-us/library/ms176060.aspx

 

SQL Server 2005 / SQL Server 2000

If you have already created the server login for the user, run this in the database with the login problem. (Change 'user' to be the username you wish to fix).

EXEC sp_change_users_login 'Auto_Fix', 'user'

Note that sp_change_users_login has be deprecated in SQL Server, future versions will require using ALTER USER.

For full details of sp_change_users_login see MSDN http://msdn.microsoft.com/en-us/library/ms174378.aspx

 

 



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 Table Function Performance

SQL Server 2008

Rob Farley has a great post on when is a SQL function not a function?

Read the two below functions - they return the same thing, but one is more effecient than the other. If you can't see why, head over to Rob's post that shows the execution plans for a great explaination.

CREATE FUNCTION dbo.FetchSales_inline(@salespersonid int, @orderyear int)
RETURNS TABLE AS 
RETURN (
    SELECT e.LoginID as EmployeeLogin, o.OrderDate, o.SalesOrderID
    FROM Sales.SalesOrderHeader AS o
    LEFT JOIN HumanResources.Employee AS e
    ON e.EmployeeID = o.SalesPersonID
    WHERE o.SalesPersonID = @salespersonid
    AND o.OrderDate >= DATEADD(year,@orderyear-2000,'20000101')
    AND o.OrderDate < DATEADD(year,@orderyear-2000+1,'20000101')
)
;
GO

CREATE FUNCTION dbo.FetchSales_multi(@salespersonid int, @orderyear int)
RETURNS @results TABLE (
    EmployeeLogin nvarchar(512),
    OrderDate datetime,
    SalesOrderID int
    )
AS
BEGIN
    INSERT @results (EmployeeLogin, OrderDate, SalesOrderID)
    SELECT e.LoginID, o.OrderDate, o.SalesOrderID
    FROM Sales.SalesOrderHeader AS o
    LEFT JOIN HumanResources.Employee AS e
    ON e.EmployeeID = o.SalesPersonID
    WHERE o.SalesPersonID = @salespersonid
    AND o.OrderDate >= DATEADD(year,@orderyear-2000,'20000101')
    AND o.OrderDate < DATEADD(year,@orderyear-2000+1,'20000101')
    ;
    RETURN
END
;
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

ANSWER: UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'

SQL Server 2008

I came across an interesting problem last night - a stored procedure that had been created years ago was suddenly coming up with this error message:

UPDATE failed because the following SET options have incorrect settings:
'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views
and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML
data type methods and/or spatial index operations.

This error message means the settings for ANSI_NULLS and QUOTED_IDENTIFIER were different at the time the procedure was created compared to when the table was created. You will notice that whenever you script out an object from sql server management studio that it will add the SET options at the top of the script. In this case it appears the stored procedure was created without the correct SET options.

To fix the problem, you will need to recreate / alter the stored procedure with the correct SET options turned on before the CREATE PROCEDURE / ALTER PROCEDURE statement.

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

alter procedure dbo.myprocedure
....(stored procedure body)

 

 

Seeing as you've already had this problem once - it is worthwhile checking your entire database to see if there are any other procedures creates with QUOTED_IDENTIFIER set off. Luckily Andrei Volkov has already provided the answer on how to do this:

SELECT
SCHEMA_NAME(s.schema_id)  + '.' + s.name AS name,
s.create_date,
s.modify_date,
OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
FROM sys.objects s
WHERE
s.type IN ('P','TR','V','IF','FN','TF')
AND OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') = 0
ORDER BY SCHEMA_NAME(s.schema_id)  + '.' + s.name DESC

You should note that the SQL CAT team have recently posted that the incorrect QUOTED_IDENTIFIER setting can cause problems with persisted computed columns. The SQLCAT ISV team have also posted about issues with filtered indexes

If you read the above articles you'll now see why all scripts generated by sql server management studio include SET QUOTED_IDENTIFIER ON



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

Finding changed rows in SQL Server using INTERSECT, ISNULL or COALESCE and performance

SQL Server 2008

If you are trying to determine which rows are different between two sets, the usual method is to create a query comparing table1.Column1 != table2.Column1 but this doesn't check for rows where one of these columns is null.

isnull(table1.Column1,-1) != isnull(table2.Column1,-1)    might seem like a good way to compare nullable columns - but the performance of this is still not optimal.

Paul White has written a great article on using the INTERSECT command (a feature in SQL Server 2005 and SQL Server 2008) to compare two sets looking for differences and why this performs much better inside SQL Server.

Paul's article details the different query plans that are created by using ISNULL and COALESCE and INTERSECT to do the comparisons, including the internal predicates that SQL Server uses to fulfill the query.

In short an INTERSECT handles null comparisons and is certainly much simpler to write. Below is a quick sample of the INTERSECT example Paul gives (for my own reference), but I highly recommend reading the article for the full details.

SELECT *
FROM  table1 as T1
JOIN table2 as T2 ON  T1.pk = T2.pk
WHERE NOT EXISTS
          (SELECT T1.* INTERSECT T2.*)

 

 



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

Microsoft SQL Server Books

SQL Server 2008



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

Why are my Covering Indexes not working in SQL Server?

SQL Server 2008

Rob Farely has posted a great article on why your covering indexes in SQL Server may not be getting the perfomance you are expecting.

Covering, schmuvvering – when a covering index is actually rubbish

If you use covering indexes but SQL Server keeps ignoring them  - read Rob's article for some reasons why.



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

Security How To Assign Rights To SQL Server Database Objects

SQL Server 2008

Piotr Rodak has a great article on Security – How To Assign Rights To SQL Server Database Objects  and why you should not assign rights to the public role and why assigning rights to roles instead of users gives you much more flexibility.

He also covers why you should only grant permissions to your stored procedures and not to your table / view objects directly.

If you're new to SQL Server database security - or even if your an old hat - take the time to read Piotr's article.

 



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

Searching SQL Stored Procedure Source Code in Information_Schema.Routines

SQL Server 2008
-- Here's a little line of code I use a lot to find out what SQL stored 
procedures / functions use a particular table

SELECT *
FROM   information_schema.routines
WHERE  routine_definition LIKE '%GetThemeColours%'

-- INFORMATION_SCHEMA.ROUTINES is similar to sys.routines but 
has all the text names instead of objectid's  with the added bonus the full 
stored procedure text is searchable (its in ROUTINE_DEFINITION column) - its like 
a super powered sp_helptext
-- Also check out these helpful views:
-- View all the columns in tables + views with full table/view name
SELECT *
FROM   information_schema.columns

-- stored procedure / function parameters
SELECT *
FROM   information_schema.parameters

-- output columns for table valued functions (NOT stored procedures)
SELECT *
FROM   information_schema.routine_columns

-- list of tables
SELECT *
FROM   information_schema.tables

-- list of views
SELECT *
FROM   information_schema.tables 

 

-- 195cac785d844883be7a61f2b54189c6



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.