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.
Been asked to restore an orphaned MDF file leftover after a botched uninstall - no .bak file. Tried to reattach, but got an error, I don't think it had been detached. My initial answer was, "No, very likely can't be done".
Am I right? Or is there a way of attaching it that doesn't require it to have been detached?
Looking through the archives, I didn't see any articles that specifically addressed the problem, so here it is:
SQL 7.0, NT4SP6, 2G ram, 4x
I've got a user process as follows: Status: ROLLBACK Command: SELECT Application: Enterprise Manager Wait Type: EXCHANGE Login time: 06/25/00 4:07:05AM Last batch: 06/25/00 4:07:20 AM The last TSQL command batch is a correlated subquery with grouping...
Apparently it hung and the user quit ungracefully.
No other processes are blocking it, but periodically it blocks other processes, including some index maintenance I need to perform.
I have tried to kill it with Enterprise Manager and with the KILL [id] command. Neither have worked.
Mgmt is reluctant to bounce the database, as am I.
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.
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.
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
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.
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.
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....
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."
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!
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.
I have a nightly job that restores a production database to a reporting server. I have some report- related stored procedures that need to run on the reporting server once the database is restored. However, during the restore, the permissions for the reporting stored procs get deleted and replaced with the production permissions, which are useless in this case.
Is there a script or system stored procedure I can schedule to run after the nightly restore that will also restore user permissions? Thanks, Frank.
i am taking the full database backup on server sql1 and restoring on sql2. there are some logins in sql1 which are not in sql2. i restored the database ,and try to create the login with same name as in sql1 and try to create the user in the database ,its giving me the error the user is already in the database . when i checked the users the user is not in the database ,but he is in the public role .i try to remove from the role its not doing it..
I'm kinda stuck with the following issue and would apreciate some help with it.
Basically i need to have a user within SQL that has rights to restore databases and is the owner of that database so it can alter the data. The problem i face is that at this moment i see no other way then making that user either member of the server role "sysadmin" or "serveradmin".
Anyway, these roles have to much rights. I tried using the dbcreator role which will make a the needed user db_owner but only when a new database is created, not when a database is restored.
Tried to resolve this with a DDL trigger and then temporarily run as a user with serveradmin rights, however there is no system event for restore database, so that doesn't work either.
Hi, Folks. I hate to have to come back and ask about restores right after I had to ask about Back ups, but I'm in a bind and can't seem to find other resources that address this issue.
I'm using VB Express 2005 and SQL Express 2005 to develop a windows form application that uses a User Instance of a database. With help from this forum, I was able to get the backup working. Now, I can't get the restore to work. Here's the code I'm using:
Dim SqlConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.NCSConnectionString)
SqlConnection.Open()
Dim ServerConnection As ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection(SqlConnection)
Dim srv As Server = New Server(ServerConnection)
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem(My.Settings.DefaultBackUpPath & "NCS_Full_Backup1.bak", DeviceType.File)
''Define a Restore object variable.
Dim rs = New Restore
'Set the NoRecovery property to true, so the transactions are not recovered.
rs.NoRecovery = True
rs.ReplaceDatabase = True
'Add the device that contains the full database backup to the Restore object.
rs.Devices.Add(bdi)
rs.NoRecovery = False
'Specify the database name.
rs.Database = SqlConnection.Database.ToString()
'Restore the full database backup with no recovery.
rs.SqlRestore(srv)
'Inform the user that the Full Database Restore is complete.
MsgBox("Full Database Restore complete.")
The error is:
- InnerException {"RESTORE cannot process database 'C:DOCUMENTS AND SETTINGSMARCOSMY DOCUMENTSVISUAL STUDIO 2005PROJECTSWORKING COPY OF NCSNCSBINDEBUGNCS.MDF' because it is in use by this session. It is recommended that the master database be used when performing this operation. RESTORE DATABASE is terminating abnormally."} System.Exception
I think that I need to either free up something that's locking the database or I need to switch to the master table in the User Instance. I tried a number of variations in attempt to try both of these avenues without sucess.
I'd really appreciate any guidance that I might be able to get.
Hi all. I have read and implemented the very helpful threads on backing up and restoring with user instance posted with MFriedlander. Thank you. However, during the rs.SqlRestore(srv) command I am getting the following error "Restore failed for Server '\.pipe4A1F91FF-F6FE-45 sqlquery'. " "Exclusive access could not be obtained because the database is in use."
I have implemented the changedatabase method as described in that thread.
SqlConnection.ChangeDatabase("master")
right before the line
rs.SqlRestore(srv)
I do not use the default instance of sql express, but I do use an instance called 'test' for my app. Should my changedatabase method also refer to my sql instance?
I am running from VS 2005 debug (F5) when it fails and cannot think of anything that would be locking it. Any help would be appreciated. Thank you. Below is the full code of the restore.
Robert
Dim sqlconnection As SqlConnection = New SqlConnection(My.Settings.dbTestConnectionString)
sqlconnection.Open()
MsgBox(sqlconnection.Database.ToString())
Dim ServerConnection As ServerConnection = New ServerConnection(sqlconnection)
Dim srv As Server = New Server(ServerConnection)
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem(txtRestoreFile.Text, DeviceType.File)
''Define a Restore object variable.
Dim rs As New Restore
'Set the NoRecovery property to true, so the transactions are not recovered.
rs.NoRecovery = True
rs.ReplaceDatabase = True
'Add the device that contains the full database backup to the Restore object.
rs.Devices.Add(bdi)
'don't know why the below norecovery is changed to false
rs.NoRecovery = False
'Specify the database name.
rs.Database = sqlconnection.Database.ToString()
sqlconnection.ChangeDatabase("master")
'Restore the full database backup with no recovery.
I don't know if this is the right forum to post this question, but here it goes.
We have restored into sql 2005 the database backups made in sql 2000. We connect with trusted connection and application roles, and when trying to execute a transaction to another database (with the guest user), we get a permission error.
Does anyone know if, apart from restoring the databases, we should do something else to get the guest user working the same way as with sql 2000 in the restored databases?
On SQL 6.5 we have had a couple of instances recently where an orphan connection cannot be killed and the only way to get rid of it is to stop and start the service. Does anyone know of any other way to get rid of that orphan connection?
As I do analysis on a database I am finding multiple clustered indexes defined on a single table. I know this is not possible since SQL Server only allows one clustered index per table. With more research I found these suspected clustered indexes have a corresponding row in sysindexes but without a correlating id match in either sysobjects or syscolumns. Is this orphaning of indexes common and what is a recommended solution. Thanks, Randy Garland
/************************************************************************************* This procedure should be created in the Master database. This procedure takes no parameters. It will remap orphaned users in the current database to EXISTING logins of the same name. This is usefull in the case a new database is created by restoring a backup to a new database, or by attaching the datafiles to a new server. *************************************************************************************/
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL BEGIN DROP PROCEDURE dbo.sp_fixusers IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>' ELSE PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>' END
GO
CREATE PROCEDURE dbo.sp_fixusers
AS
BEGIN
DECLARE @username varchar(25)
DECLARE fixusers CURSOR FOR
SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers INTO @username
WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM fixusers INTO @username END
CLOSE fixusers DEALLOCATE fixusers END go IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>' ELSE PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>' go
I am not sure if this is actually a sql connection I didn't dispose of.The database the app use is franchise_search, login testHere are the results of sp_who before I start the web server and run the page1 0 background sa 0 NULL RESOURCE MONITOR 02 0 background sa 0 NULL LAZY WRITER 03 0 suspended sa 0 NULL LOG WRITER 04 0 background sa 0 NULL LOCK MONITOR 05 0 background sa 0 master SIGNAL HANDLER 06 0 sleeping sa 0 master TASK MANAGER 07 0 background sa 0 master TRACE QUEUE TASK 08 0 sleeping sa 0 NULL UNKNOWN TOKEN 09 0 background sa 0 master BRKR TASK 010 0 background sa 0 master TASK MANAGER 011 0 suspended sa 0 master CHECKPOINT 012 0 background sa 0 master BRKR EVENT HNDLR 013 0 background sa 0 master BRKR TASK 014 0 sleeping sa 0 master TASK MANAGER 015 0 sleeping sa 0 master TASK MANAGER 016 0 sleeping sa 0 master TASK MANAGER 017 0 sleeping sa 0 master TASK MANAGER 018 0 sleeping sa 0 master TASK MANAGER 020 0 sleeping sa 0 master TASK MANAGER 022 0 sleeping sa 0 master TASK MANAGER 024 0 sleeping sa 0 master TASK MANAGER 051 0 sleeping NT AUTHORITYSYSTEM HPDEV 0 msdb AWAITING COMMAND 052 0 sleeping sa HPDEV 0 ReportServer AWAITING COMMAND 053 0 sleeping sa HPDEV 0 master AWAITING COMMAND 054 0 sleeping sa HPDEV 0 ReportServer AWAITING COMMAND 055 0 runnable sa HPDEV 0 master SELECT 0 then I run the page and all I do is using (m_sqlConn = new SqlConnection(m_strSQLConnect)) { m_sqlConn.Open(); //Log_History(); //FillCompany_DropDown(); //m_sqlConn.Open(); //BuildCategoies(); }I open the connection never use it and return the using should close the connection.But after I close the page sp_who add the line56 0 sleeping test HPDEV 0 Franchise_Search AWAITING COMMAND 0Is this connection pooling or am I missing something?No other app or anything can use the test connection I just made it and changed the password. Jon
Had to rename a SQL 2000 box and now the scheduled maint jobs that were created under the previous name of the box cannot be deleted via EM. Can anyone offer help?
