SQL 2012 :: Disable Logins Automatically Depending Upon Expiry Date In A Table?

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


ADVERTISEMENT

Update Table 1 To Table 2 Depending Date Time + ID

Apr 26, 2008

need help please on update only if .

i need to update only the field "val_holiday " (in table B from table A)

and olso to check on table B the "ID" + "new_date" only if exist

and update the field "val_holiday " (in table B)

and at the end of update change the field "field_check_update_if _ok" from 0 to 1
only the row that update in table B


select from table A

update table B

WHERE ..........................HOW ?




----------------------------------------------------------------- table A

ID fname new_date val_holiday field_check_update_if _ok

---------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 999 0

111 aaaa 16/03/2008 888 0

111 aaaa 18/03/2008 77 0

111 aaaa 19/03/2008 9 0

111 aaaa 20/03/2008 111 0

111 aaaa 21/03/2008 12 0



222 bbb 02/05/2008 15 0

222 bbb 03/05/2008 16 0

222 bbb 04/05/2008 9 0

222 bbb 05/05/2008 3 0

222 bbb 06/05/2008 90 0

222 bbb 07/05/2008 3 0

222 bbb 08/05/2008 3 0

222 bbb 09/05/2008 3 0



333 ccc 03/04/2008 4 0

333 ccc 04/04/2008 4 0



----------------------------------------------------------------- table B

ID fname new_date val_holiday

----------------------------------------------------

111 aaaa 15/03/2008 1

111 aaaa 16/03/2008 1

111 aaaa 18/03/2008 1

111 aaaa 19/03/2008 1

111 aaaa 20/03/2008 1

111 aaaa 21/03/2008 1



222 bbb 02/05/2008 3

222 bbb 03/05/2008 3

222 bbb 04/05/2008 3

222 bbb 05/05/2008 3

222 bbb 06/05/2008 3

222 bbb 07/05/2008 3

222 bbb 08/05/2008 3

222 bbb 09/05/2008 3



333 ccc 03/04/2008 4

333 ccc 04/04/2008 4

------------------------------------------------------------------------------

TNX for the help and for all

View 1 Replies View Related

Update Table A To Table B Depending Date Time + ID

Apr 25, 2008

need help please on update only if .
i need to update only the field "val_holiday " (in table B from table A)
and olso to check on table B the "ID" + "new_date" only if exist
and update the field "val_holiday " (in table B)


and at the end of update change the field "field_check_update_if _ok" from 0 to 1
only the row that update in table B




Code Snippet
select from table A
update table B
WHERE ..........................HOW ?




----------------------------------------------------------------- table A
ID fname new_date val_holiday field_check_update_if _ok
---------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 999 0
111 aaaa 16/03/2008 888 0
111 aaaa 18/03/2008 77 0
111 aaaa 19/03/2008 9 0
111 aaaa 20/03/2008 111 0
111 aaaa 21/03/2008 12 0

222 bbb 02/05/2008 15 0
222 bbb 03/05/2008 16 0
222 bbb 04/05/2008 9 0
222 bbb 05/05/2008 3 0
222 bbb 06/05/2008 90 0
222 bbb 07/05/2008 3 0
222 bbb 08/05/2008 3 0
222 bbb 09/05/2008 3 0

333 ccc 03/04/2008 4 0
333 ccc 04/04/2008 4 0


----------------------------------------------------------------- table B
ID fname new_date val_holiday
----------------------------------------------------

111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1

222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3

333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
------------------------------------------------------------------------------
TNX for the help and for all

View 5 Replies View Related

SQL 2012 :: Insert ID Column Value Into Table Depending Upon Input Filename

May 19, 2015

I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.

Lets Say
Location1 : C:LoadFilesImport
Location2 : D:LoadFilesImport

Location one has three different set files starting with

First Set: AP_1, AP_2,AP_3,
Second Set: VD_1, VD_2, VD_3,
Third Set: BK_1,BK_2,BK_3,

This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101

AP_1 goes to Table1
AP_2 goes to Table2
AP_3 goes to Table3

If the file name Starts with VD_ , custiD values should be as 201
If the file name Starts with BK_ , custiD values should be as 301

after processing all these files in first location, the SSIS looks for files in second location and does the same?

--How to achieve CustID depending upon file name ?

View 0 Replies View Related

SQL Server 2012 :: Increment Date Based On Time Value Automatically

Jul 1, 2015

I have a client data which has the candidate id, a start date which will have only the date in varchar format and a time value associated to that date in varchar format in a seperate column.

To give a brief explanation about the data a candidate will come to study center at any time point in a day for example 10:00 AM on 20-10-2014. The start date will be 20-10-2014 and from 10:00 AM he will have test based on the time point. So if the time point is 2 HR, then at 12:00 PM he will have a test. If the time point is 8 HR, it will get added with 10:00 AM and based on this he will have the test at 06:00 PM. When the timepoint reaches 00:00 the start date needs to be the next date instead of 20-10-2014.

