question with a stored procedure

I have a stored procedure that inserts a new row if the user doesn't exist in the table...that part works. I then need it to check to see if the user has changed their program code and update the row if they have. the way I'm trying to accomplish this is by selecting their id and program code by what I'm passing it, and if it does not exist, I update the row. For some reason it's not updating the table. I'm new to stored procedures so I'm not sure if I'm doing this correctly or not. Can you please take a look and let me know if this looks ok? This is for MSSQL 2000:

CREATE PROCEDURE [dbo].[InsertUpdateProcedure] (@.LastNamevarchar(255),@.FirstNamevarchar(255),@.Emailvarchar(255),@.ColleagueIDvarchar(50),@.Programvarchar(50))AS IFNOT EXISTS(SELECT users_colleague_idFROM ept_usersWHERE users_colleague_id = @.ColleagueID)BEGIN--The row doesn't exist. Insert code goes hereINSERT INTO ept_users (users_colleague_id,users_last_name,users_first_name,users_email_address,users_program)VALUES(@.ColleagueID,@.LastName,@.FirstName,@.Email,@.Program)ENDIFNOT EXISTS(SELECT users_colleague_idFROM ept_usersWHERE users_colleague_id = @.ColleagueIDAND users_program = @.Program)BEGINUPDATE ept_usersSETusers_program = @.Program,users_email_sent = 0,users_billed_current = 0,users_second_email_sent = 0WHEREusers_colleague_id = @.ColleagueIDENDGO

nevermind...it was choking on some null values. All is working now.sql

Question transport-level error

Hi all,

I'm having a little problem with my sqlserver 2005.

I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.

Like this:
-
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO

USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO

Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO

And drops the user by executing following:

Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]

-Then if I from a query, first creates the user, then drops the user, I can't create him again? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I'f i then runs the createscript again, the user is created correctly.

Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?

Best Regards
Henrik

Have you tried executing your scripts from sqlcmd instead of Management Studio? Are you seeing the same behavior?

Thanks
Laurentiu

|||

Hi Cristofor,

Thanks for you reply. I'v just tried that and get following:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

But under "Server Properties" - "Connections"

The "Allow Remote connections to this server" is checked, with a query timeout on 600

|||

In some SQL Server SKUs the remote connection is disabled by default. Use SQL Server Configuration Manager to enable TCP/IP (or named pipes) on your SQL Server instance (requires to restart SQL Server service). Also make sure the Windows firewall is configured to allow SQL Server to accept connections.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

I have just enabled this it was disabled, but still im getting the same error.

But by executeing the script localy from the database server, it works fine. And can do it as many times as i please, whithout errors.
like this:

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql"
And
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql"

But it still drops the connection to the user elsewhere, on the first time it's used after creation.
Eg. First i drop the user, then i create it again with the scripts. Then i open a browser and goes to my website, wich uses this user to connect to the database to retrive and update information. Then the first time i try to login i get the error below. Then i push "F5" to refreash the page and all works fine again?

Soucre:

.Net SqlClient Data Provider

Error Message:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

|||

My only guess is that your web application is somehow caching/reusing the old connection (with the old login), and when you refresh the page it reestablishes the connection. Remember that when you drop and create a login it is a whole new principal, not the same one (even if the name and password is the same), and any established connection will be invalidated and the connection will be forcedly terminated by the server the next time any application tries to use it.

I would recommend using SQL profiler to trace back new/closed connections as well as existing sessions to try to detect if this is indeed the case.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

Thats also what im comming to. I haven't tryed the SQL profiler yet, but will do this later.

But i have found out that it's not enough only to stop and start the web site again on IIS. I have to restart the hole "IIS" to make the "refreash". So im thinking that the session is not terminated correctly from the website, when closing the browser/session?.

|||

Besides your investigation on why (and if) the web application is reusing the connection instead of reconnecting, I have one question for you: are you dropping and recreating this user in order to create a whole new principal (i.e. a new SID, with no existing objects, etc.), or just as a mechanism to prevent this principal from establishing a new connection?

