Hello,
Can anyone tell me how to monitor logins/logouts to Analysis Services
databases? I use Profiler to do so in SQL, but cannot find a way to do
it in AS.
Thanks,
Tim
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Hi,
I need to be able to get info about failed login attempts which need to be running all the time and logging info such as hostname , application name, host machine, etc. The best way I can see to do this is by crerating a trace. Then loading the info from the trace into a table for later use by using fn_trace_gettable. But this requires that the trace be stopped and the definition removed for the trace file to be populated.
Is there a way to be able to log this info constantly into a table without having to stop and then recreate another trace. Also it requires that the table created by the function is dropped.
Anyone any idears?
Thanks
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.
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,
i need to set up some security standands in sql 6.5/7 . These includes auditing login attempts success and failure, access to db objects etc. I know sql is has very limited capabilities . can anyone tell me how to implement this without using event viewer etc??
I want to know are there any other third party tools that are used for Auditing the SQL Server Like...DBAudit.
View 2 Replies View RelatedHi all:
I need some help with this. I have a development server and all the developers log in as sa. Lately test data has been mysteriously deleted from selected tables. I need to track the time that the activity is taking place so I can figure out who might be playing this little game. Can anyone suggest what I can do to find this out? Your quick response will be appreciated.
I'm wanting to do some auditing with sql 2k and wish to get the users first and last name of the windows account to log into a table. You can easily access the windows account name by using the System_User keyword. However, is it possible to get the first and last name of the system_user? If so, how?
Thanks.
Does anyone have a query to determine if auditing is turned on and what it is set to (It needs to be set to failed logins)? Also where the log directory is going? I need the query to work on both SQL Server 2000 and 2005 servers. Any help is appreciated.
-Kyle
I am trying to create a sql script that will check the database instance for any new objects that are created in any database on my system. These objects I want to audit are users, tables, databases, stored procs, etc.. I also want the script to email me and write the information to a text log file. Thanks in advance for any help.
View 2 Replies View RelatedBelow is a script I found that will help me turn on C2 auditing. The problem is that I am generating trace files that take up way too much space.
I need to know what column id and event id, so I am only turning on "failed login" and nothing else.
exec sp_trace_setevent @TraceID, x, x, @on
Code Snippet
CREATE PROC AuditTrcProc AS
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1
-- Please replace the test InsertFileNameHere with an appropriate
-- filename prefixed by a path, eg.. c:MyFolderMyTrace. The .trc extention
-- will be appended to the filename automatically. If you are writing from
-- remote server to local crive, please use UNC path and make sure server has
-- write access to your network share
declare @cmd sysname
set @cmd = 'copy c: empsessiontrace.trc c: empsession' + cast(cast(rand() * 1000000 as int) as varchar)
print @cmd
exec master..xp_cmdshell @cmd
set @cmd = 'del c: empsessiontrace.trc'
print @cmd
exec master..xp_cmdshell @cmd
exec @rc = sp_trace_create @TraceID output, 2, N'c: empsessiontrace.trc', @maxfilesize, null
if (@rc != 0) goto error
-- Client side file and table cannot be scripted
-- set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 13, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 16, @on
exec sp_trace_setevent @TraceID, 17, 17, @on
exec sp_trace_setevent @TraceID, 17, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
return @rc
finish:
return @TraceID
Is there any way to know the auditing table name in sql server 2008.
I am performing auditing for practice . i selected application log to store the audit logs.
I want to know in which table auditing results wil be stored .
I wanted to set up a trigger to alter me or log to a table when someone is added to a server role. The event for the trigger I wanted to use is ADD_SERVER_ROLE_MEMBER. When trying to create the trigger, I get the following information:
"ADD_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.
Hello,
We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.
Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.
Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...
It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)
3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.
We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?
Thanks!
Marc Lacoursiere
RooSoft Computing
I'm a VB programmer creating apps to write/edit/delete data to a SQLServer 2000 database.For HIPAA requirements, I need to track all changes to all the tables inour database. I'm looking for the easiest and cheapest solution.I have no experience writing Sql Server 2000 triggers and storedprocedures.I have found the following application which might do what I need to do:Upscene: MSSQL Log ManagerPrice $125http://www.upscene.comKrell Software: OmniAuditPrice $399http://www.krell-software.comApex SQL Software: Apex SQL AuditPrice $599http://www.apexsql.comLogPI: LogPIPrice $825http://www.logpi.comLumigent: Entegra for SQL ServerPrice ???http://www.lumigent.comAny comments sugestions appreciated.Gregory S. MoyInformation Processing ConsultantEpiSense Research ProgramDepartment of Ophthalmology & Visual SciencesUniversity of Wisconsin - Madison
View 1 Replies View RelatedI have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.
We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.
Hi,
I have few tables. I want to identify the RECORDS for a table which has been created/modified for a particular date and time. I don't want to write a trigger to capture the event for add/update.
Is there any system table which track for date and time using stored procedure each individual records which has been last updated or newly created records??
Note : The application already created without lastModified date and each table... so, we don't want to modify the application or db.
Database : SQL Server 2000 sp4
thanks in advance.
I am trying to import this years worth of failed logins and last successful login for each user out of the logs using master.dbo.xp_readerrorlog. The script essentially loops through the linked servers I have on my DBA box and reaches out for the log data. It works, but here is the error I am getting on most of our production servers:
OLE DB provider "SQLNCLI11" for linked server "AWSCADENCEDB01" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 17
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "AWSCADENCEDB01" was unable to begin a distributed transaction.
I know how to enable distributed transactions on the servers that error out, but if it is not needed for anything other then my audit script, I doubt the business will approve me turning on distributed transactions at those locations (so I am not even going to ask).
I am attempting to setup a singular audit .rdl with the information I want to review quarterly.
CREATE PROC [dbo].[Import_Login_Data]
AS
IF EXISTS (
SELECT 1
FROM master.sys.servers
WHERE is_linked = 1
[Code] ....
good afternoon,
Does any1 know how to prevent a login screen being encountered upon hitting the SQL dbase? I'm sure there is a table in SQL server that you can create that allows this (and the number of rows retrieved as well). Can any1 help me out?
Thanks in advance for any and all help
gurmi
I have succeeded in reattaching the db to the new server, but the old logins do not work, as has been experienced by many people.
What follows was is a solution from an earlier post (last year) on this topic. I tried running the queries from Master in on the new server, and got the following error:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'sysusers' with unique index 'sysusers'.
The statement has been terminated.
The sql queries I am using are:
insert sysxlogins(srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language)select srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language
from "oldServerName".master.dbo.sysxlogins
where name not in ('DISTRIBUTOR_ADMIN', 'REPL_PUBLISHER', 'REPL_SUBSCRIBER', 'TRACEUSER', 'SA')
AND
insert sysusers(uid, status, name, sid, roles, createdate, updatedate, altuid, password)
select uid, status, name, sid, roles, createdate, updatedate, altuid, password
from "oldServerName".master.dbo.sysusers
where name not in ('public', 'INFORMATION_SCHEMA', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader','db_datawriter', 'db_denydatareader', 'db_denydatawriter', 'guest', 'dbo')
The old server is set up as a linked server on the new server.
This suggests that the information is all in the new server. Any tips? TIA D. Lewis
We are in process of migrating of databases from one Server to another, Can anyone please suggest me how to transfer or copy the logins from one Server to the other instead of creating them all over again??
Thanks in advance...
Hi,
Can anyone help me to script sql server logins.
Thanks,
ravi.
I need to restore the logins from prod server "A" to a dev server. The dev server has many databases from different prod servers. If I restore the master from prod A to dev, i'll lose the logins on dev for the other dbs. How can I get the logins from prod A to dev without losing the other logins for the other dbs?
Your help is greatly appreciated!
Can someone remind me where Server Logins are stored in SQL Server?
View 7 Replies View RelatedI have an application that runs off an sql server 2000 sp3 database. I have taken a backup (.bak file) of the production database and restored it to another server for testing purposes. the users cannot login to the new server because the logins have not been transfered to the new server. I am looking to transfer the logins using the procedure outlined at the following link: http://support.microsoft.com/kb/246133/ under the header "Create and Run Stored Procedures in the Master Database". what I need to know is will this procedure affect the production server in any way? (I can't have that) and is there anything else I need to do after this to let the users login to the test server with the same name and passwords?
thanks for any help
I need to create a new login that only has access to 2 Views.
I have tried everything, A New Role, Schema, set the Securables but when I connect to the server with MS Access or MS Excel, and sign in using that Login, I can still a bunch of tables. I only want this Login to be able to run either view and not see anything thing else.
Historically, many of us have used bulk copy to move selected contents of syslogins from one server to another so that the logins stayed in sync for things such as replication, log shipping, etc.
In SQL 2005, I have no access to passwords (this is proper from a security perspective) so I can't generate a file with bulk copy. I also can't update sys.server_principals, so it doesn't really matter that I can't export the passwords.
So what is the SQL 2005 solution? Suppose I decide to mirror my database. How do I get the logins that are users of that database over to the mirror server? Scripting the logins won't work because it generates a random password. sp_change_users_login requires me to provide a password but then I would have to know each login's current password or give them each a new password. This is viable with 2 users; it is not viable with 2000!
I know that Copy Database Wizard will move the logins with the passwords intact, but it will assign new SIDs to SQL Server authentication logins. That's fine as long as it is a one-time move but not if I am maintaining a warm standby.
This problem has been one of the most frequent problems posted on newsgroups, etc. in the past. But we have had solutions. I fear that we do not have one in SQL 2005.
I'd appreciate any help you can provide.
Thanks,
Sharon
I am replacing a server so I need to migrate everything. The old server is running SQL2000 and the new server is running SQL2005. I am trying to write an SSIS solution to migrate everything for me and I can't even get started because I get the error "The source server can not be the same as the destination server". At the same time I am changing the name of the Domain so the two machines arenot even members of the same Domain. I am doing this over the Internet so the machines are not even on the same subnet. The only thing I can think of is that the machine names are the same so even though the domains are different therefore the full names are different, the NetBIOS names are the same. Could that be the problem?
View 1 Replies View RelatedHi guys,
I have tried to use the sp_help_revlogin script to move logins from a sql 2000 server to a sql 2005 server. It works for users that have a password. But if the password is blank it will fail. Is there a work around for this since I can't change the users passwords?
I have also tried SSIS package but that too doesn't work because of the same reason.
Can anyone help...
I need to move several databases to a new server while retaining the same logins/permissions. Books Online indicates that DTS can move the SQL Server logins, but it sets the passwords to NULL in the process. Is there any way to move the logins and keep all passwords/permissions intact?
View 2 Replies View RelatedI'm changing servers and want to copy all the logins from the old server to the new server. Is there anyway to do this?
View 5 Replies View RelatedI am developing an application in VB.NET that is using a sql database. I am trying to start testing with multiple computers running the app while the database is on a remote computer. I have 2 desktop computers networked right now with 1 of them having the database and the other I want to run the program on. I am trying to figure out how to use windows authentication to login to sql server from the remote computer. I can login using Sql authentication but it is taking forever and I keep getting timeout errors so I wanted to try Windows Authentication to see if it is faster. If someone could walk me through the steps I need to take or give me some suggestions to fix my problems I would greatly appreciate it.
View 2 Replies View Related