The start date needs to be appended with the time value such that when it crosses over the time 00:00, the start date needs to get increased by 1 ie the next day. I have added the start date to the time by using the code below

CAST(STARTDATE as datetime) + CAST(CAST(STUFF([TIME],3,0,':') as time(0)) as datetime) as [EXPECTEDDATETIME]

By this above code i have created the expected datetime however

I will not be able to hardcode the value due to dynamic data. I tried to apply >= and < to the time value something like

case when MyTime >= '00:00' and MyTime < '08:10' the Dateadd(day, 1, date)

This works perfect but my concern is that I cannot put the value 08:10 because it will not a constant value for all rows.

I have provided a screenshot of my data and a expected date column for reference.

Candidate StartDateStartTimeExpected DateTime Timepoint
1 20141020 1000 2014-10-20 10:00:00 0 HR
1 201410201200 2014-10-20 12:00:00 02 HR
1 201410201400 2014-10-20 14:00:00 04 HR
1 201410201800 2014-10-20 18:00:00 08 HR
1 201410200000 2014-10-21 00:00:00 12 HR
1 201410201200 2014-10-21 12:00:00 24 HR
2 20141020 1100 2014-10-20 11:00:00 0 HR

[Code] ....

I have also attached the data for reference.

View 9 Replies View Related

How Can I Update CERTIFICATE Expiry Date ?

Aug 15, 2006

Hi,

I created a master key and

CREATE CERTIFICATE mycer
WITH SUBJECT = 'data encrypt',START_DATE = '12/1/2005',EXPIRY_DATE='7/9/2006'
GO

CREATE SYMMETRIC KEY mySymetric WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE mycer,password='mypass';
GO



and i created a table with varbinary field and i added 2 encrypted records and i got it properly

i need to update the expiry data for my certificate , so i dropped the symetric key and their certificate and run the following

CREATE CERTIFICATE mycer
WITH SUBJECT = 'data encrypt',START_DATE = '12/1/2005',EXPIRY_DATE='12/12/2006'
GO

CREATE SYMMETRIC KEY mySymetric WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE mycer,password='mypass';
GO

i tried to get the added record , i got a null value somthing is happened while dropping the key for the encrypted data??

any help ?

Jim

View 5 Replies View Related

Any Way To Disable SQL Logins After Failed Tries?

Jan 20, 2004

We're using a canned app that only uses SQL Server logins (it will NOT use Windows logins to access SQL Server)

SQL Server logins seem to lack even the most rudimentary security features such as expiring passwords and automatic disabling after a set number of failed logins. Bad. Bad Microsoft.

Has anyone figured out a way to graft this on after-the-fact?

I can do it in an awkward fashion by auditing failed logins and going back to read the error log, but this isn't real time by any stretch.

View 2 Replies View Related

How To Calculate Expiry Date Using Stored Procedure

Oct 16, 2004

Hi, i'm trying to calculate on the expiry date for product which include the start date and end date.

the product can't b displayed if the start date haven't reached the date, besides, it oso can't b displayed if it has expired.

the storedprocedure tat i've written is:

WHERE
DateDiff(day, StartDate, getDate())<=0
AND
DateDiff(day, EndDate, getDate())>=0

i wonder why it can's show the result!!! is the code correct?

View 3 Replies View Related

Expiry Date Auto Change Field

Feb 2, 2008

Hi,

My table has more columns than this but I will just use the ones here tht are important.
I have two columns in a table these are "ExpiryDate" and "Archived".
When the row is created the "Archived" field will be null but the user will have entered a expiry date. When this date is reached I would like the "Archived" data to change to "1". Is this possible? Would I have to do it through some kind of script or can I set the database to do this automatically?

Thank you, Mark

View 3 Replies View Related

Update Status Field After Expiry Date

Mar 26, 2007

Consider the following tableCustomercustId char(10)accountExpiryDate datetimeaccountStatus bitNow, I want to update the accountStatus to False as soon as thecurrent date becomes accountExpiryDate.I think it can be done using "SQL Agent" but my webhost doesnt provideme access to that. I have access only to the Query Analyzer.ThanksShane

View 12 Replies View Related

Can Date Modified Col Be Automatically Updated W/o Trigger For Each Table?

Apr 10, 2006

Hello,
I am using SQL Server 2005 and ASP.NET 2.0. We have a very simple content management system where we have to keep track of date last modified for each row in all of our content tables. I know there's a "timestamp" datatype that is used for replication scenarios, but is there anything similar that I can use to set up a date_modified column for each of my content tables that will automatically update with GETDATE() whenever anything in a given row is updated?
Or do I have to create a date_modified column of smalldatetime datatype and write a trigger on update for EVERY single table of content that I have in the database? It seems there should be an easier way to do this than to write 20 triggers for my 20 content tables.
Thanks!

View 1 Replies View Related

SQL 2012 :: Automatically Create Insert Statements For Table Data

Jun 10, 2014

How to create insert statements of the data from a table for top 'n' rows. I know we can create using generate scripts wizard, but we can't customize the number of rows. In my scenario i got a database with 10 tables where every table got millions of records, but the requirement is to sample out only top 10000 records from each table.

I am planning to generate table CREATE statements from GENERATE Scripts wizard and add this INSERT STATEMENT at the bottom.

EX : INSERT [dbo].[table] ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )

View 4 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

SQL Server 2012 :: Adding Flags Depending On Criteria

Feb 12, 2014

I have a data output with many rows. In order to group things with flags, I do this in excel using 2 formulas which *** a flag of 0 or 1 in 2 new columns.

This takes a long long time as I have hundreds of thousands of rows and wondered of I could do it in sql?

Its transact SQL and the formulas I use in excel are:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2>=C2-1/24)*($C$2:$C2< C2+1/24))> 1,0,1)
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

How I can do this in sql??

The columns above do not relate to the actual columns I use, just an example.

View 9 Replies View Related

T-SQL (SS2K8) :: Inserting Date Range Depending On Inputs

Mar 14, 2014

I'm trying to modify a date range, but it's much more complicated.

Let's say I have existing date ranges of:

BlockIDStartDateEndDateActive
182013-12-31 00:00:002013-12-31 00:00:001
192014-01-01 00:00:002014-01-23 00:00:001
202014-01-24 00:00:002014-01-25 00:00:001
212014-01-26 00:00:002014-02-04 00:00:001
222014-02-05 00:00:002014-02-13 00:00:001
232014-02-14 00:00:002014-02-15 00:00:001

I've created code that will do the following (depending on the inputs):

1) Create new start and end dates
2) Inactivate obsolete date ranges

View 8 Replies View Related

Reporting Services :: Chart - Group Values Depending On Date Range?

May 20, 2015

Currently I report our monthly fees broken down into 4 weeks per month by using 4 separate datasets with the following code

SELECT
SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT
FROM
Practice.ibvSalesByJob
INNER JOIN Practice.idvJobType

[Code] ....

The second dataset then has the date code changed to 

AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/08/' + Cast(@DateYear AS char(4)) + ' 00:00:01'
AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:00'

The third

AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:01'
AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:00'

The fourth

AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:01'

Now I was hoping so that I could report the above data in one chart and do an expression on the category and group the dates so I would just have one dataset like below but four separate columns saying Week 1, 2 3 and 4 and then the sum filtered in line.

SELECT
SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT
FROM
Practice.ibvSalesByJob
INNER JOIN Practice.idvJobType

[Code] ....

If I could somehow with SQL tie in all 4 separate datasets and display them as Week 1, 2, 3 and 4 underneath the dataset and selectable.An even simpler solution maybe just understanding how charts work, I can get it so I display the 4 separate weeks in the chart however I can't get the bottom line (Category Group) to display Week 1, 2, 3 and 4.

View 8 Replies View Related

SQL 2012 :: CDC Is Disable

May 20, 2014

CDC is disabling when we do bulkinsertsupdateschange schema .how to findout why CDC disabled and how to prevent it ?

View 3 Replies View Related

SQL 2012 :: Enable / Disable AG Listener

Jul 3, 2014

How can one enabledisable Availability Group listener? I know AG listener can be deleted and added but didnt find anything to disable or enable that.

View 1 Replies View Related

SQL 2012 :: Disable And Enable Trigger?

May 25, 2015

I have a trigger that I disable so I can do some operations on a table.

After the operations are completed I do the enable using the enable trigger comand.

Do I need to do something else to put back the trigger working as it was before the disable comand?

View 3 Replies View Related

Lost SQL Logins, But Still Have DB Logins - Script To Recreate SQL Logins?

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

SQL 2012 :: Disable File Indexing On Drives

Feb 25, 2014

Is it a best practice to disable "Allow files on this drive to have contents indexed" on NTFS drives used by SQL for its data, log, tempdb, etc?

In what I've read it seems to be a best practice for Filestream objects and Flash storage drives. We don't currently use Filestream objects or have Flash drives.

Are there any benefits or drawbacks to disabling this feature on an NTFS drive connected to SAN LUNs under mount points?

View 2 Replies View Related

SQL Server 2012 :: Disable Only One Trigger Action?

May 6, 2014

I have a trigger that executes AFTER INSERT, UPDATE, DELETE. Is there a way to disable and then reenable only the "AFTER DELETE", letting AFTER INSERT, UPDATE act normally?