If you are using these scripts to prevent new connections, you may also want to consider disabling the login instead. By disabling the login, you prevent new connections without affecting existing ones, and without the need to cleanup any objects or references (i.e. users) this principal may have in the system. You can find the syntax for this operation in ALTER LOGIN (http://msdn2.microsoft.com/en-us/library/ms189828.aspx).

-Raul Garcia

SDE/T

SQL Server Engine

|||

Actually im useing thise scripts as a part of my deploymentkit. This is to ensure that all securitysettings to any databases, stored procedures etc are droped. So i afterwards can replace them with either the same or new ones, without leaving anything behind.

But i took the change and implemented it into my deployment. The first time i got the same error about the "transport-level error". But that was because i've missed another scipt later in the installation, it was some inserts-stament i tried to execute from a vb SqlConnection. Then i moved those two things around, so it was the last code and then it worked fine, i can now open my webpage without the "transport-level error" :) yeah .. even after installing, uninstalling, installing ....

So i have to go back to the websession-closing, in my case, because under the "Activity Monitor" in sql, i can see a connection long after closing the browser. So this is now another problem for me, to find a way to kill this one.

But im stil interested in fixing the other problem, because it's annoying that things has to come in the correct order.. And the differens between executing it from a sql-query (in sql 2005), from command promt and from my msi-package. -Wich by the way executes it by calling a shell in vb wich runs a .cmd-file containing the two sql-scripts.

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql" -o "C:\DropDBUser.log"
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql" -o "C:\CreateDBUser.log"

But for the moment im happy :)

|||

I’m glad to hear that. Feel free to let us know if you have further security questions in SQL Server, we are always looking forward to help our customers whenever we have a chance.

-Raul Garcia

SDE/T

SQL Server Engine

Question transport-level error

Hi all,

I'm having a little problem with my sqlserver 2005.

I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.

Like this:
-
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO

USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO

Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO

And drops the user by executing following:

Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]

-Then if I from a query, first creates the user, then drops the user, I can't create him again? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I'f i then runs the createscript again, the user is created correctly.

Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?

Best Regards
Henrik

Have you tried executing your scripts from sqlcmd instead of Management Studio? Are you seeing the same behavior?

Thanks
Laurentiu

|||

Hi Cristofor,

Thanks for you reply. I'v just tried that and get following:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

But under "Server Properties" - "Connections"

The "Allow Remote connections to this server" is checked, with a query timeout on 600

|||

In some SQL Server SKUs the remote connection is disabled by default. Use SQL Server Configuration Manager to enable TCP/IP (or named pipes) on your SQL Server instance (requires to restart SQL Server service). Also make sure the Windows firewall is configured to allow SQL Server to accept connections.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

I have just enabled this it was disabled, but still im getting the same error.

But by executeing the script localy from the database server, it works fine. And can do it as many times as i please, whithout errors.
like this:

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql"
And
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql"

But it still drops the connection to the user elsewhere, on the first time it's used after creation.
Eg. First i drop the user, then i create it again with the scripts. Then i open a browser and goes to my website, wich uses this user to connect to the database to retrive and update information. Then the first time i try to login i get the error below. Then i push "F5" to refreash the page and all works fine again?

Soucre:

.Net SqlClient Data Provider

Error Message:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

|||

My only guess is that your web application is somehow caching/reusing the old connection (with the old login), and when you refresh the page it reestablishes the connection. Remember that when you drop and create a login it is a whole new principal, not the same one (even if the name and password is the same), and any established connection will be invalidated and the connection will be forcedly terminated by the server the next time any application tries to use it.

I would recommend using SQL profiler to trace back new/closed connections as well as existing sessions to try to detect if this is indeed the case.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

Thats also what im comming to. I haven't tryed the SQL profiler yet, but will do this later.

But i have found out that it's not enough only to stop and start the web site again on IIS. I have to restart the hole "IIS" to make the "refreash". So im thinking that the session is not terminated correctly from the website, when closing the browser/session?.

|||

Besides your investigation on why (and if) the web application is reusing the connection instead of reconnecting, I have one question for you: are you dropping and recreating this user in order to create a whole new principal (i.e. a new SID, with no existing objects, etc.), or just as a mechanism to prevent this principal from establishing a new connection?

