Hi
Just logged on to one of the SQL Servers.I see a lot of logins there who dont have any database access. Its a SQL Server 2000 box.
Can someone please help me with a script that would get me a list of such Orphan Logins (not orphan users).
Thanks
Regards
Like orphan users, is there a way to figure out orphan logins?
------------------------
I think, therefore I am - Rene Descartes
ok, 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
Hi Folks,
I have a 2000 remote server that polls the 6.5 server every 2 mins to check for access violation on the server via a query analyzer connection. It then uses xp_sendamil and net send for notifications.
The problem is, I've started to get loads of orphan connections from the remote server, which will not kill when attempted.
Any ideas, apart from stopping and starting sql service?
I have run a NOT EXISTS query comparing a table in test and production, to determine which records have been added to the table in production so I can import those records to test. The count(*) from both tables has a difference of 766 records but the query is showing 768. I am trying to find the easiest/quickest way to identify the orphan records.
View 2 Replies View RelatedHi,Say, a database has all the FK constraints created properly, would sqlserver 2000 save this kind of meta data into sysforeignkeys table? Orit depends on ...?I'm trying to find all the orphen records in a given database via metadata.TIA.
View 7 Replies View RelatedNot sure if this is the correct place to ask. But it made sense to start here.
I have a legacy stored procedure that had been running under SQL2005 for some time correctly. Now it fails as it is trying to deal with user tables listed in the sys.objects view. The issue appears to be an individual table that no longer exists in the database but is still has a record visible from sys.objects. This entry is an orphan. My question is how do I remove the orphan record from sys.objects?
Thank you
Hello,
I have a VB program which uses ADODB.Connection to make connection to SQL Server DB.
However, under some circumstances, orphan / ghost connections remain in DB even after the VB program closed.
Here are the facts...
(1) Windows Server 2000 + SQL Server 2000
- run the VB program and made connection to the DB, leave it for 3 minutes, the connection closes when the program closes.
- run the VB program and made connection to the DB, leave it for 3 hours, the connection closes when the program closes.
(2) Windows Server 2003 + SQL Server 2000
- run the VB program and made connection to the DB, leave it for 3 minutes, the connection closes when the program closes.
- run the VB program and made connection to the DB, leave it for 3 hours, the connection does not close even after the program is closed.
Does anyone know if there is any problem with Windows Server 2003 + SQL Server 2000 using ADODB.Connection?
Or is there some kind of "timeout" parameter for which I can set to resolve such issue?
Any help is appreciated!!!
Thanks & Regards,
Andy.
I am using a table to display a very large amount of data (a basic list of information) with groups (the group header repeats on each page). Each of the groups can span multiple pages, The problem I have is that in the printed mode (or PDF) the group title may show at the bottom of some pages with no details (there may not be enough space for a detail row) and then the heading repeats on the next with the detail. How do I prevent this widow problem?
This can be controled in Access and Brio but not here seems a little odd.
Hello,
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem?
Please, help
Thanks
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service.
When we install the app, we run a process that builds 2 dbs to include:
Tables, indexes, stored procedures, views and user accounts.
SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
We have a client that wants to remove our standard logins so that they can run on only a Windows login.
I know I should be able to tie the db users to a Windows login.
And I can do the same for the service.
But I am at a loss as to how to get this done.
How do you associate db users with a Windows login?
When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Hope this all makes sense.
Here's the DB Connection code for a site (Web.config file)
I was wondering how I could specify an idle timeout or orphan timeout.
I tried idle timeout=x and kept getting errors
<add key="DBConn"
value="server=localhost;Trusted_Connection=true;database=SaltwaterFishing;Min Pool
Size=5;Max Pool Size=150;"></add>
Thank you
How do you make Asp.net use something other than the local ASPNET user for data access in sql server
View 3 Replies View RelatedI need to copy 80 logins within the same SQL Server (7.0 SP1) from 80 "old" logins that I'll delete later.
I clearly need to maintain all the security options for the new logins.
Is there a way to do this, adding the logins with the new name and granting all security options? Is there a script that will do this task or can somebody help me in doing that?
Thank you.
Hi,
I have two servers (server1, server2). I was trying to access the data from server1 to server2. I linked the servers and set up the option for data access.
when I run the following command from server1: sp_remoteoption 'server2', 'sa', 'sa', TRUSTED, TRUE
It gives me the following error. How to handle this.
Server: Msg 15185, Level 16, State 1, Line 0
There is no remote user 'sa' mapped to local user 'sa' from the remote server 'server2'.
I appreciate your help.
Thank you.
Eric s.
I have a server that was recently upgraded from SQL 6.5 to 7.0 that contains almost 700 logins using standard security. Is there a limit to the number of logins that SQL will host? The logins/users have all been added using a GUI within an application and does not support NT authentication. Does this cause any known problems in SQL 7? When I right click on the database and go to properties / permissions MMC gets hung up. Also one of the systems analysts is convinced that SQL is dropping permissions (I disagree to this). Any one have any thoughts or experience with large numbers of logins / users?
Linda
Does anyone know of any 3rd party software applications that will make users change their SQL
logins every 3 months?
Thanks,
Heather
Hi!
How can I copy all logins from one SQL server 6.5 to
another one.
Thank you.
Anny.
How can I determine which users are using the database??
Thanks
I am new in sql and have problems about logins.
with sql 2005
1)I create a windows login in my home pc (win xp home sp2) and i can connect server with ssms this new user
but in my office (win xp pro sp2) create a windows login (it is a windows user also) and when try to connect with ssms take error 18456 (user name and pass is true!!!)
2)in my office pc i create a sql login which has no role. but this user can create users and dbs. but in my home pc, user has no role cant do thats...:eek:
Hi everyone,I am new to this forum.I hope some body will help me in this,I am trying this for the past 6hrs.
on my server there are some hundreds of sql logins are there,I need to identify the logins which doesn't associated with either any databases or dbroles,server roles.Later i need to delete those logins.Can any body help me in this.
Thanks in advance.
y r u doing like dis guys?
View 4 Replies View RelatedHi All,
I want to generate script for users and their associated permissions on the xyz database and at the same time I want to generate script for associated logins and their roles over the xyz database.
I am using SQL 2005 environment.
Needs urgent help.
Regards,
Austin
2 issues
i login using windows authentication. looks like i cant create a new sql server authentication login. what can i do?
and when i try to login with my sa login it pops the errror 18452. does this mean i didnt install mixed mode? i cant remeber.
After installing 2005 I have these 5 logins. Do I need them or should i wipe them out? What are they?
YPGA-SQL2005-DEVSQLServer2005MSFTEUser$IPG-SQL2005-DEV$MSSQLSERVER
YPGA-SQL2005-DEVSQLServer2005MSSQLUser$IPG-SQL2005-DEV$MSSQLSERVER
YPGA-SQL2005-DEVSQLServer2005SQLAgentUser$IPG-SQL2005-DEV$MSSQLSERVER
BUILTINAdministrators
NT AUTHORITYSYSTEM
=============================
http://www.sqlserverstudy.com
We created web site which has NT Authentication....
Using service account
SERVICE ACCOUNT
WEBGROUP
This has WEBGROUP_USERS
WEBGROUP_USERS
Tracey
John
When myself logs in...
Connect To SQL i see
in sysprocesses WEBGROUP
Is there a way to pass TRACEY to the process rather than having
SQL Login set up..
Reason is if someone adds themselves to this group in AD they could have access to our applications im trying to figure out
how to not make AD drive the access to SQL.
Thanks
Hello,I'm currently developing an access application which uses sql server as abackend.What i'm uncertain about, is how i should setup security without creatingtoo many sql server logins. I'm convinced that i should limit access to sqlserver, otherwise people can get in with other means than my application(e.g. odbc, sem, etc.).I've setup sql server in mixed mode and created a login for my applicationto connect to a database.So, what is the best way to implement tight security, but still be able tolog the current user and hostname? If a separate table is needed to createdusers and passwords, please tell me how to use it, i've never done thatapproach.Thnx------------------------------------------------------This mailbox protected from junk email by MailFrontier Desktopfrom MailFrontier, Inc. http://info.mailfrontier.com
View 1 Replies View RelatedOn a huge Sql-Server 7 installation, we have various client applicationsdistributed along the Lan, accessing one main database.Each application accesses the db using one out of around ten logins. Most ofthem, have only DBDataReader right on the db, as they are consultationconsolles only.In order to monitor db usage, the customer requires some kind of log of useraccess.My need, mainly, is to INSERT a record into a log table, recording Date,Time, Login, Host of each access.But, and this is the problem, the job has to be done by the server itself,not by each client, because of various reasons:1) we don't like to increase rights of logins2) we don't plan to change anything in our custom client application3) few of those client applications have been developed by foreignsuppliers, so we cannot change them.My question is: does it exist any kind of authentication LOG, which I canwork on?Or, is it possible to activate a kind of TRIGGER, reacting on loginauthentication?Thanks in advanceAlberto
View 2 Replies View RelatedHow can I get a list of all SQL server logins that are on a given sqlserver? What sql query would get me that? Or is there an sp thatgives me that. Also, if it could list what db they have rights tothat would be great also.
View 2 Replies View RelatedI am confused about windows account, win dimain account, sql loginaccoun and sql database user account.... can you give me a freshexample to show the exact relationship between them and how they worktogether?
View 1 Replies View Related
hi ,
we have nearly 1 thousand logins are there. out of thousand only 8 persons are directly interacting with sql server .
remaining are accessing the databases through application only. my query is every one is having sysadmin permissions .
if i remove the permissions to them, they can't able access the databases.i am new to the organization.perviously no one was there to maintain the server .i don't know how to resolve this problem.my manager asked me to do something for this one.
can anyone help me to reslove this prb
regards,
manoj
How can I write a script to generate the list of SQL users that have sysadmin privileges. I would also like to do the same for the windows accounts as well.
Please let me know.
Thanks.
I'm monitoring 2005 while running our application by looking at
sys.dm_os_performance_counters, general statistics, logins/sec.
I began testing a .NET 2 application, and logins/sec went from 63 to 11,433 in 30 minutes. Even though I have closed the application, it is still showing 11,449 - the only thing that's happened in the last hour is that I've run some queries from QA. is this login number cumulative, or is it an actual reflection of logins/sec? How on earth could that many logins be happening when no one else can even get to that server?