View 6 Replies View Related

T-SQL Create Self-reference In Depending Table

Jul 23, 2005

Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?

View 7 Replies View Related

Updating A Table Depending On 2 Databases

Dec 3, 2007

In a package we have statements pointing to 2(or more) different databases on the server. When moving between environments, is there an easy way to change statements like:

UPDATE t1
SET t1.name = t2.name,
t1.age = t2.age
FROM DB1..Person t1
INNER JOIN DB2..PersonToo t2
ON t1.PersonID = t2.PersonID

I can think only of building the statements replacing the database names with variables, but that's not an easy way. I do not know how to use package variables in this situation.
1 way of doing this may be by using a Lookup or Conditional Split and use the resulting dataflow in a SP or such to update, using parameters...
It all sounds very messy, and I still don't know how
Any ideas?


TIA,

View 7 Replies View Related

SQL 2012 :: Table Property For Retention Date?

Feb 21, 2014

I want to store data warehouse source tables and files in an Archive schema and then delete / drop them after a specified period of time.

Is there a table property that I can set (can't find one) or some other mechanism so that I can easily identify these tables with a script.

If there is no such property or feature within the database engine I will define a metadata table and record it there, but a property or similar that I can set at archive time would be very handy.

View 0 Replies View Related

SQL Server 2012 :: DATE As Dynamic Table Name

Jan 13, 2015

I am trying to select data from table that have YYMM as table names, they are formatted table1410,table1411, table1412. I am trying to format it like this

declare @tablename60 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -1, getdate())) as varchar(2)),1,2)

But this is hard coding the YYMM, and I would like to have it pull 30,60,90 days fromthe first of the current month. I am having a bit of trouble formatting, how to accomplish this.

View 1 Replies View Related

SQL Server 2012 :: Rule Of Thumb When To Disable Indexes?

Jun 19, 2014

It's often said or done that when inserting or updating into a 'large' table that disabling the non-clustered indexes can is needed for performance.

Now I know the obvious way to find out if this is best or not is by testing the different options. I was wondering if there was a rule of thumb to this?

Say you have a table with half a billion rows and 4 non-clustered indexes and are only updating half a million rows then sometimes disabling every night and re-enabling can take way more time than the actual update. Haven't found an articles advising to disable them when a table is over X rows and you are updating Y% of them...

View 5 Replies View Related

Altering Strings Depending On Data In A Table.

Sep 10, 2007

Hi Guys,

I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.

Let's say I have a Dictionary table, 2 columns:

Word | Definition

And I have a string - "The cat sat on the dog"

If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"

At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"

The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...

I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )

Any ideas?

Thanks in advance
-Craig

View 4 Replies View Related

Insert Data Depending On Year Value In Table

Jul 15, 2004

Hi there,

Can anyone help? I currently have this query that imports distinct data into a prices table with a couple of contraints. This table became to large so I have now split this down into yearly tables (dbo.price --> dbo.price2001, dbo.price2002 etc). I get the data each day in another table that may contain data for different years so I need to be able to look at this data and insert into the right yearly tables.

E.g. present query:

Insert Prices
select distinct M.ids, C.zdateT, 1.0,0 from mapid as M,datacorrect as C
where M.asset = 'money'
and C.zDate not in (select zdate from price where sid >=15)


So I need to run this query for each yearly table with a date listed in the datacorrect table (insert prices(yr) yr = 2002,2003,2004 etc)

Any ideas would be appreciated!!

Thanks

S

View 3 Replies View Related

Table Visiblity Depending On Filter Selection

Apr 21, 2008

H, need help please!

I have two tables on a reports, each having it's own dataset.

I want to set the visibility of the table depending on the filter selection.

So if the user selects: show open then show only the table with the open dataset.

I don't now how to this using the expression in the visibility expression.

Please Assist!

Regards

View 2 Replies View Related

Hiding A Table Row Depending On Page Number?

Jul 5, 2007

Hi, Everyone.
i am wondering if there is some way to hide a table row depending on the page number.
I have tried to find a way to access the global page number to use it in an expression for the visibility property on the table row, but i havent found anything useful.

View 14 Replies View Related

Set Visibility Of An Image Depending On If A Certain Table Has No Rows

Aug 22, 2007

Hi,

I'm wondering if it's possible in SSRS 2005 to have an image's hidden expression to depend on if some table in the report has no rows.

Thanks,
Liron

View 4 Replies View Related

SQL 2012 :: Automatically Log Off From Database

Apr 3, 2015

Our auditors are trying to enforce a requirement that all users be disconnected from any application or database after 15 minutes of inactivity. Is there any way to force a logoff within SQL Server? For example, someone is in Management Studio connected to a database. They should be logged off after 15 minutes of no activity.

View 1 Replies View Related







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