If you are using these scripts to prevent new connections, you may also want to consider disabling the login instead. By disabling the login, you prevent new connections without affecting existing ones, and without the need to cleanup any objects or references (i.e. users) this principal may have in the system. You can find the syntax for this operation in ALTER LOGIN (http://msdn2.microsoft.com/en-us/library/ms189828.aspx).

-Raul Garcia

SDE/T

SQL Server Engine

|||

Actually im useing thise scripts as a part of my deploymentkit. This is to ensure that all securitysettings to any databases, stored procedures etc are droped. So i afterwards can replace them with either the same or new ones, without leaving anything behind.

But i took the change and implemented it into my deployment. The first time i got the same error about the "transport-level error". But that was because i've missed another scipt later in the installation, it was some inserts-stament i tried to execute from a vb SqlConnection. Then i moved those two things around, so it was the last code and then it worked fine, i can now open my webpage without the "transport-level error" :) yeah .. even after installing, uninstalling, installing ....

So i have to go back to the websession-closing, in my case, because under the "Activity Monitor" in sql, i can see a connection long after closing the browser. So this is now another problem for me, to find a way to kill this one.

But im stil interested in fixing the other problem, because it's annoying that things has to come in the correct order.. And the differens between executing it from a sql-query (in sql 2005), from command promt and from my msi-package. -Wich by the way executes it by calling a shell in vb wich runs a .cmd-file containing the two sql-scripts.

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql" -o "C:\DropDBUser.log"
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql" -o "C:\CreateDBUser.log"

But for the moment im happy :)

|||

I’m glad to hear that. Feel free to let us know if you have further security questions in SQL Server, we are always looking forward to help our customers whenever we have a chance.

-Raul Garcia

SDE/T

SQL Server Engine

sql

Question regarding storing session with set context_info

Currently our need is to track the data that is changed by the user and record it as a part of the Audit Log.

We have accomplished this need ny making use ofset context_infoand also by passing the UserId from all the Layers and finally log the user name using the set context_info in the stored proc.

What we thought of was using the trigger for each update,delete records for the table.So by setting the context in teh stored proc we could access the UserId in the trigger and able to record the changes made by Tagged userId.

What we want to make sure before making this as our permananet solution is

(1) Reliability - In a web application (N Tier) how would the context not get out of scope.

2) any Performannce related issues, you guys can think of.

3) This Context used in the database -- would that be the part of the connection used from the UI, meaning the if a request is made from UI to the Database . would the context be alive till the connection doesn't go back to the connection pool.

Thanks

Sweety.

No answers yet.

Any idea wether this approach would be good or not.

Need your Input

Thanks

Question regarding Security and user authentication

Hey. We have an application which is opened by a user to enter in their
username and password. This data resides in SQL 2000 but I'm upgrading to SQ
L
2005. I need to store the passwords of users in an encrypted format. How
would I do this?
Can you provide with the steps for the procedure? thank you.Have you tried looking in BOL?
How To: Encrypt a Column of Data
<http://msdn2.microsoft.com/en-us/library/ms179331.aspx>
*mike hodgson*
http://sqlnerd.blogspot.com
Tejas Parikh wrote:

>Hey. We have an application which is opened by a user to enter in their
>username and password. This data resides in SQL 2000 but I'm upgrading to S
QL
>2005. I need to store the passwords of users in an encrypted format. How
>would I do this?
>Can you provide with the steps for the procedure? thank you.
>sql

Question regarding Security and user authentication

Hey. We have an application which is opened by a user to enter in their
username and password. This data resides in SQL 2000 but I'm upgrading to SQL
2005. I need to store the passwords of users in an encrypted format. How
would I do this?
Can you provide with the steps for the procedure? thank you.This is a multi-part message in MIME format.
--010100040609020901020201
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Have you tried looking in BOL?
How To: Encrypt a Column of Data
<http://msdn2.microsoft.com/en-us/library/ms179331.aspx>
--
*mike hodgson*
http://sqlnerd.blogspot.com
Tejas Parikh wrote:
>Hey. We have an application which is opened by a user to enter in their
>username and password. This data resides in SQL 2000 but I'm upgrading to SQL
>2005. I need to store the passwords of users in an encrypted format. How
>would I do this?
>Can you provide with the steps for the procedure? thank you.
>
--010100040609020901020201
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Have you tried looking in BOL?<br>
<br>
<a href="http://links.10026.com/?link=How">http://msdn2.microsoft.com/en-us/library/ms179331.aspx">How
To: Encrypt a Column of Data</a><br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tejas Parikh wrote:
<blockquote cite="midCD30DC41-20C2-4E08-9668-87E020E95E12@.microsoft.com"
type="cite">
<pre wrap="">Hey. We have an application which is opened by a user to enter in their
username and password. This data resides in SQL 2000 but I'm upgrading to SQL
2005. I need to store the passwords of users in an encrypted format. How
would I do this?
Can you provide with the steps for the procedure? thank you.
</pre>
</blockquote>
</body>
</html>
--010100040609020901020201--

question part 2

sorry.. should have added this to help...
Oldest active transaction:
> SPID (server process ID) : 144
> UID (user ID) : 6
> Name : implicit_transaction
> LSN : (106228:47115:1)
> Start time : Jan 28 2004 11:55:08:840AM
> DBCC execution completed. If DBCC printed error
messages, contact your system administrator.>--Original Message--
>sorry.. should have added this to help...
>Oldest active transaction:
>> SPID (server process ID) : 144
>> UID (user ID) : 6
>> Name : implicit_transaction
>> LSN : (106228:47115:1)
>> Start time : Jan 28 2004 11:55:08:840AM
>> DBCC execution completed. If DBCC printed error
>messages, contact your system administrator.
>.
>
P.S can anyone tell me how to see if its turn on or not...
sql server wiese... i now "set" will turn it off and on
but how can i check its current status.. if i turn it off
will it be for just THAT database or all of the server

Question on Users and Logins

Hi All,
How can we reveal the users? I was trying to do import exports from one
database to another .Source database is containing one user which is not
present in destination database . It raises an error of "login is not
available " , Is there any other way to import /export whole database with
logins and users as well .
Thanks & Regards,
Swati
This is from Microsoft
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
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 OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> How can we reveal the users? I was trying to do import exports from one
> database to another .Source database is containing one user which is not
> present in destination database . It raises an error of "login is not
> available " , Is there any other way to import /export whole database with
> logins and users as well .
>
> Thanks & Regards,
> Swati
>
>
|||Hi Uri , I have tried this , but this is not solving purpose . This is
creating a logins on different database , but users which are present is not
copied to another server .
I have to expiclitely create same users on different server ..Pls suggest me
..
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzF6b8tyEHA.1264@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> This is from Microsoft
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> 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 OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
with
>
|||swati
All database users are stored in sysuser system table.
By using RESTORE DATABASE command you are moved the users of the database.
and then run these sp from the article.
http://support.microsoft.com/default...en-us;246133#4
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uuw1GFuyEHA.2752@.TK2MSFTNGP11.phx.gbl...
> Hi Uri , I have tried this , but this is not solving purpose . This is
> creating a logins on different database , but users which are present is
not
> copied to another server .
> I have to expiclitely create same users on different server ..Pls suggest
me[vbcol=seagreen]
> .
> Regards,
> Swati
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OzF6b8tyEHA.1264@.TK2MSFTNGP12.phx.gbl...
one[vbcol=seagreen]
not
> with
>
|||Hi Swati,
To transfer users between servers you can use DTS.
Open a new DTS package and click on Transfer Login task, it will ask you
source and destination server. Once the Logins are transferred to you new
server you need to add those logins to your taget database. If the database
is restored then you will be having these users. If you dont see these users
in the database then script the source database. Right Click -- > All Tasks
--> Generate Sql Script --> options Tab--> Check ,script sql server logins.
Now run this sql script on your target database. You will get all the users
to your target database.
To add login to a database it needs to be present in sql server, so run the
DTS to transfer Logins to target server first if your source database and
target database are on different servers.
Regards,
Arshad
"Uri Dimant" wrote:

> swati
> All database users are stored in sysuser system table.
> By using RESTORE DATABASE command you are moved the users of the database.
> and then run these sp from the article.
> http://support.microsoft.com/default...en-us;246133#4
>
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uuw1GFuyEHA.2752@.TK2MSFTNGP11.phx.gbl...
> not
> me
> one
> not
>
>
sql

Question on Users and Logins

Hi All,
How can we reveal the users? I was trying to do import exports from one
database to another .Source database is containing one user which is not
present in destination database . It raises an error of "login is not
available " , Is there any other way to import /export whole database with
logins and users as well .
Thanks & Regards,
SwatiThis is from Microsoft
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
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 OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> How can we reveal the users? I was trying to do import exports from one
> database to another .Source database is containing one user which is not
> present in destination database . It raises an error of "login is not
> available " , Is there any other way to import /export whole database with
> logins and users as well .
>
> Thanks & Regards,
> Swati
>
>|||Hi Uri , I have tried this , but this is not solving purpose . This is
creating a logins on different database , but users which are present is not
copied to another server .
I have to expiclitely create same users on different server ..Pls suggest me
.
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzF6b8tyEHA.1264@.TK2MSFTNGP12.phx.gbl...
> This is from Microsoft
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> 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 OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
with[vbcol=seagreen]
>|||swati
All database users are stored in sysuser system table.
By using RESTORE DATABASE command you are moved the users of the database.
and then run these sp from the article.
http://support.microsoft.com/defaul...;en-us;246133#4
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uuw1GFuyEHA.2752@.TK2MSFTNGP11.phx.gbl...
> Hi Uri , I have tried this , but this is not solving purpose . This is
> creating a logins on different database , but users which are present is
not
> copied to another server .
> I have to expiclitely create same users on different server ..Pls suggest
me
> .
> Regards,
> Swati
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OzF6b8tyEHA.1264@.TK2MSFTNGP12.phx.gbl...
one[vbcol=seagreen]
not[vbcol=seagreen]
> with
>|||Hi Swati,
To transfer users between servers you can use DTS.
Open a new DTS package and click on Transfer Login task, it will ask you
source and destination server. Once the Logins are transferred to you new
server you need to add those logins to your taget database. If the database
is restored then you will be having these users. If you dont see these users
in the database then script the source database. Right Click -- > All Tasks
--> Generate Sql Script --> options Tab--> Check ,script sql server logins.
Now run this sql script on your target database. You will get all the users
to your target database.
To add login to a database it needs to be present in sql server, so run the
DTS to transfer Logins to target server first if your source database and
target database are on different servers.
Regards,
Arshad
"Uri Dimant" wrote:

> swati
> All database users are stored in sysuser system table.
> By using RESTORE DATABASE command you are moved the users of the database.
> and then run these sp from the article.
> http://support.microsoft.com/defaul...;en-us;246133#4
>
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uuw1GFuyEHA.2752@.TK2MSFTNGP11.phx.gbl...
> not
> me
> one
> not
>
>

Question on Users and Logins

