Copying/Moving User Accounts...how?
Sep 27, 2006
Ok. So we just moved over one DB from our original server to another server. The DB copied over fine.
Our next step is trying to see if there is a way to move accounts. Is that possible?
We have about 40 accounts (using SQL authentication) on the original server. Is there a way to move or copy those accounts to the new SQL server that is holding that same DB?
Haven't found anything in the Enterprise manager, so im thinking it will be something to do in the SQL analyzer.
My lack of knowledge in SQL is not helping me much here.
I appreciate the help.
View 17 Replies
ADVERTISEMENT
Jul 23, 1999
Does anyone know how to move or copy DTS Package?
That I need to do:
I would have to move database from one server to the other
and last thing I want to do is recreate DTS packages from
scrach.
I could not find any way of transfering DTS packages.
Any help greatly appreciated
View 3 Replies
View Related
Oct 4, 2007
Hello everyone!
I have two servers, one is 2005 standard, the other is 2005 express.
I'm using express to record test results and it will be keep recording non-stop.
So, what I want to do is I want to transfer data from express to standard using SSIS package, and as soon as data gets transferred the data to no longer exists in express.
I have a job running the "copy" package every minute, but I don't know how to delete the data in express.
Does anyone know how to help me out pleaseeeee..?
Thanks in advance.
MIKE!
View 6 Replies
View Related
May 30, 2006
hi,
When you copy a sequence container between dtsx you obtain a different size at destination. Any way to avoid this? I'd like to see that task with the same size.
Maybe some posh from my side...
View 1 Replies
View Related
Jan 17, 2005
Hi,
how do you create a username and password for a database in SQL.
Thanks
View 3 Replies
View Related
Oct 22, 2007
Hi
I have a (possibly) common position where half of our IT department is SQL 2005 and the rest is SQL2000. For myself, having to work in a SQL2000 environment and needing data from a SQL2005 Cluster I came up with this solution.
Also, to alert me when the process starts and completes since it is part of a scheduled process, I have it do a RAISERROR with logging to record entry and exit times. In addition, this runs out a series of PRINT statements that lets the operator know what table is currently being worked on.
Of course, any suggestions for speeding this up would be helpful!
What I have found that works for getting data (albeit slow) from SQL 2005 down to SQL 2000 is to script a fairly simple copy process -
-- it is actually pretty easy to follow if you just read the code......
-- first, we find all the views which are present, so they can be ignored when copying the raw data
CREATE TABLE #VIEWS (TABLE_NAME NVARCHAR (255) )
INSERT #VIEWS
SELECT TABLE_NAME FROM
OPENDATASOURCE(
'SQLOLEDB',
Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' )
.SQL_SIS_SYSTEM.INFORMATION_SCHEMA.VIEWS
-- now, we get the actual tables with data
CREATE TABLE #TEMP (TBL_SCHEMA VARCHAR (12), TBL_NAME VARCHAR (255), RECCNT INT )
INSERT #TEMP
SELECT TABLE_SCHEMA , TABLE_NAME FROM
OPENDATASOURCE( 'SQLOLEDB', 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' )
.SQL_SIS_SYSTEM.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM #VIEWS)
-- then, we start copying tables over - now we tag the ones which are populated with at least 1 row.
-- the first cursor loop gets all table names, the second will find row counts of source tables.
-- this segment uses (ugh) cursor to loop through and gather all of the data. This cursor is at the table name level - not
-- processing anything, and is used only to find tables which have a rowcount > 0
-- believe it or not, the cursors run pretty darn quickly since they arnet doing any calculations - just finding
-- tables with row counts > 0
SET @QUOT = CHAR(39)
SET @LBRAKT = '['
SET @RBRAKT = ']'
SET @IUT = 'IsUserTable'
SET @ODBC_CMD ='(' + @QUOT + 'SQLOLEDB' + @QUOT + ',' + @QUOT + 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT +
').SQL_SIS_SYSTEM.dbo.'
PRINT 'BEGIN TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) )
DECLARE GETEM CURSOR FOR
SELECT TBL_SCHEMA, TBL_NAME FROM #TEMP
OPEN GETEM
FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE #TEMP SET RECCNT = ' +
'(SELECT COUNT(*) FROM OPENDATASOURCE(' +
@QUOT + 'SQLOLEDB'+ @QUOT + ',' + @QUOT +''Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT +
').SQL_SIS_SYSTEM.' + @TBL_SCHEMA + '.' + @TBL_NAME +')' +
' WHERE TBL_NAME = ' + @QUOT + @TBL_NAME + @QUOT
EXEC (@SQL)
FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME
END
CLOSE GETEM
DEALLOCATE GETEM
PRINT 'FINIS TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) )
DECLARE @PLIN VARCHAR (80),@LFT_PLIN VARCHAR (20),
@RT_PLIN VARCHAR (20), @TLIN INT
PRINT 'BEGIN TABLE CHECK AND LOAD CURSOR.'
DECLARE FETCHIT CURSOR FOR
SELECT TBL_NAME, RECCNT FROM #TEMP WHERE RECCNT > 0
OPEN FETCHIT
FETCH NEXT FROM FETCHIT INTO @TBL_NAME, @RECCNT
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'CHECKING ' + @LBRAKT+ @TBL_NAME + @RBRAKT
IF NOT EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id
(@LBRAKT+ @TBL_NAME + @RBRAKT )
AND OBJECTPROPERTY(id,''+@IUT) = 1)
BEGIN
SET @LFT_PLIN = 'OBJECT' + SPACE(5)
SET @RT_PLIN = SPACE(5) + 'NOT FOUND. BUILD ' +
CAST(@RECCNT AS VARCHAR (6) ) + ' rowS.'
SET @PLIN = @LFT_PLIN + @LBRAKT+ @TBL_NAME + @RBRAKT
SET @TLIN = LEN(@PLIN)
SET @PLIN = @PLIN + SPACE(50 - @TLIN) + @RT_PLIN
PRINT 'ENTRY. ' + CAST(GETDATE() AS VARCHAR (50) )
PRINT @PLIN
PRINT 'COPYING DATABASE TABLE.'
SET @SQL = ' SELECT * INTO ' + @TBL_NAME +
' FROM OPENDATASOURCE' + @ODBC_CMD + @TBL_NAME
EXEC (@SQL)
PRINT 'COMPLETED. ' + CAST(GETDATE() AS VARCHAR (50) )
END
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id
(@LBRAKT+ @TBL_NAME + @RBRAKT )
AND OBJECTPROPERTY(id,''+@IUT) = 1)
-- #TEMP (RECCNT) CONTAINS ROW COUNT OF SOURCE SYSTEM.
-- COMPARE TO ROW COUNT OF LOCAL TABLE
BEGIN
SELECT @DEST_RECCNT = RECCNT FROM #TEMP
WHERE TBL_NAME = @TBL_NAME
IF @DEST_RECCNT = @RECCNT
BEGIN
PRINT 'TABLE row COUNT MATCHES. BYPASSING REBUILD.'
END
IF @DEST_RECCNT <> @RECCNT
BEGIN
SET @SQL = 'DROP TABLE ' + @TBL_NAME
EXEC (@SQL)
SET @LFT_PLIN = 'OBJECT' + SPACE(5)
SET @RT_PLIN = SPACE(5) + 'REBUILD.' +
CAST(@RECCNT AS VARCHAR (6) ) + ' rowS.'
SET @PLIN = @LFT_PLIN + @LBRAKT+ @TBL_NAME + @RBRAKT
SET @TLIN = LEN(@PLIN)
SET @PLIN = @PLIN + SPACE(50 - @TLIN) + @RT_PLIN
PRINT 'ENTRY. ' + CAST(GETDATE() AS VARCHAR (50) )
PRINT @PLIN
PRINT 'COPYING DATABASE TABLE.'
SET @SQL = ' SELECT * INTO ' + @TBL_NAME +
' FROM OPENDATASOURCE' + @ODBC_CMD + @TBL_NAME
EXEC (@SQL)
PRINT 'COMPLETED. ' + CAST(GETDATE() AS VARCHAR (50) )
END
END
FETCH NEXT FROM FETCHIT INTO @TBL_NAME, @RECCNT
END
CLOSE FETCHIT
DEALLOCATE FETCHIT
PRINT 'FINIS TABLE CHECK AND LOAD CURSOR.'
XIT:
RAISERROR (50002,010,1) WITH LOG -- FLAG COMPLETION TIME IN SQL LOG
SET QUOTED_IDENTIFIER OFF
View 1 Replies
View Related
Dec 21, 2000
Is it possible to rename a SQL Login or User account?
View 1 Replies
View Related
Dec 29, 1999
I am copying a database to an alternate server by restoring a full backup onto the new server. However, whether I create the logins prior to the restoration or not the user accounts in the database no longer map to logins in the master.
Unfortunately it is not simply a case of dropping the acounts as they own objects in the database.
My best solution to date has been to use the sp_addalias to alias logins of the same name to the original user. However this is a far from ideal situation as one cannot readily tell that the logins are aliased and there must be an additional overhead in doing this.
View 3 Replies
View Related
Aug 27, 2007
Hi
Is it possible to import/export sql server user accounts?
View 1 Replies
View Related
May 2, 2007
We are moving from Oracle to SQL Server 2005 for our next release and I'm looking for content that describes creating Logins, creating User accounts and what approach to take if the database is using windows authentication vs. SQL authentication.
Any suggestions?
View 1 Replies
View Related
Oct 24, 2006
Hi,
I would like to know whether it is possible to start-up SQL Server for selective user accounts in Windows XP.
Say I have 2 user accounts - A and B.
I want A to use SQL Server 2005, but dont want B to use the engine.
Is it possible my any means?
Thanx in advance.
Best Wishes.
View 1 Replies
View Related
Feb 18, 2006
hii all;
My question is from 2 sections:
1- Is it secure to put user accounts (userName and password ...) on the database?
2- How can I set username and password for SQL server 2005 express file?
Thanks
View 5 Replies
View Related
Aug 14, 1998
i have a sql cluster setup, and need to change the user account that sqlserver starts with....any ideas? i screwed up and left it using localsystem account and now i can`t get sqlmail to work. i`m trying to avoid having to create the cluster again. any info appreciated.......jim jones
View 1 Replies
View Related
Jun 26, 2007
This has been extremely confusing for me.
I want to just make a simple backup.
first of all when i choose the pick a folder to backup, no mapped drives I make are even THERE.
I realize this is probably related to the account being used, okay I thought let me change the user account to a network admin account... I still cannot see the drive.
Can't this thing just accept whatever I tell it to access like any other program??
You would think they would at least keep the standard Open File dialog so we can use the network browser or something...
I've changed my accounts all to NETWORK SERVICE, then LOCAL SYSTEM, then a DOMAIN ADMIN...
I can't get this to work correctly on this freshly installed server... can someone please help?
I'm at the point where I don't care if i have to just re-install the damn thing...
Just someone please tell me what to pick for the accounts.
Bonus: I have this same issue with reporting services and Services for Unix NFS Mapped drives.
How can I map a drive with NETWORK SERVICE Credentials so it finds the datasource path?
I've only been able to do something like this with psexec and Local System.
When logged in as Domain Admin it will show a disconnected network drive that you cant get rid of but system account can use.
View 3 Replies
View Related
May 15, 2008
This may be an idiotic question:
I am attempting to use Visual Web Developer Express with a connection to a SQL Express db from a non-admin account on my XP Pro SP2 machine.
I can do everything in the app under an admin login, but can't seem to configure the db to allow the non-admin account access to the db. I've tried tweaking WMI, using Network Service, Local Service, and Local System with NT AUTHORITY, individual logins, and group permissions, but I'm stuck.
Any thoughts?
View 5 Replies
View Related
Oct 22, 2014
I have an SSIS package built by another developer, and now that I'm running it under my login the passwords won't save. The solution and packages are setup with ProtectionLevel EncryptSensitiveWithUserKey, but how do I get the User Key to reset so I can now save passwords? I can re-enter them, but whenever I enter hte password and test it then click OK it still has the red arrow next to the connection as if there's an error. I can create new connections and those passwords save fine, but with 40-50 items in this package I hate the thought of having to go into each and change the connection.
I tried changing the package and solution to DontSaveSensitive then rebuilding and closing then reopening, I hoped there was some option to reset the User Key just as if I created the solution. If this option doesn't exist why?
View 1 Replies
View Related
Apr 8, 2008
I am reading kb 934164. I am confused about (creating system administrator) domain user accounts....
IN SQL 2005 USER PROVISIONING Tools
under kb934164 8e type a window account by the following format
domain/user 8k Type a windows account by following format domain/user
DO I simply type domain/user or do I actually Type my domain/user account
What is domain user?
In other words where does domain (PASSWORD) come from?
where does user(PASSWORD) come from?
I have being trying to find the answer for this
Is there anything else I need to be prepared for in user
provisioning. By the way do you need to turn off uac in vista while installing
sql 2005....Thanks Is there any examples of this? I just want to get it right....
View 6 Replies
View Related
Aug 12, 2015
I cannot get a consistent answer as to how many domain accounts would be suggested in a SQL Server 2014 installation. Previously the recommendation was a separate account for each service to provide isolation and minimum permissions for each account. It seems from what I've read that a single domain account would have something added to make it unique from SQL Server's perspective. Several still advocate multiple accounts. I don't know if they are doing so because that's the way it's always been done or if there is still some compelling reason to do so. I don't want to create unnecessary accounts simply because something is "ideal."
View 8 Replies
View Related
Feb 18, 2008
set up asp .net user account on sql server 2005Question:
I've read the instructions in this article: http://www.netomatix.com/Development/aspnetuserpermissions.aspxBut do not know how to do this:You can grant 'Network Service' or 'ASPNET' user accounts permissions to connect to database.Please provide example on how to do this, thanks!
View 2 Replies
View Related
Jul 23, 2015
Do we still need the below service accounts in SQL 2008+ version even if we have proper SQL service accounts added in the logins?
[NT AUTHORITYSYSTEM]
[NT ServiceMSSQLSERVER]
[NT SERVICEReportServer]
[NT SERVICESQLSERVERAGENT]
[NT SERVICESQLWriter]
[NT SERVICEWinmgmt]
View 0 Replies
View Related
Apr 5, 2007
I have a root domain and child domain.
After using ADMT to migrate the domain user or group into the root domain, when I use enterprise manager to try and change the permissions allocated to that domain user/group, i get the 'Error 15401 NT user or Group not found'.
This is a correct error as the user is now in the root domain, however sql (in sysxlogins) still thinks its in the child domain.
Is there a simpler way, other than collecting the users permissions, deleting the user from SQL then adding back in with the correct domainusername format, then adding the permissions back?
I tried renaming the 'name' in sysxlogins (not recommended) and while that worked, whenever I tried to add the migrated user to another database, the login name was missing and would not resolve.
I believe it is something to do with the SID not matching.
Any ideas on how to fix this ?
View 1 Replies
View Related
Apr 2, 2001
I saw an article I believe on this site, now I can't find on HOW TO MOVE USERS AND ROLES FROM ONE SERVER TO ANOTHER. DOES ANYONE KNOWS WHAT IS THE BEST WAY TO DO that?
View 3 Replies
View Related
Apr 10, 2008
Hi!
I have a SQL 2000 server with a lot of databases (about 100) and I would like to move a great deal of them (+/- 50) to a new SQL 2005 server. All the databases I would move has the same word in the database name. Let's say the all include "Customer" in the Database name.
It would be a huge job to detach and attach every db manually. Is there a method that can detach db's based on a query againts the master db with a select on the name of the db?
If so, I also need to attach the db's on the new server with a script after moving the files to a new location.
Any good ideas?
View 5 Replies
View Related
Jan 17, 2006
My app works fine in development server. After moving to web server, error occured as bellow. Can someone help me? I use vs 2005 and SQL server is windows authentication mode.
Login failed for user 'domain_nameREPORT$'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'domain_nameREPORT$'.Source Error:
Line 348:
Line 349: If MyTable = Nothing Then
Line 350: MySQLDataAdapter.Fill(MyDataSet)
Line 351: Else
Line 352: MySQLDataAdapter.Fill(MyDataSet, MyTable)
View 1 Replies
View Related
Mar 9, 2000
How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database.
I do not wish to use the sp_addalias as it is available only for backward compatibility.
Is there a better way of doing this ?
View 4 Replies
View Related
Nov 10, 2015
I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
View 3 Replies
View Related
Dec 7, 2006
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
View 3 Replies
View Related
Jul 23, 2005
What does the "[dbo]." mean in the following sql script stmts?use [IBuyAdventure]GOif exists (select * from dbo.sysobjects whereid = object_id(N'[dbo].[Accounts]')and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[Accounts]GOand if you please, what does the "N" in N'IsUserTable' mean?thanks,-Steve
View 2 Replies
View Related
Aug 2, 2000
Can anyone tell me the purpose to using service accounts in SQL Server rather than just having the services start as a system account.
Thanks
John Shurer
john.shurer@gte.net
View 2 Replies
View Related
Mar 1, 2001
Hi,
How can i code a SQL statement that will return the top 20 accounts from a huge client table?
Thanks
View 1 Replies
View Related
Jan 26, 2012
I am setting up Replication and have a question about what's considered best practice for the accounts that will be running the replication agents. Microsoft says, "Run each replication agent under a different Windows account, and use Windows Authentication for all replication agent connections." What they don't say is whether these accounts are local accounts or domain accounts.
Which should I use/create, domain accounts or local accounts?
View 1 Replies
View Related
Jun 18, 2008
The following error keeps being reported in the Domain Controller Logs:
"There are multiple accounts with name MSSQLSvc/....."
View 1 Replies
View Related
Jan 26, 2007
Im pretty new to DBA world
We have a SQL2005 Standard setup with mirror and witness
I create a Database in the Principle, create a SQLLogon account and give it permission to the database. All works.
I then fail the databse over to SQL2 and the database is there, it has the SQLAccount I create at the database level, but a logon does not work. I notice there is not login account at the database level and If I attempt to create one, I am told there is one already. I try to assign permission to that account for the database and it again replys that there is already on.
Is this refered to as an orphaned logon?
I was a post on Moving logins from on server to another, is that what I must do?
THank you
View 7 Replies
View Related
