Fix Orphaned Users

Feb 4, 2002

/*************************************************************************************
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

View 20 Replies


ADVERTISEMENT

Mirroring And Orphaned Users

May 29, 2006

Hi there!

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.

alter TRIGGER map_orphaned_users

ON ALL SERVER

FOR ALTER_DATABASE

AS

execute sp_change_users_login 'update_one', 'easyris_41', 'easyris_41';

Someone an idea how to automate these usermapping after failover on the (new) principal server?

View 10 Replies View Related

Orphaned Users Afret Restor - SQL 2005

Aug 19, 2007

Hi,

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

--
Regards,
anxcomp

View 2 Replies View Related

Orphaned Connections

Jan 15, 2002

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?

View 1 Replies View Related

Orphaned Indexes?

Jan 14, 2000

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

View 2 Replies View Related

Orphaned .mdf File

Nov 3, 2004

I have a .mdf file that I would like to restore. However the log file has been destroyed. Is there any way to restore just an .mdf file?

View 1 Replies View Related

Orphaned SQL Server Connections

Jan 8, 2008

 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  

View 1 Replies View Related

Finding Orphaned Records

Mar 10, 2004

I am trying to find records that are orphaned in a data base after thier parent record (in another table) has been deleted.

Something happened to the constraints and I had no idea until now this had happened.

I have the faint rustle of a query in query sort of thing but my knowledge needs expansion.

PS: sorry for the cross post - just noticed that I was in the wrong forum before.

Thanks,
Matt

View 11 Replies View Related

Orphaned Scheduled Jobs

Mar 17, 2004

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?

View 1 Replies View Related

Orphaned Transactions Problems

Mar 10, 2008

so i'm having a bit of a bad day today

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

View 20 Replies View Related

SQL 2000 Orphaned Connections

May 24, 2007

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.

View 1 Replies View Related

Orphaned SQL Logins On The Mirror

Nov 20, 2006

Hi there,

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.

Thanks
Ed

View 7 Replies View Related

Orphaned/Hung User Process

Jul 21, 2000

Hi,

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.

Any ideas?

Thanks in advance!
--Nassif

View 2 Replies View Related

Orphaned User Accounts When Restoring

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

Replication - Orphaned Distribution Agent

Dec 9, 2002

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.

Any help would be appreciated.

Colleen

View 1 Replies View Related

SQL 2012 :: Restoring Orphaned MDF File

Sep 23, 2014

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?

View 4 Replies View Related

SQL 2012 :: Cannot Drop Orphaned Account

Dec 30, 2014

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.

View 8 Replies View Related

Deleting Orphaned Data - Maintenance

Dec 10, 2007

Hi,

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.

thoughts on this approach ?

View 5 Replies View Related

XML Services And SQL Server Orphaned Processes

May 1, 2007

Hi

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?



Help much appreciated.



regards

benc

View 3 Replies View Related

Large Numbers Of Orphaned/expired Requests

Mar 30, 2007

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.

View 1 Replies View Related

Orphaned Publication Information Preventing Drop Of Database

Jul 7, 1999

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.

View 2 Replies View Related

SQL 2012 :: Transactional Replication - How To Remove Orphaned Articles

Jan 27, 2015

We are runnning on SQL Server 2012 SP1 + CU9.

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.

View 1 Replies View Related

How To Find Orphaned Value From Parent / Child Hierarchy Table

Aug 15, 2013

how to find the orphaned value from the below parent/child hierarchy Table.

create table dbo.Hier(parent varchar(100), child varchar(100))

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?

View 2 Replies View Related

Is There A Hack That Would Allow You To Reuse Identity Numbers That Were Orphaned By Deleted Records?

Aug 24, 2006

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.

Thanks.

View 4 Replies View Related

Reporting Services :: Adding (new) Child Domain Users To SSRS As System Users?

Jul 28, 2015

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?

View 5 Replies View Related

Why Out Of The Blue Would VPN Users Be Unable To Connect To Database And Local Users Are Unaffected?

Mar 6, 2008

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!!

View 6 Replies View Related

SQL Security :: Did Not Create Any New Users And There Are No Other Users Listed In Accounts Section

Sep 28, 2015

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.

View 2 Replies View Related

Stored Procedure To Get All Users Or All Users Of A Specific Country

Apr 30, 2008

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?

View 9 Replies View Related

Difference Between Database -- &&> Users And Security --&&> Users

Nov 28, 2006

Hi Team,

In SQL Enterprise Manager, when we expand "Database -->Users", we see the

users there. When we expand "Security --> logins" we see the same users there.

Can you differentiate these two.

Thanks

Santhosh

View 1 Replies View Related

List Users Then Disconnect Users

Jun 4, 2008

I want to be able to list all users connected/logged in a specific database and disconnect them all or a certain user.

can this be done in SQL and if so how?

View 1 Replies View Related

Users Get Blocked By Other Users Alot

Sep 26, 2007

We are using Navision with SQL server 2003.

What kind of methods is there out there to reduce hwo often this happens?

View 10 Replies View Related

Db Users Mix Up

Feb 28, 2006

Hello:
I am having a problem understanding the db users.
I am working on a DNN site, and having troubles with db users permissions.

I want to know what is the DBO?
I have a user in my database, that is assigned "public".

In my application usually, what should i do:
1- Login with the public user, create tables, views, sp, add data ...
2- Use the dbo always?

In my application, I will be using the public user in the connections string.

Can you help please.

View 3 Replies View Related

SQL 7 Users

Nov 30, 2001

I have moved several SQL 7 databases from one server to another. The databases
are attached, and I can see all the tables,etc. However, I can only see 1 user, that being dbo, therefore users cannot log into the db. HELP

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved