Does SQL Sever 2000 keep track of all the logins/userids that were
deleted over the last year? For example, If I deleted a login "Joe"
from a SQL 2000 server, (and of course the corresponding userid "Joe"
got deleted from the database) would that be recorded somewhere in the
system or the production database? i.e. the information such as the
login "joe" deleted on such and such date from such and such database?
Long Live SOX :)
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
We are working on an application but with other users who have been using the SA password. The application is running on MSSQL 6.5 and sp4. We have been generally assigning logins/userids to new users although some people are using the SA id. The SA id password has been changed so that will stop users (really developers) from using it.
Most users are assigned to a group which has select, insert, update, delete permission. Some users will be moved to a group which as only select privilege
I would like to query the system tables and see exactly what permissions are assigned to each user.
I can look at Enterprise manager and cut and paste this information I think.This is cumbersome
1) Does anyone have a query I can run to get the userid/login and the permissions for each userid?
2) How can I make a user a dbo and give him/her the same permissions as a dbo on tablesalready creaetd?
Has anybody been successful migrating userids from one server to another server using SQL7.0? If so, is anyone willing to please provide any sample scripts.
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.
Folks, I'm brand new to MSSQL, I come from a DB2 background.
I've installed SQL Server Express Edition on my WinXP (SP2) system. I'm able to connect to it from a command prompt using "sqlcmd -S CS288290-ASQLEXPRESS -e".
Now I want to connect using a specific UserId and Password. So I changed my "LoginMode" registry variable to "0x00000002" and restarted the service. But now when I issue "sqlcmd -S CS288290-ASQLEXPRESS -U g2user -P g2pwd", I get error:
Msg 18456, Level 14, State 1, Server CS288290-ASQLEXPRESS, Line 1 Login failed for user 'g2user'.
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.)
The 'view job history' on Enterprise Manager is showing me only 4 or 5 run histories. I want to see more history on each of the jobs.Even I changed the limit to 10000 lones from 1000 lines on the server properties in the job system option, I am still seeing only the last 4 or 5 histories for each job(there are a total of 70 jobs on the server).Any ideas??Thanks. Reddy.
I have set up a job as sa.After that I changed the sa password.Now I am not able to view the job history since then.Do i need to delete the job and reschedule it?
I have a question about the sql job history. I have a sql 2000 server that was recording all the job history on my sql jobs just fine until the other day. Today I checked the history and the jobs didnt show the past history anymore. Just today's history. So if the job didnt run today there is no history for that particular job. This isn't true, the job was run a few days ago. I know that no one went in and cleard the history on the jobs. What causes this issue, is there a way to not make this happen in the future? Is there a way I can recover the past history for my jobs.
i have my job running every 5 min. I want to view the history log. In job history it shows only few hundreds of records.. But i want to see from beginning. where to view whole log of job history?
hello, I'm am looking for a smart solution for keeping history of changes in records in my sql database. Not only history of a record but also which user caused the change and when (I have the username in session). for now I added 4 fields to each table: "created by","created on","cancelled by",cancelled on", when the user create a record the 2 first fields are filled and and a user delete a record the two last fields are filled and the record is not really deleted but it won't be shown again (I'm using "where createdby is null"). this solution is taking the performance down and it does not solve the editing record tracking. creating new records and cancelling old ones for editing/changing solve that but than I m having problems with the primary key and relations to other tables. I tried also to use a table that holds records that descirbes every change in records in my tables. it's very hard to search old data that way. I know that oracle databases has a smart solution for history... please someone advise me.. Elad.
hi , i am working on an application using c#, visual studio 2005, sql server 2005. i have a few tables in sql server 2005. i need to save the history. (i.e) all the inserts, updates, and deleats performed on the tables. can any one suggest me how can i achieve that. should i use triggers and save the changes in another table ??? waiting for your suggestion?? thank you
Hi, I want to know where the history of stored procedures stored, I mean yesterday I created one procedure and today I dropped that procedure and recreate it, I checked in sysobjects table name,crdate it shows only the creation date of the procedure. Where can I find the Modification date or something related to procedure history.
I'm using merge replication between 10 SQL server 7.0 SP2 machines. One central server is the publisher and 9 subscribers. I’ve setup an alert to get a message in case of conflicts. I defined it to trigger everytime the performance counter conflicts/sec rises above 0.
After some experimenting this seems now to work reliable, but there is still one point which bothers me. All conflicts are kept in the conflict history and everytime I get a message through the alert the number of copnflicts stated in the message increases by one. Also if I open view replication conflicts in EM all conflicts can still be viewed even those I manually resolved. I can't even find a way to seen which conflicts are new ones and which have been resolved already. Does anybody know a way how to reset this numbers without going through all the conflict tables.
In SQL 6.5, when a scheduled job failed, you could see the error message in the history. In SQL 7.0, it simply tells you which was the last step to run. Is there a place which will report the actual error message generated by the task?
I have set up a job as sa.After few days I changed the sa password.Now I am not able to view the latest job history since then.It says the last run as 'succeeded',but it is not showing the latest history since the time I changed the password.It still shows the previous history only.Do I need to delete the job and reschedule it? Thanks!
I feel stupid asking this because I have worked with SQL Server 6.0/6.5 for serveral years now, but I can't seem to find the task history in 7.0. When I look at a task, I can tell if each step ran or not, but I am used to seeing error messages upon failure by right-clicking on the task and choosing history.
How do I find this in 7.0? In casual looking for a few days, I've come up empty.
Hi I have a full database backup scheduled at 2AM in the morning everyday and If I see that job status in Sql Server logs current activity , it shows me that database has been backed up at 2AM....But the problem is when I go to jobs and try to do a view job history it doesn't give me the details of the job and I didn't clear the job history..Can anyone tell me the reason why it doesn't show me the job history.
Hi - In order to restore a DB on another system I need to know the exact expansion history of the source DB in order to create/expand the devices on the target system the same way... Is there a way for me to see the history of expansions I did on a DB ?? Tanx, Paul
I have several transaction tables on which I need to track the changes. That is I need to maitain the history of changes. Only few column values are changed often.
Which is the best way for tracking the changes.
1.Store the whole record after the change ?
Or
2.Store the ColumnName & its respective old & new value ?
Or any other better.
Note : UI part & SP's will take care of the tracking & no plans for triggers.
I am trying to build a cube to monitor the sales performance. My measure is the sale amount. My dimensions include 1) Time 2) Sale hierarchy (salesman, sales office, region), and 3) Product Line. The problem I have is that when the salesman move from one office to another, the sales occured before this movement should continue to be credited to the old office. Only sales happen after the movement should be credited to the new office. How should I implement the cube to achieve this effectively? We have more that 40,000 sales, about 500 offices, in the company to monitor. So I don't think keeping a copy of the sales hierarchy whenever there is a change is feasible.
I have a table with video information ( Name varchar(30) null, Serial_no int(12) null, Agency_no int(8) null, Agency varchar(30) null ) When items are damaged they are sent to our agency #5. I would like view not only the items that are assigned to our Agency but also what Agency owned this equipment BEFORE us.
Today when I tried to troubleshoot a failed job, I found out that the job history is disappreared. Some jobs' history still exist. Does anybody have idea what happened? How can I fix it?
Is there a place where SQL Server logs errors? When I run my Access MDBagainst a SQL 7 back end, I get ODBC errors, which are sometimes a littledescriptive, sometimes not at all. I usually have to execute code directlyagainst the back end to find out what's really going on. And that's if I'mable to reproduce the error. It would be helpful if SQL logged all errors itgenerates, or if something like that can be set up.Thanks,Neil
Is there a way to increase the number of records preserved in jobhistory? I have 82 jobs on my box, 30 of them run every 20 minutesfor 23 hours a day, every 30 days. Another 30 run once, every 30days. The system seems to have a limit of 50 history records for anyjob that hasn't run since yesterday and purges all history records ifthe job is more than a week old. I didn't know if there might be aconfiguration record buried in MSDB or somewhere else that would allowme to increase this size or perhaps a system SP that prunes jobhistory that I could modify.It isn't critical as the system is set to notify me when jobs fail,but still, I'd like to be able to look at a given day and verify thateverything went normally.
