login works with SQL but not MSDE

Any idea why the following dmo connection string would not work on MSDE, but
does work on SQL Personal Edition? It appears to connects, but then can't
find the server name. In both cases we are using the latest SQL Personal,
MSDE, and Windows XP.
Thanks for any guidance.
Neil
connection string:
server=myserver;trusted_connection="yes";database= northwind;connection
timeout=10;
exception:
[INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
timeout=10;
[EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login, Object
Password)
at SQLServer.Connect()
Was MSDE installed with the network protocols enabled? With SP3a they are
turned off by default.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> Any idea why the following dmo connection string would not work on MSDE,
but
> does work on SQL Personal Edition? It appears to connects, but then can't
> find the server name. In both cases we are using the latest SQL Personal,
> MSDE, and Windows XP.
> Thanks for any guidance.
> Neil
> connection string:
> server=myserver;trusted_connection="yes";database= northwind;connection
> timeout=10;
> exception:
> [INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
> timeout=10;
> [EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login,
Object
> Password)
> at SQLServer.Connect()
>
|||This took me a little bit further. I can now actually see the tables in in
my program using the dmo calls. Thanks! owever, when I try to access the
individual columns, I continue to get errors such as:
EXCEPTION] 20040520 08:37:30.955 (sqlserver.myObject) Error retrieving info
from test2: Server user 'mymachine\mylogin' is not a valid user in database
'test2'.
Again, this all works fine using SQL Personal Edition.
Neil
"Jim Young" <thorium48@.hotmail.com> wrote in message
Was MSDE installed with the network protocols enabled? With SP3a they are
turned off by default.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> Any idea why the following dmo connection string would not work on MSDE,
but
> does work on SQL Personal Edition? It appears to connects, but then can't
> find the server name. In both cases we are using the latest SQL Personal,
> MSDE, and Windows XP.
> Thanks for any guidance.
> Neil
> connection string:
> server=myserver;trusted_connection="yes";database= northwind;connection
> timeout=10;
> exception:
> [INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
> timeout=10;
> [EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login,
Object
> Password)
> at SQLServer.Connect()
>
|||Carefully read the error message. You need to make the user you are
connecting with, in this case a Windows login, a user in the database you
are trying to use. Everyone that can connect to a server can see the schema
of any database, but you can't select or change any data until you are given
explicit permissions in a database.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:OShgsvmPEHA.2468@.TK2MSFTNGP11.phx.gbl...
> This took me a little bit further. I can now actually see the tables in
in
> my program using the dmo calls. Thanks! owever, when I try to access the
> individual columns, I continue to get errors such as:
> EXCEPTION] 20040520 08:37:30.955 (sqlserver.myObject) Error retrieving
info
> from test2: Server user 'mymachine\mylogin' is not a valid user in
database[vbcol=seagreen]
> 'test2'.
> Again, this all works fine using SQL Personal Edition.
> Neil
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> Was MSDE installed with the network protocols enabled? With SP3a they are
> turned off by default.
> Jim
> "Neil W." <neilw@.netlib.com> wrote in message
> news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> but
can't[vbcol=seagreen]
Personal,
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection[vbcol=seagreen]
or
> Object
>
>

Login scripting to a file

I am new to DMO and I am trying to find a way where I can create a script file containing all server logins. THis needs to run on a daily basis.

Anyone have any examples to share?

Use the following procs, you will generate the login scripts.

Cut and paste the following proc and then execute

exec sp_script_generator 'c:\scripts\', <sa pwd>.

if sql server authentication, provide the password.

Please note that, I wrote the scripts long back.

If any issues in the procs, please post the same.

if exists (select * from sysobjects where id = object_id('dbo.sp_hexadecimal') and sysstat & 0xf = 4)
drop procedure dbo.sp_hexadecimal
GO

CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(255),
@.hexvalue varchar(255) OUTPUT
AS
DECLARE @.charvalue varchar(255)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH(@.binvalue)
SELECT @.hexstring = '0123456789abcdef'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue

GO

if exists (select * from sysobjects where id = object_id('dbo.sp_displayoaerrorinfo') and sysstat & 0xf = 4)
drop procedure dbo.sp_displayoaerrorinfo
GO

CREATE PROCEDURE sp_displayoaerrorinfo
@.object int,
@.hresult int
AS
DECLARE @.output varchar(255)
DECLARE @.hrhex char(10)
DECLARE @.hr int
DECLARE @.source varchar(255)
DECLARE @.description varchar(255)
PRINT 'OLE Automation Error Information'
EXEC sp_hexadecimal @.hresult, @.hrhex OUT
SELECT @.output = ' HRESULT: ' + @.hrhex
PRINT @.output
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT " sp_OAGetErrorInfo failed."
RETURN
END

GO

set quoted_identifier off
go
if exists (select * from sysobjects where id = object_id('dbo.sp_srv_obj_scr_gen') and sysstat & 0xf = 4)
drop procedure dbo.sp_srv_obj_scr_gen
GO

/****************************************************************************************************************/
/* Procedure : sp_srv_obj_scr_gen */
/* Purpose : To generate server objects namely logins, devices and databases in a SQL Server */
/* Called From : sp_Script_Generator [Main] */
/* Author : Narasimhan Jayachandran */
/* Date : 09/28/98 */
/* Date : 03/15/00 */
/****************************************************************************************************************/

create proc sp_srv_obj_scr_gen
@.object int,
@.srvObjColl varchar(30),
@.strObjQry varchar(255),
@.strFile varchar(6000),
@.strFilePath varchar(6000)=null
as
begin

declare @.srvObjname varchar(128), @.strMethod varchar(6000),@.hr int,@.return varchar(255), @.strCmd varchar(6000)
declare @.dbCtr int, @.FileCtr int, @.DestFile varchar(6000)

select @.dbctr=0

select @.strCmd = "echo use master >> " + @.strFile
exec master..xp_cmdshell @.strCmd, no_output
select @.strCmd = "echo go >> " + @.strFile
exec master..xp_cmdshell @.strCmd, no_output

exec("declare server_obj_cursor cursor for "+ @.strObjQry)

open server_obj_cursor

fetch next from server_obj_cursor into @.srvObjname

while @.@.fetch_status = 0
begin
-- select @.srvobjname

select @.strMethod =' '+@.srvObjColl+'("'+@.srvObjname+'").Script(324,"'+@.strFile+'")'

exec @.hr = sp_OAMethod @.object, @.strMethod, @.return out

if @.hr <> 0
begin
close server_obj_cursor
deallocate server_obj_cursor
return 1
end

if @.srvObjColl="Databases" -- database files --
begin
select @.dbCtr = @.dbCtr + 1
select @.strCmd = "copy "+ @.strFile+" "+@.strFilePath+convert(varchar(30),@.dbCtr)+".sql"
end

exec master..xp_cmdshell @.strCmd, no_output

fetch next from server_obj_cursor into @.srvObjname

end

close server_obj_cursor
deallocate server_obj_cursor

if @.srvObjColl="Databases" -- database files --
begin
select @.DestFile=@.strFile
select @.strCmd = "copy ",@.FileCtr=1,@.strFile=" "
while @.FileCtr <= @.dbCtr
begin
select @.strFile = @.strFile + @.strFilePath+convert(varchar(30),@.FileCtr)+".sql + "
select @.FileCtr = @.FileCtr + 1
end
select @.strFile = left(@.strFile,len(@.strFile)-2)

select @.strCmd = @.strCmd+@.strFile+" "+@.destFile
exec master..xp_cmdshell @.strCmd, no_output

select @.strCmd = "del ",@.FileCtr=1
while @.FileCtr <= @.dbCtr
begin
select @.strCmd = "Del "+@.strFilePath+convert(varchar(30),@.FileCtr)+".sql"
exec master..xp_cmdshell @.strCmd, no_output
select @.FileCtr = @.FileCtr + 1
end
end

return 0
end

GO

GRANT EXECUTE ON dbo.sp_srv_obj_scr_gen TO public
GO

--exec sp_script_generator "D:\backup\sqlserver\Script\"

if exists (select * from sysobjects where id = object_id('dbo.sp_script_generator') and sysstat & 0xf = 4)
drop procedure dbo.sp_script_generator
GO
/* ************************************************************************************************************** */
/* Procedure : sp_script_generator */
/* Purpose : To generate scripts for all objects of all databases in a SQL Server 6.5/7.0 */
/* Sub Procedures : sp_srv_obj_scr_gen,sp_db_obj_scr_gen,sp_tb_obj_scr_gen,sp_con_obj_scr_gen */
/* Author : Narasimhan Jayachandran */
/* Version : 1.0 */
/* Date : 09/28/98 */
/* Version : 1.1 */
/* Date : 03/25/99 */
/* Version : 2.0 */
/* Date : 02/15/00 */
/* Usage Hints : sp_Script_Generator <Output File Path>,[SA Password - optional ] */
/* Example : sp_script_generator "c:\scripts\", [SA Password] */
/* Outputs : c:\scripts\SQLSERVER_logins.sql */
/* c:\scripts\SQLSERVER_devices.sql */
/* c:\scripts\SQLSERVER_databases.sql */
/* c:\scripts\SQLSERVER_<databaseName1>.sql */
/* ... */
/* ... */
/* ... */
/* c:\scripts\<SQLSERVER>_<databaseNameN>.sql */
/* Modified Date Reason */
/* 06/25/2001 Jobs Scripts added */
/* 07/03/2001 syslogins used to pick the loginnames */
/* Windows Authentication Mode added */
/* Use the System function ServerName instead of as a parameter */
/****************************************************************************************************************/
create proc sp_script_generator
@.strFilePath varchar(6000) = null,
@.pwd varchar(128) = null

as
begin

declare @.object int, @.hr int,@.return varchar(255)
declare @.dbname varchar(128),@.ObjColl varchar(30)
declare @.strFile varchar(6000), @.strCmd varchar(6000)
declare @.strObjQry varchar(6000), @.strObjQry1 varchar(255), @.strMethod varchar(255)
declare @.dbObjColl varchar(30), @.tbObjColl varchar(30),@.srvObjColl varchar(30)
declare @.dbobjname varchar(30), @.tbobjname varchar(30),@.ObjcollName varchar(60)
declare @.srvObjFlag tinyint,@.sts tinyint,@.TbCtr int,@.ScriptType int
declare @.Ver6570 char(4),@.ProgID varchar(16), @.Property varchar(6000)
declare @.src varchar(255), @.desc varchar(255)

declare @.objJobserver int

set nocount on

if @.strFilePath is null
begin
print "Usage : sp_Script_Generator <Output File Path>, [SA Password]"
print "-"
print "Example : sp_script_generator 'c:\scripts\',[SA Password]"
print "Outputs : c:\scripts\SQLSERVER_logins.sql"
print " c:\scripts\SQLSERVER_devices.sql"
print " c:\scripts\SQLSERVER_databases.sql"
print " c:\scripts\SQLSERVER_<databaseName1>.sql"
print " .."
print " .."
print " .."
print " c:\scripts\SQLSERVER_<databaseNameN>.sql"
print "-"
return
end

-- version check
select @.Ver6570=substring(@.@.version,23,4)

select @.ProgID =
case
when @.Ver6570 = '6.50' then 'SQLOLE.SQLServer'
else 'SQLDMO.SQLServer'
-- when @.Ver6570 = '7.00' then 'SQLDMO.SQLServer'
end

-- Create an object
print "Creating SQL SERVER Object..."
exec @.hr = sp_OACreate @.ProgID, @.object out
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
return
end

print "Connecting to the SQL SERVER "+@.Ver6570+"..."
-- Set the property value to true if it is Windows Authentication
if @.pwd is null
begin
-- Set a property
Print "Connecting to SQLServer using Windows Authentication Mode...!"
exec @.hr = sp_OASetProperty @.object, 'LoginSecure', TRUE
if @.hr <> 0
begin
exec sp_OAGetErrorInfo @.object, @.src out, @.desc out
select hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
return
end

-- Connect to the sql server -- Windows Authentication Mode
select @.strMethod = 'Connect("'+@.@.ServerName+'")'
exec @.hr = sp_OAMethod @.object,@.strMethod
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return
end
end
else
begin
Print "Connecting to SQLServer using SQLServer Authentication Mode...!"
-- Connect to the sql server
select @.strMethod = 'Connect("'+@.@.ServerName+'" , "sa", "'+@.pwd+'")'
exec @.hr = sp_OAMethod @.object,@.strMethod
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return
end
end

-- Verify that the connection
exec @.hr = sp_OAMethod @.object, 'VerifyConnection' ,@.return out
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return end

select @.srvObjFlag = 0

-- Server Objects

-- Logins
print "Creating Scripts for "+@.@.servername+" Server Logins..."
select @.strFile=@.strFilePath+@.@.servername+"_logins.sql"
select @.strCmd = "del "+@.strFile
exec master..xp_cmdshell @.strCmd, no_output

select @.srvObjColl = "Logins", @.sts = 0
select @.strObjQry =
case
when @.Ver6570 = "6.50" then
" select name from master..syslogins where status = 8 and name != 'probe' "
else
" select loginname from master..syslogins where loginname not in ('sa') "
end

exec @.sts = sp_srv_obj_scr_gen @.object,@.srvObjColl,@.strObjQry,@.strFile
if @.sts = 1 goto ProcErr

goto CloseDbCur

ProcErr: exec sp_displayoaerrorinfo @.object,@.hr
if @.srvObjFlag = 0 goto ProcFine

CloseDbCur:
close database_cursor
deallocate database_cursor
ProcFine:
exec @.hr = sp_OADestroy @.object
if @.hr <> 0
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.ObjJobServer
if @.hr <> 0
exec sp_displayoaerrorinfo @.ObjJobServer, @.hr

set nocount off
return
end

GO

--

Thanks.

Naras.

|||Actually, you don't need DMO to do this - just search for sp_help_revlogin and install the procedure in your master database. It generates a script which creates all the logins on your server with the correct hash for the password in place for each login.|||

Cool.

Thanks Allen White.

But let him learn DMO...too

Thanks

Naras.