need help on analyse of sql2008 Upgrade advisor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

need help on analyse of sql2008 Upgrade advisor

Author

Topic

sql2020
Yak Posting Veteran

54 Posts

Posted - 2011-06-08 : 23:44:53

HI

here the example on advisor report how analyse this difficulties for upgrade. please look on this

Database Server |PreOrPostUpgrade| Remove statements that modify system objects| Upgrade Advisor detected statements that update the system catalog. Direct system catalog updates are not allowed in SQL Server 2005 or later. Modify your SQL scripts to use official and documented APIs.| Source Type: Database |Database: ABCD |Object Name: sp_ResetUser |Object Type: P |sp_configure Command: allow updates

sql2020

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-09 : 00:14:49

Well you need to check out sp_resetuser and modify it to supported code. What does that stored procedure do? Can you post it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sql2020
Yak Posting Veteran

54 Posts

Posted - 2011-06-09 : 02:42:41

here the sp:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Create Procedure sp_ResetUser(@UserID sysname) As

exec sp_configure 'Allow Updates', 1
Reconfigure With Override

Update sysusers
Set sid = l.Sid
From master..syslogins l
Where sysusers.Name = l.Name
And l.Name = @UserID

exec sp_configure 'Allow Updates', 0

Reconfigure With Override

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

sql2020

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-09 : 11:09:03

Well you need to stop doing that! Seriously, why do you have a stored procedure like this? There is no reason to do this. You can easily sync sids via other methods.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sql2020
Yak Posting Veteran

54 Posts

Posted - 2011-06-09 : 21:10:35

Can i know how?

sql2020

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-09 : 23:15:22

Have you even tried sp_change_users_login?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources