There is still a problem with mirrored (mapped) SQL Users. If you mirror a database where an application connects with an sql user, the mapping (login / user) will be lost on the mirror server. After a failover occurs, it it not possible to log onto the new principal database because the database use will be an orphaned user and has to be remapped to the login (using sp_change_users_login 'update_one', 'user', 'user').
Is there any chance to do it in a system trigger? What is the firing event after the failover occurs? I've tried something like following, but it doesn't fire.
/************************************************************************************* 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've restored my database from backup on another SQL 2005 server and can't login, could you tell me what is the best way to repair this on SQL 2005, please
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
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?
i have an app that starts a transaction (some deletes, etc...) in the middle of this transaction the connection drops (say we pull out our network cable) but this will leave the transaction still running and it will be orphaned. i know i can kill them in sql server, but what i'm looking for this to somehow not happen at all. I tried SET XACT_ABORT ON but no luck.
Is what i need even possible?
_______________________________________________ Causing trouble since 1980 blog: http://weblogs.sqlteam.com/mladenp SSMS Add-in that does a few things: www.ssmstoolspack.com
We have 4 sql servers which service 4 load balanced web servers (with sticky sessions) and we recently brought one of the four SQL servers online.
In the last week the new SQL Server 2000 Standard (SP4) has been exhibing orphaned connections and attempts to use invalid connections out of the connection pool on the app servers. This manifests itself in General network Errors and Timeouts (when the sql is not really doing much other than simple table requests).
Does anyone have any experience in what might be causing this on a new server. I've asked our networking people to verify that the network routing and firewall are set the same as the other three servers. The application is the same acrossed all of the app servers so I've ruled out any issues with the application not closing connections.
I€™ve recently set-up database mirroring between two servers in the same domain: DMZSQL01 and DMZSQL02 with a witness of DMZSQL03. The mirroring as all gone according to plan.
Set up all the users/databases on the Mirror (DMZSQL02) and then do a back-up/restore to sync the databases and then enable the mirroring, this has all gone fine and we have lots of synchronised/mirrored databases now. However, if I do a failover the logins on the mirror are not valid, they are in SQL Server and also the database but they don€™t work. I€™ve read through other posts and found links to this SQL Server article: http://support.microsoft.com/kb/918992/
This has had no effect though. Do I need to remove everything off my mirror and start again setting up the users first and then doing a backup/restore or is there some other way. Both servers are reporting the SID€™s are the same for the logins which do not work. However the principle_ID is different, I'm quite sure if this is a problem or not??
Can anyone point me in the right direction of what to do next? Or have any ideas.
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.
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.
I seem to have an orphaned Distribution Agent. There is no associated Publication and the agent is sending errors. The errors would be legitimate if only I had an associated publication. I also do not have an associated Snapshot Agent.
How can I get rid of this bogus agent? I already deleted the associated job and rebooted SQL and SQLAgent but it still persists.
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?
All is happened when a server crashed some weeks ago and it was removed from my network. After that, under my SQL Server 2012 I get an orphaned account which cannot be removed. This account is a computer account related to an old SCOM installation.
If I try to execute the command DROP USER [NETWORKSERVERNAME$] I get the following error message:
The database principal has granted or denied permissions to objects in the database and cannot be dropped. Msg 15284, Level 16, State 1, Line 1
The database principal has granted or denied permissions to objects in the database and cannot be dropped.But if I run the following command to know all permission granted to the account, I get an empty result:
select * from sys.database_permissions where grantee_principal_id = user_id ('NetworkSERVERNAME$');
Furthermore, following the instructions provided by the official KB for troubleshooting orphaned users, I get another error [URL].
sp_change_users_login 'update_one', 'NetworkSERVERNAME$', 'NetworkSERVERNAME$' Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114 Terminating this procedure. The User name 'NetworkSERVERNAME$' is absent or invalid.
The only thing I can retrieve is 15 permissions that this account granted to another account in the past:
select * from sys.database_permissions where grantor_principal_id = user_id ('NetworkSERVERNAME$' -- class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc 17 SERVICE 65538 0 5 19 SN SEND G GRANT And more 14 rows…
resolve my issue and safe delete the account SERVERNAME$? I need this because I have to reinstall SCOM with the same computer name on another server, but installation fails due to this behavior.
I really don't know how frowned upon my approach is here, but it was the only way I have been able I've been able to do it.
On my application, when users delete their account, it sometimes brings the db server to a COMPLETE crawl. The reason is some users who delete have many years of related data, and when the data deletes with them, its very slow.
To avoid this I've taken off many contstraints, and I do have some sprocs that deleted orphaned data at night.
Wanting to know how do you detect and kill orphaned spid in SQL Server left behind by an XML service which was not completed normally. The client requests an XML service which open a connection, and due to network problem the client did not manage to call the another service which suppose to end the connection. Thus leaving behind in SQL Server orphaned spid which continue to hold locks. This created problem when the next user wants to access the resource.
The simplest way is to get all spid which are holding locks for more than a certan period and kill them. Is there a better way to actually detect orphaned spid?
We are experiencing a situation where the SRS (2000 SP2) report server will no longer render reports. In the log file, there are many instances of
w3wp!runningjobs!434!3/23/2007-10:12:57:: i INFO: Adding: 8 running jobs to the databasew3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request
What could be causing this? The reports are making queries through an OLE DB provider. There are no scheduled jobs, and the load doesn't seem that heavy.
I am attempting to drop a database (sales), however I receive this message: Error 3274 is "Cannot drop the database 'sales' because it is published for replication." Yet, I no longer have any publications in this database. It seems that there is some orphaned information related to a publication that existed. Any help would be great.
I have found some articles with no publication in our transactional replication.
For example, running this:
select p.publication, a.publication_id, a.article from dbo.MSArticles as a left outer join dbo.MSpublications as p on a.publication_id = p.publication_id
shows this:
NULL1org_Community NULL3org_Community Purchasing to EDW5org_Community NULL1org_Division NULL3org_Division Purchasing to EDW5org_Division
How can I get rid of the articles that are not part of a publication?
I can't use sp_droparticle because it requires a publication which these articles do not have.
insert into Hier select 'subramanium','Manickam' union all select 'subramanium','Munuswamy' union all select 'Munuswamy','senthil' union all select 'Munuswamy','sasi' union all select 'Munuswamy','uma' union all select 'manickam','vijay' union all select 'manickam','bhavani' union all select 'manickam','dhanam' union all select 'uma','varsha'
Delete from Hier where child='uma'
I tried:
select parent from Hier where parent not in(select Child from Hier) and parent <> 'subramanium' Getting resultset as: parent ====== uma
I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?
I guess this is a fairly common topic but couldn't find the right words to find anything in a search.
What I'm getting at, is there any tsql functions or combination of commands for the following.
You have identity columns in your tables, if you set the a seed and autoincrement, I enter in rows 1 -10 and then I delete 4, 6, 7, 8.
My next new record uses 11. Is there any logic that allows you to check and reuse 4, 6, 7 & 8 described above? Not looking for something that consists of having to create an extra ID table for each table and handle configuring what the next available number is everytime an Insert or delete is called.
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
I need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name) at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries". now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country} who has an idea how to solve this problem?
