I need to transfer the logins and passwords from my prod server to my DR server. I've seen a number of articles about how to transfer logins; but, I'm not finding anything on how to transfer both logins and their passwords. SQL server R2008 R2
View 4 RepliesI have versions of sp_hexadecimal and sp_help_revlogin I used with SQL 2000 to transfer logins. Will they work with SQL 2005 as-is? Might I need updated versions?
View 2 Replies View RelatedI€™m using express edition of SQL server 2005. 9.00.1399.06 (Intel X86)
I need to transfer all SQL logins and passwords from one server to another.
The script at http://support.microsoft.com/kb/246133 (designed for sqlserver 7.0 and 2000) This script references a system table (master..syslogins) that may not exits in sqlserver 2005. I get the following error:
EXEC master..sp_help_revlogin
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
Would SMO be something to try for transfering logins and passwords?
Thanks,
Karl
Hi All,
Sql server 7
There are set of logins in sql server some are NT authentication and some are sql server authentication. How can i retrieve the passwords for both these logins.
TIA
Adil
Hi,
WE are moving one of our applications to oracle from sql server.For this migration is there any way I can move passwords from sql server syslogins table to oracle or extract the passwords.
thanks in advance
Mohan
Hi,
I want to delete N number of logins today and restore them 5 days later.
What steps should be done beside backup of master.
what whould be the way to restore logins with passwords (with or without permissions )without restoring master?
I gess just running sp_resolve_logins
will not help.
thank you
Alex
How do I generate a script of my users logins and passwords?
View 2 Replies View RelatedDoes anyone have a script or tip on how to move passwords associated with logins. I am consolidating two 6.5s into one and want to maintain the passwords associated with the logins. Any hints?
View 2 Replies View RelatedHello,
I am creating a new sql server 2000 machine and was wondering how to get the users and passwords from the sql6.5 box over onto the new machine. I know for sql 6.5 there is a stored proc sp_TransferPasswords that I have used but it will not script over cleanly to the sql 2000 box.
Thanks,
Steve
I can backup my logins but without passwords....using SQL 2005 , generate sql scripts...
any idea i can generate a SQL script with the passwords in cleartext? i like to recreate them in my test database later.
How do you transfer the user logins with the encrypted passwords from one DB to another. Help!
View 1 Replies View RelatedWondering on how to script over the passwords from one 2000 box to another. We are cutting a box over from dev to production and need to copy userids and passwords from another box.
Any suggestions?
Thanks,
Steve
I would like to know what procedures are required to transfer passwords from my Production server to my Test server.
View 1 Replies View RelatedHI,
If i transfer logins from one server to another server using dts, I am getting all the logins and permissions... How will i get passwords too..
-thank u.
--Siva
Does anyone have a sample SQL DMO script to transfer SQL Server logins from one server to another including the passwords ? any pointers will be greatly appreciated
View 1 Replies View RelatedI would like to know how I can transfer all Login and password and access records from one SQL Server to another, without transferring the databases. I know I can do this via DTS, but I can not figure out the right combination for bringing over the login names. passwords, and roles, with out transferring the databases. The databases have been transfered already via a backup and restore process. Now I need all of the login infomation from the old server to come across. In DTS I go through the Wizard saying to transfer the Master Database from one server to the other, and transfer objects and Data between SQL Server databases. Under the selcet options to transfer page, I uncheck the "Use Default Options" , and on the options page I select all of the "Security options" and deselect all of the "Table options" (Indexes, triggers, keys, etc). The process runs for a short time before bombing out with a failure. I can not find a log file under the Log folder to tell me where it has failed. Can anyone one lend me a hand with this?
TIA
Ken Merrigan
Hi all
In the process of migration of a database from one SQL 2000 server to another, I am transfering the logins using the microsoft documented script
http://support.microsoft.com/kb/246133/
When i am executing the script on the source server, I am getting the following error.
Server: Msg 195, Level 15, State 10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.
In all the past migrations , I have used this without any issues, but not sure what have changed.
Or am i doing something really stupid.
Can you guys pls execute this in your server and see whether you are able to create the sp_help_revlogin stored procedure.
Thanks in advance
Sreenath
Could anybody tell me whetherTransfer logins task transfers passwords for logins or not?
View 1 Replies View RelatedAs we all know logins and jobs aren't replicated when using AllwaysOn. What i wanna know is if the "tranfer login taks" will be enough to cover the transfer of logins between servers.
View 7 Replies View RelatedHi,
View 4 Replies View RelatedHi
How to transfer logins from 2000 to 2005?
Raj.
I need to make a general package to copy SQL databases between servers.
A package with fixed task properties work fine.
But my plan is to make a general package and use varables to handle parameters like connection strings, server names, database names etc.
My problem is the Transfer Logins Task and its collection properties like LoginsList and DatabasesList which uses string collections.
In my understanding I can't make a ordinary variable in "design mode" for string collection. For that reason I wrote some code in a Script Task to set an Object variable to a string collection. And then use the object variable to set the LoginsList or DatabasesList property.
But I get following error:
TITLE: Microsoft Visual Studio
------------------------------
Nonfatal errors occurred while saving the package:
Error at Transfer Logins Task: The data type of variable "User:ourceDB_list_o" is not supported in an expression.
Error at Transfer Logins Task: Reading the variable "User:ourceDB_list_o" failed with error code 0xC00470D0.
Error at Transfer Logins Task: The expression "@[User:ourceDB_list_o]" on property "DatabasesList" cannot be evaluated. Modify the expression to be valid.
My code to set the object variable:
Dim DBs As System.Collections.Specialized.StringCollection = New System.Collections.Specialized.StringCollection()
DBs.Add(Dts.Variables(0).Value.ToString())
Dts.ExecutionValue = DBs
Grateful for any guidance.
Regards, Jonas
I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.
I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.
The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.
declare @name varchar(200)
declare @object varchar(200)
DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
select table_name,table_type from INFORMATION_SCHEMA.TABLES
union
Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
union
select name,'FN' from sys.objects where type_desc like '%FUNCTION'
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @name,@object
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
if @object = 'BASE TABLE'
begin
exec ('drop table ' + @name)
end
else if @object = 'VIEW'
begin
exec ('drop view ' + @name)
end
else if @object = 'SPROC'
begin
exec ('drop procedure ' + @name)
end
else if @object = 'FN'
begin
exec ('drop function ' + @name)
end
FETCH NEXT FROM object_cursor INTO @name,@object
END
CLOSE object_cursor
DEALLOCATE object_cursor
Users were able tolog on to our SQL 2000 servers with their passwords being case-insensitive.
Now with SQL 2005 some users can't logon because SQL 2005 passwords are case-sensitive.
Can SQL 2005 be configured so that passwords are NOT case sensitive???
Thanks!
I have a stored procedure which will send an email notification everyday the count of names and count of logins at night. And this sends like a link so the users can directly click on the link(s) and see who all logged in. This works perfectly fine.
Now my requirement is that if there is no count for either name or logins it should send just as text and not as link.
Select count (name) from dbo.test=0 just plain 'text total count of names 0'
Select count (logins) from dbo.test1=0 just plain text 'total count of logins 0'
I am trying to transfer all jobs from one instance to another by using data tools. However, once i tried to make smo connection i am getting this error;
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
In order to solve this issue i have tried these solutions;
1. SQL Server should be up and running. (OK)
2. Enable TCP/IP in SQL Server Configuration (OK)
3. Open Port in Windows Firewall (FW ACCEPTS ALL LOCAL PORTS)
4. Enable Remote Connection (CHECK OUT THE sp_configure SETTINGS, i even right-click instance then see from properties )
5. Enable SQL Server Browser Service (sql server browser has been restarted)
6. Create exception of sqlbrowser.exe in Firewall (FW ACCEPTS ALL PROGRAMS)
7. I tried windows and sql authentication which has sysadmin role
8. INSTANCE name is also chekced millions of times
I am replacing a server so I need to migrate everything. The old server is running SQL2000 and the new server is running SQL2005. I am trying to write an SSIS solution to migrate everything for me and I can't even get started because I get the error "The source server can not be the same as the destination server". At the same time I am changing the name of the Domain so the two machines arenot even members of the same Domain. I am doing this over the Internet so the machines are not even on the same subnet. The only thing I can think of is that the machine names are the same so even though the domains are different therefore the full names are different, the NetBIOS names are the same. Could that be the problem?
View 1 Replies View Relatedok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Any assistance would be greatly appreciated.
Thanks,
John
Transfer tables with Data from one database to another one on a same server?Â
View 10 Replies View RelatedI have few issues regarding the transfer of the tables from one file group to another file group  in SQL 2008 and also How can we  backup and restore the particular database based on file group level.
Let’s say I have a tables stored within the different FG. such as
Tables                                                   Â
                                    Â
