SQL 2012 :: Code That Audits All Logins To Production
Jul 2, 2015
So I'm using the 2012 SSMS to connect to a SQL 2008 database, upon which we have code that audits all SQL logins to production and notifies us via email that someone is using logins they shouldn't. Mostly it's to notify us if people other than the DBAs are using these logins from their desktops instead of using their windows accounts.
This morning I opened up SSMS 2012, logged into my production servers in Object Explorer using Windows Auth. An hour later, I had to update an exception table, so I opened a new query with SQL Auth and used a SQL-only login it. Immediately the email pops that someone on my desktop is using a SQL Login. That's okay. That's expected.
What I didn't expect is that after I closed the query window, the email kept popping. The query window isn't even open / connected to production any more, but SQL still thinks I'm logged in using a SQL Login instead of with Windows Authentication (which is what I used on the Object Explorer connection).
Is there a bug with 2012 that causes a new connection type to affect all current connections? I.E., did it change my DB Windows Auth connection to SQL Auth on the other connection?
View 0 Replies
ADVERTISEMENT
Jan 14, 2008
I am curious to find out by other people on how they manage incremental code changes to SSIS packages. I am in a situation now where we deliver production SSIS packages to a client, where the client changes the connection details to the data sources and exectues the packages on a nightly basis.
We have had instances where clients are asking for enhancements and modifications to the SSIS package whereby we would develop it in our development environment then replace the existing copy in the clients production environment. There has been instances whereby the packages would work as desired on our end, but it requires further refinement on the clients side. As such, we have had instances where we had to "back-out" into the previous packages.
I am just trying to find out what methods or procedures other people are following to acknowledge situations like this or similar to this. Currently we are following an exhaustive method of having two copies of the code on the client side, the "live" code is never modified, just the secondary copy, then when all is good, we move this code into the "live" code. Does anyone have any means of performing incremental updates on SSIS packages easily?
View 8 Replies
View Related
Jan 1, 2008
Hi
I am currently developing a DBA management console for a company who have 45 SQL Servers, 20 of which are in the production environment. As part of the development I have created a DBA dashboard with various alerts and information dials regarding the state of the SQL Infrastructure. I came accross a problem yesterday that I cannot find the answer to.
One of the alerts I have developed is a simple "Heart Beat" indicator that displays that the SQL Server is present and "alive". To do this I have a central database running on SQL 2005 which has linked server connections toall 45 SQL Servers. This central database runs a simple select statement on each server (something that I know works if the server is there). The select is run as dynamic SQL as I need to build up the SQL as part of a cursor to place the server name within the from clause. Here is the select I am using: -
select top 1 dbid from [server].master.dbo.sysdatabases
If the server is not there an error occurrs. I trap this and record that the server may not be present. The cursor then moves on to the next server to check.
This works fine when ran from Query Analyser. I can simulate a failed server by deleting the link server connection. For the server in questions an error is raised when the select is run, I store the error and on the Dashboard screen you can see an indicator saying that the server is currently non-contactable (i.e. failed Heart Beat).
My problem comes when I try and schedule this procedure. If I run it as an SQL Agent Job it behaves differently. Here, as soon as the error is detected it bombs out stating that the step has failed. I don't mind it stating that the step has failed, what I want it to do is to complete the cursor loop on all the servers. You see at the top of the procedure I set all the heartbeats flags to 0 (i.e. not contactable) so I need to to complete the run. I have search the Agent set-up and can find no flag to stop it halting processes when an error occurs.
The servers are configured not to allow RPC and I would rather not have to put a job on every server to tell me they exist as this would need managing going forward.
Any help you can offer would be greatly appreciated - Happy New Year by the way!
View 4 Replies
View Related
Jul 3, 2015
I'm learning how to use audits in SQL Server 2014, but when I opened the Security folder there was no Audits folder inside. I did a bit of research and I found out that auditing is a feature in all editions of SQL Server 2012 onwards so I should have the Audits folder in order to create a new audit. There doesn't seem to be many posts like this online because the only posts I found about missing audit folders were about SQL Server 2008 (which doesn't include auditing in the standard version).
View 8 Replies
View Related
May 3, 2002
Hi
I took the 3 databases backup on Production Server
Customer
Banking
Accounts
I restored these Databases into a another new server Server name : PDST84
Then i am not able to see the Loging under the Security , how can i get the Logins what ever existing on Production.
There 120 Logins under SQL Logins, how can i get those logins in to this Server, Both Servers are running SQL 2000.
View 1 Replies
View Related
Jan 21, 2007
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
View 3 Replies
View Related
Jan 29, 2014
Full Text Searches are working on my test server, but not on my production server. My test scenario is as follows:
CREATE FULLTEXT CATALOG FTC_Test
AS DEFAULT
AUTHORIZATION dbo
CREATE FULLTEXT INDEX ON guest.FtsTest(FullName)
KEY INDEX PK_FtsTest ON FTC_TestI wait briefly and then check to see if the index has been populated:
SELECT * FROM sys.fulltext_indexescrawl_end_date is not null,
So I'm assuming I don't have to wait anymore before I try some FTS searches. Right? I can't get any queries to return anything, though.
The following tells me the full text item count for the table is zero:
DECLARE @TableId INT
SELECT @TableId = id FROM sys.sysobjects WHERE [Name] = 'FtsTest'
SELECT OBJECTPROPERTYEX(@TableId, 'TableFulltextItemCount') AS TableFulltextItemCount
As mentioned, the full text search works on my test server. Both of them are SQL 20012 SP1 (11.0.3000) x64 running on WinServer 2008 R2 SP1.
View 3 Replies
View Related
Mar 20, 2014
just i see a database and a table 'tbl_OutBox_MT' where there is now primary key and have index (non unique, non cluster). and it store almost 3000000 data per everyday. and wipe out data from their and archive all data to other location and broadcast this table 'tbl_OutBox_MT' by mobile operator everyday from morning to evening. but when it perform broadcast it to mobile operator it takes huge time. because this table gather data from different sources (tables) by using complex query and INSER INTO statement and insert into this table.
I need to perform first, my observation is there is no primary key. when i run any complex query into this table it takes huge time and sometimes shows transaction deadlock error.
CREATE TABLE [dbo].[tbl_OutBox_MT](
[TRAN_ID] [varchar](36) NOT NULL,
[OUT_MSG_ID] [int] IDENTITY(1,1) NOT NULL,
[OUT_MSG_ID_TELCO] AS (CONVERT([bigint],((((CONVERT([varchar](4),datepart(year,[PROCESS_TIME]),(0))+case len(CONVERT([varchar](2),datepart(month,[PROCESS_TIME]),(0))) when (1) then '0' else '' end)
[code]....
View 1 Replies
View Related
Dec 22, 2014
(SQL 2012 Standard)
I have a database with one 320 .mdf file and one 200GB .ndf file.
I would like to reconfigure my database to have four 200GB .mdf files. How do I get from here to there?
View 5 Replies
View Related
May 29, 2014
I refresh QA environments with copies of our production database quite often. Many of the users also have read-only logins to production, but not all. I've noticed that in QA the users in the restored database are matched up with QA server logins that no longer have "DBO" as their default schema. We almost always use DBO, nothing else.
On the most recent restore, I didn't drop the target DB first, just restored with "replace." Does it matter whether I drop or replace in this instance?
The one user reporting issues could not open the database in management studio to view objects/tables etc. I noticed their default schema was their domain login, so fixed that and they now no longer have the issue.
View 0 Replies
View Related
Oct 17, 2014
We have a particular database sat on SQL Server 2012 box along with about 20 other databases.
What I require is a method/Script/Audit that will simply track anyone who logs (successfully / unsuccessful) into this one particular database on the server (The single database is the key as the end user does not want information on any of the other databases that sit on the server), it also has to log time the attempt was made and it must track the logins via SQL Server or the application itself that is attached to the database.
View 1 Replies
View Related
Oct 16, 2015
I am in the process of writing a generic PowerShell script (that executes T-SQL statements) to copy all or some databases from one SQL Server 2012 SP2 instance to a second instance that runs SQL Server 2012 SP2 or higher. The script is designed to be used on demand, and part of its execution will involve reconciling the logins between the source and destination instances.
I am looking for a reliable way to reconcile the logins between the instances in a somewhat repetitive (i.e., "on demand") fashion. There is no SSIS in the environment; so the SSIS transfer logins task to not available to me.
View 5 Replies
View Related
Jul 23, 2014
I am attempting to create a Test db from a full backup of the production db. With 2012, I cannot do it the the way i had done it in previous versions (and now i understand why because of Logical names).
The Test db runs in the same instance as Prod db.
I attempted to run this but come up with errors. This is what i executed:
RESTORE DATABASE TEST FROM DISK = 'E:<path>FULL.BAK'
WITH REPLACE, RECOVERY,
MOVE 'PROD' TO 'E:<path>TEST.MDF';
The errors are all cannot execute due to PROD is in use.
View 9 Replies
View Related
Oct 17, 2014
I keep getting requests to increase the width of a varchar colum every now and then.
I want to ask if its perfectly ok when you have active users connecting to the application to do this?
View 7 Replies
View Related
Mar 3, 2015
Setting up Transaction Replication in test environment. I am willing to bet that most of you take a production backup (if so, how, and using what?), restoring the database to your test environment, then running a snapshot to your subscriber and away you go.
But perhaps you take a backup of your publisher and subscriber, if so, how do you know there are no inconsistences because there were transactions sitting on the distributor?
What do you do if you have additional indexes on the subscriber for reporting, that are not on the publisher?
Here at work we are having issues with getting consistent databases set up with T Rep, missing rows, duplicate keys at subscriber etc. How to avoid these issues.
View 0 Replies
View Related
Feb 24, 2015
I am new to SQL Server and I am trying to create a script / possible a cursor that will allow me to create multiple logins in one pass - without having to type each one.
I have 20 users USER1, USER2, USER3, USER4... but I can't seem to figure out how to get those users into a loop
so I can end up with something like this.
CREATE LOGIN <user> FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[English]......
I realize I can type this statement 20 times but perhaps there is a faster way -
View 3 Replies
View Related
Jun 18, 2015
I am working on a Login migration issue. I have the Master database from the original database, that I have attached as a User Database, call it OriginalMaster. I also renamed the mdf and ldf files, before attaching, just in case.
I want to select the logins from the OriginalMaster, and using sp_help_revlogin script all the Logins out. I know there were many logins in that database. But what I am seeing, are all the Logins from the Current Master database. Which are only 7 Logins.
How can I select the logins, or script out the Logins from the OriginalMaster database?
View 1 Replies
View Related
Jun 4, 2014
Disable logins on access expiry date(Not windows password expiry). we grant access to users on databases only for 60 days. So access is only valid for 60 days. Then the user should again request access to the database going thru security clearance. Thn the DBA's enable the login. Maintaining all these logins of users manually is causing more confusion.
As we know, we dont have any inbuilt functionality to automatically disable logins in SQL Server.
I have a table where the logins and expirydate were recorded in a DB.
Using this table and SQL Server agent. Can i achieve this process automated ?
CREATE TABLE [dbo].[LoginsExpiry](
[LoginName] [varchar](50) NULL,
[ExpiryDate] [date] NULL,
[Roles] [varchar](500)
) ON [PRIMARY]
GO
View 3 Replies
View Related
Sep 16, 2014
In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".
View 2 Replies
View Related
Mar 23, 2015
We log ship our production database tansaction logs to a secondary database on a separate remote domain.
Incidentally, this was not set up via the "Ship Transaction Logs" wizard, but instead we had to use the various system stored procedures after restoring a copy of the database on the remote domain in standby mode.
We now have an issue where users are unable to access the remote secondary database without having sysadmin privileges. Clearly we want to avoid this!
We want to be able to use Windows Authentication mode on the server of the secondary database. But since the two domains differ, is it possible to even achieve this?
How can we set up read-only access for users on the secondary database without giving them sysadmin privileges?
View 3 Replies
View Related
Jul 17, 2001
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
View 3 Replies
View Related
Apr 3, 2007
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.
View 2 Replies
View Related
Oct 17, 2014
I designed the AlwaysOn wrong, but every time we fail over from primary server to another server, my applications cannot connect because the sql logins cannot connect to their default databases. Once I run the command to link the login with the user in the default database then the users are able to connect. Did I do something wrong when designing AlwaysOn?
View 9 Replies
View Related
Jul 31, 2015
I wrote code to archive Data.I created a table that set the maximum number of records to archive and delete and loop until finished or a set a flag in a table to stop archiving. I was able to limit the number of records to commit.
View 9 Replies
View Related
Aug 25, 2014
Someone claimed that the query:
INSERT INTO [table1] SELECT * FROM [table1]
was a result of running DBCC DBREINDEX.I wasn't able to replicate this when I did a trace on my local machine, but SQL Monitor from Red-Gate shows that query happening for at least some of the tables that are being reindexed via a job. Is there anyway to examine the actual code inside DBREINDEX to see exactly what commands it may execute?
View 3 Replies
View Related
Dec 28, 2014
I have 10k indexes I need to rebuild and each time the script reaches an error it stops all further activity. How can I append 'GO' to the end of each line so it will continue on error messages?
Once I have the syntax I can do a find and replace function in Notepad++
USE [AdventureWorks2014] + char(13) + char(10) + GO
ALTER INDEX [IX_Person] ON [Person].[Person] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO
ALTER INDEX [IX_Emp] ON [HumanResources].[Employee] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO
************** Truncate ***********
View 3 Replies
View Related
Jun 4, 2015
I Want to monitor Replication count of object (Table )if it is not equal to Publication (Table ) and subscriber (Table ), It have to send mail with count difference.
View 9 Replies
View Related
Apr 18, 2014
We made SSIS package in dev environment in windows 2008R2 and SQL server 2012. Same packages were placed on SAN disks in cluster environment and are invoked with security contexxt of admin user and wih dtexec utility ( we call this using an sp). rarely but procedure completed i see the log text file by SSIS package and find the return code 5 means package is unable to load. i canst found what are the exact reasons for return code 5.
View 2 Replies
View Related
Jul 6, 2015
exporting an SSIS from a 2008 R2 SQL Server and re-import it into a SQL 2012.
I have inherited an SQL 2008 R2 Server running two SSIS packages. I have both as .dtsx files and & a manifest to install.The task at hand is now to migrate those to a new SQL 2012 server.[There is also an IIS running on that machine and project-specific .dll and stuff within the IIS document root. I do not know if these .dlls relate to the IIS web page or to the SQL .dtsx) But the migrated web site runs fine].
Installation of the packages into the MSDB works out flawlessly and one of the scripts runs fine, but the other fails to run with the error:
"Error: the binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully."
Google tell me something about a "Pre-compile option"-setting on server as explained here. I can not find this option setting anywhere in SSMS. Also as the migration is from 2008 R2 -> 2012 this should not be relevant as in both versions pre-compilation should be automatic, right?
- I installed visual studio 2013 community
- I installed SSDTBI
- Start the "Integration Services Import Project Wizard"
to import the SSIS directly from the "Integration Services Catalog"
However, things don't quite work very well - Trying to import (from the locally installed SQL instance) it will not display the tree of SSIS stuff, but only the root directory.
So importing directly from the running system won't work. Let's see if we can get somewhere with the .dtsx As I _do_ actually have the .dtsx files here, why not open them up directly in Visual studio and try to get compiled whatever needs to be compiled.I create a new "integration Services" Project and open up the .dtsx into this project. ==> LOTS of errors of all kind.
(The job of this script is to fetch messages from an Exchange.)But opening up this specific bit of code doesn't work a bit - there is no binary code in it and how to reate it or where to get it from...
View 4 Replies
View Related
Sep 8, 2015
I am querying with a SELECT statement against an address table that has a post code column with corrupt data.
Sample record:- Northants NN4 0NB
Should be NN4 0NB
I have used the following on another column:-
LEFT(A.Addr1,CASE WHEN CHARINDEX ('(', A.Addr1) =0 THEN LEN(A.Addr1) ELSE CHARINDEX('(' ,A.Addr1) -1 END) AS 'Address 1'
but unable to correct this problem?
View 9 Replies
View Related
Sep 14, 2015
Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.
I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.
Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')
[Code] .....
View 3 Replies
View Related
Apr 21, 2015
After adding Service Reference to WebService, the Script Component has Binary Code not found, red circle not showing these are the steps I followed:
1) Add Script Component as Source
2) Add 3 x Output Columns Col1,2 and 3
3) Add HTTP Connection URL>..Binary Code not found, red circle showing
4) Add test code to Sub CreateNewOutputRows Dim i As Integer = 6 Binary Code not found, red circle not showing
5) Add Service Reference URL...Binary Code not found, red circle showing again
Should just adding Service Reference cause Binary Code not found, red circle to appear. I have to set precompilescripttobinary option , however cannot see this option in 2012
View 3 Replies
View Related
Sep 3, 2015
Currently we are using Custom Delivery Extension for SSRS 2008R2 We are planning to move it SSRS2012
My Question is: Whether we can use the same Code used for SSRS2008R2 to SSRS2012?
if not what code changes we should do?
View 3 Replies
View Related