Hi All,
How can we reveal the users? I was trying to do import exports from one
database to another .Source database is containing one user which is not
present in destination database . It raises an error of "login is not
available " , Is there any other way to import /export whole database with
logins and users as well .
Thanks & Regards,
SwatiThis is from Microsoft
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
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 OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> How can we reveal the users? I was trying to do import exports from one
> database to another .Source database is containing one user which is not
> present in destination database . It raises an error of "login is not
> available " , Is there any other way to import /export whole database with
> logins and users as well .
>
> Thanks & Regards,
> Swati
>
>|||Hi Uri , I have tried this , but this is not solving purpose . This is
creating a logins on different database , but users which are present is not
copied to another server .
I have to expiclitely create same users on different server ..Pls suggest me
.
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzF6b8tyEHA.1264@.TK2MSFTNGP12.phx.gbl...
> This is from Microsoft
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> 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 OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > How can we reveal the users? I was trying to do import exports from one
> > database to another .Source database is containing one user which is not
> > present in destination database . It raises an error of "login is not
> > available " , Is there any other way to import /export whole database
with
> > logins and users as well .
> >
> >
> > Thanks & Regards,
> > Swati
> >
> >
> >
> >
>|||swati
All database users are stored in sysuser system table.
By using RESTORE DATABASE command you are moved the users of the database.
and then run these sp from the article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133#4
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uuw1GFuyEHA.2752@.TK2MSFTNGP11.phx.gbl...
> Hi Uri , I have tried this , but this is not solving purpose . This is
> creating a logins on different database , but users which are present is
not
> copied to another server .
> I have to expiclitely create same users on different server ..Pls suggest
me
> .
> Regards,
> Swati
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OzF6b8tyEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > This is from Microsoft
> >
> > USE master
> > GO
> > IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> > DROP PROCEDURE sp_hexadecimal
> > GO
> > CREATE PROCEDURE sp_hexadecimal
> > @.binvalue varbinary(256),
> > @.hexvalue varchar(256) OUTPUT
> > AS
> > DECLARE @.charvalue varchar(256)
> > 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 OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> > DROP PROCEDURE sp_help_revlogin
> > GO
> > CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> > DECLARE @.name sysname
> > DECLARE @.xstatus int
> > DECLARE @.binpwd varbinary (256)
> > DECLARE @.txtpwd sysname
> > DECLARE @.tmpstr varchar (256)
> > DECLARE @.SID_varbinary varbinary(85)
> > DECLARE @.SID_string varchar(256)
> >
> > IF (@.login_name IS NULL)
> > DECLARE login_curs CURSOR FOR
> > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > WHERE srvid IS NULL AND name <> 'sa'
> > ELSE
> > DECLARE login_curs CURSOR FOR
> > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > WHERE srvid IS NULL AND name = @.login_name
> > OPEN login_curs
> > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > IF (@.@.fetch_status = -1)
> > BEGIN
> > PRINT 'No login(s) found.'
> > CLOSE login_curs
> > DEALLOCATE login_curs
> > RETURN -1
> > END
> > SET @.tmpstr = '/* sp_help_revlogin script '
> > PRINT @.tmpstr
> > SET @.tmpstr = '** Generated '
> > + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> > PRINT @.tmpstr
> > PRINT ''
> > PRINT 'DECLARE @.pwd sysname'
> > WHILE (@.@.fetch_status <> -1)
> > BEGIN
> > IF (@.@.fetch_status <> -2)
> > BEGIN
> > PRINT ''
> > SET @.tmpstr = '-- Login: ' + @.name
> > PRINT @.tmpstr
> > IF (@.xstatus & 4) = 4
> > BEGIN -- NT authenticated account/group
> > IF (@.xstatus & 1) = 1
> > BEGIN -- NT login is denied access
> > SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> > PRINT @.tmpstr
> > END
> > ELSE BEGIN -- NT login has access
> > SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> > PRINT @.tmpstr
> > END
> > END
> > ELSE BEGIN -- SQL Server authentication
> > IF (@.binpwd IS NOT NULL)
> > BEGIN -- Non-null password
> > EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> > IF (@.xstatus & 2048) = 2048
> > SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> > ELSE
> > SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> > PRINT @.tmpstr
> > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > END
> > ELSE BEGIN
> > -- Null password
> > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > END
> > IF (@.xstatus & 2048) = 2048
> > -- login upgraded from 6.5
> > SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> > ELSE
> > SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> > PRINT @.tmpstr
> > END
> > END
> > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > END
> > CLOSE login_curs
> > DEALLOCATE login_curs
> > RETURN 0
> > GO
> >
> > sp_help_revlogin
> >
> >
> >
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > How can we reveal the users? I was trying to do import exports from
one
> > > database to another .Source database is containing one user which is
not
> > > present in destination database . It raises an error of "login is not
> > > available " , Is there any other way to import /export whole database
> with
> > > logins and users as well .
> > >
> > >
> > > Thanks & Regards,
> > > Swati
> > >
> > >
> > >
> > >
> >
> >
>|||Hi Swati,
To transfer users between servers you can use DTS.
Open a new DTS package and click on Transfer Login task, it will ask you
source and destination server. Once the Logins are transferred to you new
server you need to add those logins to your taget database. If the database
is restored then you will be having these users. If you dont see these users
in the database then script the source database. Right Click -- > All Tasks
--> Generate Sql Script --> options Tab--> Check ,script sql server logins.
Now run this sql script on your target database. You will get all the users
to your target database.
To add login to a database it needs to be present in sql server, so run the
DTS to transfer Logins to target server first if your source database and
target database are on different servers.
Regards,
Arshad
"Uri Dimant" wrote:
> swati
> All database users are stored in sysuser system table.
> By using RESTORE DATABASE command you are moved the users of the database.
> and then run these sp from the article.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;246133#4
>
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uuw1GFuyEHA.2752@.TK2MSFTNGP11.phx.gbl...
> > Hi Uri , I have tried this , but this is not solving purpose . This is
> > creating a logins on different database , but users which are present is
> not
> > copied to another server .
> > I have to expiclitely create same users on different server ..Pls suggest
> me
> > .
> >
> > Regards,
> > Swati
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OzF6b8tyEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > > This is from Microsoft
> > >
> > > USE master
> > > GO
> > > IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> > > DROP PROCEDURE sp_hexadecimal
> > > GO
> > > CREATE PROCEDURE sp_hexadecimal
> > > @.binvalue varbinary(256),
> > > @.hexvalue varchar(256) OUTPUT
> > > AS
> > > DECLARE @.charvalue varchar(256)
> > > 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 OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> > > DROP PROCEDURE sp_help_revlogin
> > > GO
> > > CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> > > DECLARE @.name sysname
> > > DECLARE @.xstatus int
> > > DECLARE @.binpwd varbinary (256)
> > > DECLARE @.txtpwd sysname
> > > DECLARE @.tmpstr varchar (256)
> > > DECLARE @.SID_varbinary varbinary(85)
> > > DECLARE @.SID_string varchar(256)
> > >
> > > IF (@.login_name IS NULL)
> > > DECLARE login_curs CURSOR FOR
> > > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > > WHERE srvid IS NULL AND name <> 'sa'
> > > ELSE
> > > DECLARE login_curs CURSOR FOR
> > > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > > WHERE srvid IS NULL AND name = @.login_name
> > > OPEN login_curs
> > > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > > IF (@.@.fetch_status = -1)
> > > BEGIN
> > > PRINT 'No login(s) found.'
> > > CLOSE login_curs
> > > DEALLOCATE login_curs
> > > RETURN -1
> > > END
> > > SET @.tmpstr = '/* sp_help_revlogin script '
> > > PRINT @.tmpstr
> > > SET @.tmpstr = '** Generated '
> > > + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> > > PRINT @.tmpstr
> > > PRINT ''
> > > PRINT 'DECLARE @.pwd sysname'
> > > WHILE (@.@.fetch_status <> -1)
> > > BEGIN
> > > IF (@.@.fetch_status <> -2)
> > > BEGIN
> > > PRINT ''
> > > SET @.tmpstr = '-- Login: ' + @.name
> > > PRINT @.tmpstr
> > > IF (@.xstatus & 4) = 4
> > > BEGIN -- NT authenticated account/group
> > > IF (@.xstatus & 1) = 1
> > > BEGIN -- NT login is denied access
> > > SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> > > PRINT @.tmpstr
> > > END
> > > ELSE BEGIN -- NT login has access
> > > SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> > > PRINT @.tmpstr
> > > END
> > > END
> > > ELSE BEGIN -- SQL Server authentication
> > > IF (@.binpwd IS NOT NULL)
> > > BEGIN -- Non-null password
> > > EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> > > IF (@.xstatus & 2048) = 2048
> > > SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> > > ELSE
> > > SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> > > PRINT @.tmpstr
> > > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > > + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > > END
> > > ELSE BEGIN
> > > -- Null password
> > > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > > + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > > END
> > > IF (@.xstatus & 2048) = 2048
> > > -- login upgraded from 6.5
> > > SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> > > ELSE
> > > SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> > > PRINT @.tmpstr
> > > END
> > > END
> > > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > > END
> > > CLOSE login_curs
> > > DEALLOCATE login_curs
> > > RETURN 0
> > > GO
> > >
> > > sp_help_revlogin
> > >
> > >
> > >
> > >
> > > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > > news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
> > > > Hi All,
> > > >
> > > > How can we reveal the users? I was trying to do import exports from
> one
> > > > database to another .Source database is containing one user which is
> not
> > > > present in destination database . It raises an error of "login is not
> > > > available " , Is there any other way to import /export whole database
> > with
> > > > logins and users as well .
> > > >
> > > >
> > > > Thanks & Regards,
> > > > Swati
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Question on Settings in Connection

