problem with sp_resolve_logins

I've set up SQL Server log shipping and I'm testing the failover process.
BOL says to run sp_resolve_logins from the target database. That failed for
me with the error: "Invalid object name 'syslogins'."
I searched google, and the consensus was that sp_resolve_logins has to be
run from master. When I did this, the procedure executed successfully, but
all the users in my database remained orphaned.
Next, I ran sp_helptext on sp_resolve_logins to see what's really happening.
It looks like when I ran it the first time from the target database it
failed on this (line 59):
SELECT *
INTO #sysloginstemp
FROM syslogins
WHERE sid = 0x00
because syslogins isn't qualified with master.dbo.
When run from master, it looks the reason it didn't fix the logins is
because of this line (93):
SELECT @.usrname = u.name
FROM dbo.sysusers u
,master.dbo.syslogins l
WHERE u.sid = @.lgnsid
AND l.loginname = @.lgnname
AND l.sid <> u.sid
Since I had to run it from master, it is going to check the sysusers table
of master, not my database, so there won't be any users to resolve.
Can someone tell me if I missed something important here? I came up with a
workaround, but I'd prefer to do it by the book.
I'm using SQL Server 2000 SP3. This particular instance was on the
developer edition, but I looked at sp_resolve_logins on an enterprise
edition machine and it's the same.
Thanks in advance for any insight,
Mike FormanHi Michael,
The Transact-SQL code for the sp_resolve_logins stored procedure
incorrectly uses the syslogins system table (syslogins resides in the
master database). The following code in the stored procedure causes the
error to occur:
SELECT *
INTO #sysloginstemp
FROM syslogins
WHERE sid = 0x00
You may check the following article.
310882 BUG: sp_resolve_logins Stored Procedure Fails If Executed During Log
http://support.microsoft.com/?id=310882
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Michael Forman" <mforman@.factset.com>
| Subject: problem with sp_resolve_logins
| Date: Wed, 6 Aug 2003 16:49:03 -0400
| Lines: 41
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <OpdStwFXDHA.536@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: inetuser.factset.com 164.55.1.103
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:299890
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I've set up SQL Server log shipping and I'm testing the failover process.
| BOL says to run sp_resolve_logins from the target database. That failed
for
| me with the error: "Invalid object name 'syslogins'."
|
| I searched google, and the consensus was that sp_resolve_logins has to be
| run from master. When I did this, the procedure executed successfully,
but
| all the users in my database remained orphaned.
|
| Next, I ran sp_helptext on sp_resolve_logins to see what's really
happening.
| It looks like when I ran it the first time from the target database it
| failed on this (line 59):
| SELECT *
| INTO #sysloginstemp
| FROM syslogins
| WHERE sid = 0x00
|
| because syslogins isn't qualified with master.dbo.
|
| When run from master, it looks the reason it didn't fix the logins is
| because of this line (93):
| SELECT @.usrname = u.name
| FROM dbo.sysusers u
| ,master.dbo.syslogins l
| WHERE u.sid = @.lgnsid
| AND l.loginname = @.lgnname
| AND l.sid <> u.sid
|
| Since I had to run it from master, it is going to check the sysusers table
| of master, not my database, so there won't be any users to resolve.
|
| Can someone tell me if I missed something important here? I came up with
a
| workaround, but I'd prefer to do it by the book.
|
| I'm using SQL Server 2000 SP3. This particular instance was on the
| developer edition, but I looked at sp_resolve_logins on an enterprise
| edition machine and it's the same.
|
| Thanks in advance for any insight,
| Mike Forman
|
|
|

Problem with role permissions - supplemental info

