sp_sidmap on SQL 2005

29 Mar 2007 3:54 PM

kzakoski

I've been using sp_sidmap to remap users when moving databases from server to
server in SQL 2000 without any issues. Now that I'm setting up and migrating
to SQL 2005, I've encounter issues with SID's not matching and would like to
run sp_sidmap to sync.

The problem is, I've downloaded the scripts referenced in KB 240872, which
say they apply to SQL 2005, but they can't be applied to SQL 2005 because
2005 doesn't allow adhoc updates to sytem catalogs.

Is there a way in SQL 2005 to compile and use this proc?

--
Kevin Z

29 Mar 2007 5:45 PM

Kalen Delaney

Hi Kevin

No, there is no way to use this proc in SQL 2005. Direct access to the
system tables is not allowed, not even for selecting.

The documented procedure sp_change_users_login is the recommended approach
in SQL 2005.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com

Show quote

"kzakoski" <kzako***@discussions.microsoft.com> wrote in message
news:062715B9-2B71-4215-A474-FBAB8DC5F69A@microsoft.com...
> I've been using sp_sidmap to remap users when moving databases from server
> to
> server in SQL 2000 without any issues. Now that I'm setting up and
> migrating
> to SQL 2005, I've encounter issues with SID's not matching and would like
> to
> run sp_sidmap to sync.
>
> The problem is, I've downloaded the scripts referenced in KB 240872, which
> say they apply to SQL 2005, but they can't be applied to SQL 2005 because
> 2005 doesn't allow adhoc updates to sytem catalogs.
>
> Is there a way in SQL 2005 to compile and use this proc?
>
> --
> Kevin Z

29 Mar 2007 7:10 PM

kzakoski

Kalen,

From what I can tell from BOL, sp_change_users_login will only fix SQL
accounts like sp_sidmap did, but I'm also encountering issues with NT
accounts needing to be sync'd, which sp_sidmap did as well.

Is there a way to resync NT accounts? I've used sp_grantlogin which appears
to work, but sp_sidmap would update accounts when there was a change in
domain names and sync when names matched up. This occurs often in our
environment when we're copying DB between our US and UK offices which are on
different Domains.

Kevin Z.

--
Kevin Z

Show quote

"Kalen Delaney" wrote:

> Hi Kevin
>
> No, there is no way to use this proc in SQL 2005. Direct access to the
> system tables is not allowed, not even for selecting.
>
> The documented procedure sp_change_users_login is the recommended approach
> in SQL 2005.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
>
> "kzakoski" <kzako***@discussions.microsoft.com> wrote in message
> news:062715B9-2B71-4215-A474-FBAB8DC5F69A@microsoft.com...
> > I've been using sp_sidmap to remap users when moving databases from server
> > to
> > server in SQL 2000 without any issues. Now that I'm setting up and
> > migrating
> > to SQL 2005, I've encounter issues with SID's not matching and would like
> > to
> > run sp_sidmap to sync.
> >
> > The problem is, I've downloaded the scripts referenced in KB 240872, which
> > say they apply to SQL 2005, but they can't be applied to SQL 2005 because
> > 2005 doesn't allow adhoc updates to sytem catalogs.
> >
> > Is there a way in SQL 2005 to compile and use this proc?
> >
> > --
> > Kevin Z
>
>
>