File group Dimension tables       Primary Fact tables                       Â
FG1 ... Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
FG2…
zzz_tables                        DEFAULT_FG   Â
dim.table1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â DEFAULT_FG
dim.table2Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â DEFAULT_FG
…                                                                                                                                             ….
Here all I want to transfer the dim.table1 ,dim.table2  from  DEFAULT_FG to the Primary File group .So is there simple methods for transfer the dim.table1,2  from one FG to another .I have tried somewhat but I couldn’t get the exact way.Secondly after moving those dim.table1 ,dim.table2 from DEFAULT_FG to Primary ,All I want to backup and restore the database only containing  the Primary and FG1,FG2… not a DEFAULT_FG.Is it possible or not.?
I have a requirements to collect Transactions per second from a sql server instances level. Any script to collect TPS in the instance level ?
View 5 Replies View RelatedHaving problems running the BPA for 2012. We setup using a clustered environment, SQL Server installed using default instance, and a static port not 1433. When I try to run the BPA using the instance name it says it doesn't exist, and won't let me add a port number. However, if I use MSSQLSERVER as the instance name it runs with no port specified and returns results. I assume that it doesn't require a port because the default instance would pull from the Browser Service and the using MSSQLSERVER is the same as running the default on a standalone server. Are those assumptions correct?
View 2 Replies View RelatedWe have an app that supports any version of sql beyond 2008. Â What we are finding is that users are upgrading to new versions of sql and just restoring their db on the new version of sql and not changing the compatibility level. Â I would like a script that would change the db's compatibility level to match whatever version of sql that they are using.
ALTER DATABASE db_name
SET COMPATIBILITY_LEVEL = (SELECT TOP 1 compatibility_levelÂ
 FROM sys.databases
 WHERE name = 'master')