I foud a way to fix this, but it's a long process (this DB is huged), and I
would still like to know why this hapened. Here is what I did.
To fix a database (refered as orgDB below) with a role permissions problem
(won't save), do the following
a.. Create a new database (refered as tmpDB below)
b.. Import all tables from orgDB into tmpDB
c.. in tmpDB, run scrip to add users and set permissions
d.. Detach both database (right-click on db name, All Tasks, Detach
database). The DBs will disapear from the list.
e.. In Windows explorer, rename the original data (mdf) and log (ldf)
files: orgDB.mfd >> xx_orgDB.mdf and orgDB.lfd >> xx_orgDB.ldf
f.. In Windows explorer, rename the temporary data (mdf) and log (ldf)
filesto the original name: tmpDB.mfd >> orgDB.mdf and tmpDB.lfd >>
orgDB.ldf
g.. In Enterprise Manager, right-click on Databases / All Tasks / Attach
Database and follow the wizzard and select the orgDB.mdf file.
If there is a simplier (shorter) way to do this, I would like to know it.
C.R.
"C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
news:eYGmqGBwDHA.1744@.TK2MSFTNGP12.phx.gbl...

quote:

---

> We are experiencing a problems setting permissions on roles on SQL Server
> 2000. Our SQL server is holding several databases (60+) and on some of

---

them

quote:

---

> (not all), when we change permissions on tables for a role (Public for
> example), they do not seam to be saved.We tried through Enterprise Manager
> and SQL Query Analyser with the same result. We change the permission,
> click on OK, go out of EM and when we go back in, the permissions are back
> the way they were before we changed them What is puzzling us is that it
> works fine on some databases and not on other. We do not know of any
> database options that would prevent changing the permissions, is there

---

one?

quote:

---

> Anyone as any taught about this?
> MS SQL Server 2000 (sp3a)
> Windows 2000 (sp4)
> --
> C.R.
>

---

I've never heard of something like this before - it'd be great if you could
call product support and help them reproduce this so we can see if there's a
problem with SQL Server or something really really strange about the
database...
-Richard
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
news:e3vpYRDwDHA.1872@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I foud a way to fix this, but it's a long process (this DB is huged), and

---

I

quote:

---

> would still like to know why this hapened. Here is what I did.
> To fix a database (refered as orgDB below) with a role permissions problem
> (won't save), do the following
> a.. Create a new database (refered as tmpDB below)
> b.. Import all tables from orgDB into tmpDB
> c.. in tmpDB, run scrip to add users and set permissions
> d.. Detach both database (right-click on db name, All Tasks, Detach
> database). The DBs will disapear from the list.
> e.. In Windows explorer, rename the original data (mdf) and log (ldf)
> files: orgDB.mfd >> xx_orgDB.mdf and orgDB.lfd >> xx_orgDB.ldf
> f.. In Windows explorer, rename the temporary data (mdf) and log (ldf)
> filesto the original name: tmpDB.mfd >> orgDB.mdf and tmpDB.lfd >>
> orgDB.ldf
> g.. In Enterprise Manager, right-click on Databases / All Tasks / Attach
> Database and follow the wizzard and select the orgDB.mdf file.
> If there is a simplier (shorter) way to do this, I would like to know it.
>
> C.R.
>
> "C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
> news:eYGmqGBwDHA.1744@.TK2MSFTNGP12.phx.gbl...
Server[QUOTE]
> them
Manager[QUOTE]
back[QUOTE]
> one?
>

---

|||I have noted this too.
We have a SQL 2000 SP3 that is having databases of its own.
We recently upgraded (other) databases from a SQL 7.0 server to this s2k
server.
The problem of not saving the permisssions is happening only on the upgraded
dbs not the ones created in SQL 7.0.
WE have managed to replicate this problem on at least 3 different ugpraded
servers.
Although it seems that databases backed up in sQL 7.0 and restore in SQL
2000 does not have this problem but databases upgraded using the copy db
wizards - all have the problem.
Interestingly, checkint the tables (syspermissions) indicate that the role
does actually have the permissions, but they are not shown on EM or scripted
out of the objects if u script them.
Hope that helps in replication of the problem.
MHJ
"Richard Waymire [MSFT]" <rwaymi@.online.microsoft.com> wrote in message
news:eFRavfEwDHA.2492@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I've never heard of something like this before - it'd be great if you

---

could

quote:

---

> call product support and help them reproduce this so we can see if there's

---

a

quote:

---

> problem with SQL Server or something really really strange about the
> database...
> -Richard
> --
> Richard Waymire, MCSE, MCDBA
> This posting is provided "AS IS" with no warranties, and confers no

---

rights.

quote:

---

> "C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
> news:e3vpYRDwDHA.1872@.TK2MSFTNGP09.phx.gbl...
and[QUOTE]
> I
problem[QUOTE]
Attach[QUOTE]
it.[QUOTE]
> Server
of[QUOTE]
> Manager
permission,[QUOTE]
> back
it[QUOTE]
>

---

Problem with repot layout

Hi,
I'm in the process of designing a report and am having a few problems
with the layout. What I need is a header on all pages but footer on
only the first page. I managed to accomplish this by hiding all the
textboxes on the footer if the current page global variable <> 1. The
problem is that the footer still takes up space on the following
pages. I tried placing all the information in the body of the report
but there is no way to know what page I am currently on.
I just can't seem to see a way of having footer on the first page only
and thereafter only the header and detail.
Any help would be appreciated.
Thanks in advance.
DaveOn Jun 5, 5:35 am, david.richa...@.siigroup.com wrote:
> Hi,
> I'm in the process of designing a report and am having a few problems
> with the layout. What I need is a header on all pages but footer on
> only the first page. I managed to accomplish this by hiding all the
> textboxes on the footer if the current page global variable <> 1. The
> problem is that the footer still takes up space on the following
> pages. I tried placing all the information in the body of the report
> but there is no way to know what page I am currently on.
> I just can't seem to see a way of having footer on the first page only
> and thereafter only the header and detail.
> Any help would be appreciated.
> Thanks in advance.
> Dave
The only thing I can think of at the moment is to encapsulate all the
textbox controls in a rectangle in the footer, that might conserve a
little space. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||On 5 Jun, 14:32, EMartinez <emartinez...@.gmail.com> wrote:
> On Jun 5, 5:35 am, david.richa...@.siigroup.com wrote:
>
>
> > Hi,
> > I'm in the process of designing a report and am having a few problems
> > with the layout. What I need is a header on all pages but footer on
> > only the first page. I managed to accomplish this by hiding all the
> > textboxes on the footer if the current page global variable <> 1. The
> > problem is that the footer still takes up space on the following
> > pages. I tried placing all the information in the body of the report
> > but there is no way to know what page I am currently on.
> > I just can't seem to see a way of having footer on the first page only
> > and thereafter only the header and detail.
> > Any help would be appreciated.
> > Thanks in advance.
> > Dave
> The only thing I can think of at the moment is to encapsulate all the
> textbox controls in a rectangle in the footer, that might conserve a
> little space. Sorry that I could not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
Thanks for you response, but unfortunately I already tried that. The
problem is that the footer takes up about half the page which is why
it is only required on the first page. The document conforms to a
certain legal requirement required by my company.
Thanks again.
Dave