I've programmed a user defined function (SQL2000), which in a specific query
references a linked server (another SQL instance, BTW contained in same
physical server). The sintaxis is ok, but i couldn't apply the definition
because of following error:
"Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistente
query semantics. Enable these options and then reissue your query."
I set the corresponding settings in both servers, section Connections of
Server's properties, but to no avail.
Which is the trick here? How is resolved the 'connection' issue referred in
the error message?
Thanks in advanceMiguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> I've programmed a user defined function (SQL2000), which in a specific
> query references a linked server (another SQL instance, BTW contained in
> same physical server). The sintaxis is ok, but i couldn't apply the
> definition because of following error:
> "Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
> options to be set for the connection. This ensures consistente
> query semantics. Enable these options and then reissue your query."
> I set the corresponding settings in both servers, section Connections of
> Server's properties, but to no avail.
> Which is the trick here? How is resolved the 'connection' issue referred
> in the error message?
The trick is to stop using Enterprise Manager for editing functions and
stored procedures. Use Query Analyzer instead, this is a far better tool
for the task.
The particular problem here, is that Enterprise Manager creates functions
and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
settings are saved with the procedure/function. Thus you need to recreate
the function with ANSI_NULLS ON. (In Query Analyzer all needed options
are ON by default.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many thanks, it resolves the problem.
In ahead I will take this great tip in account.
"Erland Sommarskog" wrote:

> Miguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> The trick is to stop using Enterprise Manager for editing functions and
> stored procedures. Use Query Analyzer instead, this is a far better tool
> for the task.
> The particular problem here, is that Enterprise Manager creates functions
> and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
> settings are saved with the procedure/function. Thus you need to recreate
> the function with ANSI_NULLS ON. (In Query Analyzer all needed options
> are ON by default.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Question on reading from SQL SERVER

ok I use a datareader..Perhaps not the best method..but...if a better one exsits please let me know...
The problem...
I get info on a user...It returns an unknown amount of rows back...lets say two for instance...

ok...
How do I tell the number of rows...also the ability to reference each row and items in that row...
..
i.e. Row contains this

uname | password | address | Phone | notes

Like I said could contain multiple rows
I need to figure out info for specific rows and items in it...
in datareader I know you can reference each item which I guess you could use x number of variables to get each item in the row but that is tedious...
IS there a better answer??Use a DataAdapter/DataTable then.|||Perhaps you have a code example of how this might be attained|||Yes I do. And so does the rest of the sites google links to when you search those two.

DataTable oDataTable = new DataTable;
SqlDataAdapter oDataAdapter = new SqlDataAdapter(YourCommandObject);
oDataAdapter.Fill(oDataTable);
oDataAdapter.Dispose();
// close your objects
for (int i=0;i<oDataTable.Rows.Count;i++) {
 for (int j=0;j<oDataTable.Columns.Count;j++) {
 Console.WriteLine(oDataTable.Columns[j].ColumnName + " = " + oDataTable.Rows[i][j].ToString());
 }
}
oDataTable.Dispose();

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
Paul G
Software engineer.
Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
Paul G
Software engineer.
"Chris Wood" wrote:

> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL process
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to SQL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>
>
|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...[vbcol=seagreen]
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
--
Paul G
Software engineer.Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
--
Paul G
Software engineer.
"Chris Wood" wrote:

> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL proces
s
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to S
QL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>
>|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...[vbcol=seagreen]
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:
>

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
--
Paul G
Software engineer.Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
--
Paul G
Software engineer.
"Chris Wood" wrote:
> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL process
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to SQL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
> >I am a recent user of sql2005 but have worked with sql2000 for a few years.
> > I noticed under the sql server agent there is a listing for proxy. I have
> > used the sql server agent to set up jobs to run packages developed with
> > Integration services. I was wondering if anyone had a simple explination
> > of
> > proxys and how/why to set them up.
> > Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:
>> Paul,
>> My take on the proxy is if you need to run something from inside SQL that
>> needs to go outside of SQL you need the proxy account. So if an SQL
>> process
>> needs to run xp_cmdshell then you need the proxy account. We have cases
>> where we start inside SQL and go outside to another server that runs a
>> process inside another SQL server. Here the proxy is needed to go outside
>> and needs to be defined on the other server and possibly given access to
>> SQL
>> resources inside the other SQL Server otherwise it will not work.
>> Clear as mud!
>> To use xm\p_cmdshell you need to make it available SAC and give
>> permission
>> to run it and then either create the proxy Under
>> Server/Properties/Security/Server proxy Account or by using the system sp
>> sp_xp_cmdshell_proxy_account. They both do the same thing.
>> HTH
>> Chris
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>> >I am a recent user of sql2005 but have worked with sql2000 for a few
>> >years.
>> > I noticed under the sql server agent there is a listing for proxy. I
>> > have
>> > used the sql server agent to set up jobs to run packages developed with
>> > Integration services. I was wondering if anyone had a simple
>> > explination
>> > of
>> > proxys and how/why to set them up.
>> > Thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>