Friday, March 23, 2012
problem with sp_resolve_logins
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 sp_change_users_login
I am trying to connect the user 'dbo' for a user database to an existing SQL
Server login, which, according to SQL Server BOL, should be accomplished by
using this syntax:
use MyUserDB
go
sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
I get an error message stating that 'dbo' is a forbidden value for the login
parameter in this procedure.
I tried reversing the order, but I get the same error.
If anyone has any clues to what is wrong I would appreciate a comment
I am working with a server which is to be a backup server for one of our Web
servers.
I am changing the setup to match the live server, where the 'dbo' user is
mapped to a login.
I did not set up either of them initially and I am not quite sure which user
names have to have a login, so I am playing it safe by matching the setup of
the live server.
Thank you
Ragnar
Use "sp_changedbowner"
Geoff N. Hiten
Microsoft SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O6CaM$vWFHA.2060@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to connect the user 'dbo' for a user database to an existing
> SQL Server login, which, according to SQL Server BOL, should be
> accomplished by using this syntax:
> use MyUserDB
> go
> sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
> I get an error message stating that 'dbo' is a forbidden value for the
> login parameter in this procedure.
> I tried reversing the order, but I get the same error.
> If anyone has any clues to what is wrong I would appreciate a comment
> I am working with a server which is to be a backup server for one of our
> Web servers.
> I am changing the setup to match the live server, where the 'dbo' user is
> mapped to a login.
> I did not set up either of them initially and I am not quite sure which
> user names have to have a login, so I am playing it safe by matching the
> setup of the live server.
> Thank you
> Ragnar
>
|||Thank you Geoff,
It worked!
I did not try that because according to sp_helpdb the user with the login I
want to connect to dbo was already the owner.
However, when I displayed the users for the DB there was no login name shown
for the dbo user.
In the logins, under Security, this user name has the master as the default
DB, but that is true for the live server too.
I thought maybe this was similar to a case of orphaned users, which happens
when I restore database from a backup of the live server DB, even though dbo
was not shown as an orphaned user when I ran the report..
BTW, I assume the problem with orphaned users is because I have not been
able to restore the master DB with a backup from the live server, because I
have not been able to start SQL Server in single user mode.
I stop it, then start it from the command line with sqlservr.exe -c, -m, as
described in SQL Server BOL
Ragnar
Problem with sp_change_users_login
I am trying to connect the user 'dbo' for a user database to an existing SQL
Server login, which, according to SQL Server BOL, should be accomplished by
using this syntax:
use MyUserDB
go
sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
I get an error message stating that 'dbo' is a forbidden value for the login
parameter in this procedure.
I tried reversing the order, but I get the same error.
If anyone has any clues to what is wrong I would appreciate a comment
I am working with a server which is to be a backup server for one of our Web
servers.
I am changing the setup to match the live server, where the 'dbo' user is
mapped to a login.
I did not set up either of them initially and I am not quite sure which user
names have to have a login, so I am playing it safe by matching the setup of
the live server.
Thank you
RagnarUse "sp_changedbowner"
Geoff N. Hiten
Microsoft SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O6CaM$vWFHA.2060@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to connect the user 'dbo' for a user database to an existing
> SQL Server login, which, according to SQL Server BOL, should be
> accomplished by using this syntax:
> use MyUserDB
> go
> sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
> I get an error message stating that 'dbo' is a forbidden value for the
> login parameter in this procedure.
> I tried reversing the order, but I get the same error.
> If anyone has any clues to what is wrong I would appreciate a comment
> I am working with a server which is to be a backup server for one of our
> Web servers.
> I am changing the setup to match the live server, where the 'dbo' user is
> mapped to a login.
> I did not set up either of them initially and I am not quite sure which
> user names have to have a login, so I am playing it safe by matching the
> setup of the live server.
> Thank you
> Ragnar
>|||Thank you Geoff,
It worked!
I did not try that because according to sp_helpdb the user with the login I
want to connect to dbo was already the owner.
However, when I displayed the users for the DB there was no login name shown
for the dbo user.
In the logins, under Security, this user name has the master as the default
DB, but that is true for the live server too.
I thought maybe this was similar to a case of orphaned users, which happens
when I restore database from a backup of the live server DB, even though dbo
was not shown as an orphaned user when I ran the report..
BTW, I assume the problem with orphaned users is because I have not been
able to restore the master DB with a backup from the live server, because I
have not been able to start SQL Server in single user mode.
I stop it, then start it from the command line with sqlservr.exe -c, -m, as
described in SQL Server BOL
Ragnarsql
Problem with sp_change_users_login
I am trying to connect the user 'dbo' for a user database to an existing SQL
Server login, which, according to SQL Server BOL, should be accomplished by
using this syntax:
use MyUserDB
go
sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
I get an error message stating that 'dbo' is a forbidden value for the login
parameter in this procedure.
I tried reversing the order, but I get the same error.
If anyone has any clues to what is wrong I would appreciate a comment
I am working with a server which is to be a backup server for one of our Web
servers.
I am changing the setup to match the live server, where the 'dbo' user is
mapped to a login.
I did not set up either of them initially and I am not quite sure which user
names have to have a login, so I am playing it safe by matching the setup of
the live server.
Thank you
RagnarUse "sp_changedbowner"
Geoff N. Hiten
Microsoft SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O6CaM$vWFHA.2060@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to connect the user 'dbo' for a user database to an existing
> SQL Server login, which, according to SQL Server BOL, should be
> accomplished by using this syntax:
> use MyUserDB
> go
> sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
> I get an error message stating that 'dbo' is a forbidden value for the
> login parameter in this procedure.
> I tried reversing the order, but I get the same error.
> If anyone has any clues to what is wrong I would appreciate a comment
> I am working with a server which is to be a backup server for one of our
> Web servers.
> I am changing the setup to match the live server, where the 'dbo' user is
> mapped to a login.
> I did not set up either of them initially and I am not quite sure which
> user names have to have a login, so I am playing it safe by matching the
> setup of the live server.
> Thank you
> Ragnar
>|||Thank you Geoff,
It worked!
I did not try that because according to sp_helpdb the user with the login I
want to connect to dbo was already the owner.
However, when I displayed the users for the DB there was no login name shown
for the dbo user.
In the logins, under Security, this user name has the master as the default
DB, but that is true for the live server too.
I thought maybe this was similar to a case of orphaned users, which happens
when I restore database from a backup of the live server DB, even though dbo
was not shown as an orphaned user when I ran the report..
BTW, I assume the problem with orphaned users is because I have not been
able to restore the master DB with a backup from the live server, because I
have not been able to start SQL Server in single user mode.
I stop it, then start it from the command line with sqlservr.exe -c, -m, as
described in SQL Server BOL
Ragnar
Wednesday, March 21, 2012
Problem with source from Books Online - Application.LoadPackage
I'm trying to execute a package in the file system from an c# assembly. In BOL there is the following sample code available:
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/M_Microsoft_SqlServer_Dts_Runtime_Application_LoadPackage_1_cfc592c3.htm
Application.LoadPackage Method (String, IDTSEvents)
class ApplicationTests
{
static void Main(string[] args)
{
// The variable pkg points to the location of the
// ExecuteProcess package sample installed with
// the SSIS samples.
string pkg = @."C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\ExecuteProcess Sample\ExecuteProcess\UsingExecuteProcess.dtsx";
Application app = new Application();
Package p = app.LoadPackage(pkg, null);
// Now that the package is loaded, we can query on
// its properties.
int n = p.Configurations.Count;
DtsProperty p2 = p.Properties["VersionGUID"];
DTSProtectionLevel pl = p.ProtectionLevel;
Console.WriteLine("Number of configurations = " + n);
Console.WriteLine("VersionGUID = " + p2);
Console.WriteLine("ProtectionLevel = " + pl);
}
}
I added the reference to "Microsoft.SQLServer.DTSRuntimeWrap" and declared it by
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
No I'm not able to compile because app.LoadPackage(pkg, null); needs an additional IDTSEvents90. After changing the row to
Package p = app.LoadPackage(pkg, true, null);
I'm getting the following errors:
Error 1 Cannot implicitly convert type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackage90' to 'Microsoft.SqlServer.Dts.Runtime.Wrapper.Package'. An explicit conversion exists (are you missing a cast?)
Error 2 The type or namespace name 'DtsProperty' could not be found (are you missing a using directive or an assembly reference?)
Error 3 'Microsoft.SqlServer.Dts.Runtime.Wrapper.Package' does not contain a definition for 'Properties'
Can someone please give me an example how to start the Package now in the latest code syntax and set the values of a variable from outside?
I am using the following versions
Microsoft Visual Studio 2005
Version 8.0.50727.26 (RTM.050727-2600)
Microsoft .NET Framework
Version 2.0.50727
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.1314.00
Best regards,
Dirk
.Wrapper assembly and namespace contain COM interop declarations - i.e. if you want to work with unmanaged object model directly. It is usually more convinient to work with managed object model built on top of it.sql