Our application running on SQL server 2008 since its storing confidential data we would like to mask the sensitive data in non prod environments.Currently I would like to mask data for one table and their 4 columns data. Basically I would like to know how to do this and how it’s being consumed by downstream systems?Are downstream needs to undo the mask data on their end?
View 5 Replieswhen prod db is restored in test server, i have to hide/replace sensitive fields (name, phone etc)Is it doable in SQL 2008 and SQL 2012?
View 5 Replies View RelatedI have a column called study in a table which has the number of studies such as 10000 or 9934. I wish to display the result 10,000 or 9,934. I wish to put a comma in the numbers. How can I achieve this using a select statement.
View 5 Replies View RelatedI one of the requirements, i have to mask the data of a column based on a security parameter. how do we do that in Ad Hoc reporting?
View 3 Replies View RelatedI find that dynamic masking does not work on joining tables in SQL Server 2016 CTP2.1.
For examples, I create the following table:
CREATE TABLE [dbo].[HRM_StaffAppointment](
[StaffID] [nvarchar](11) NOT NULL,
[ApptSeqNo] [smallint] NOT NULL,
[ReportingDept] [nvarchar](10) NULL,
[Code] ...
Then I apply mask on StaffID and RankDesc.
alter [dbo].[HRM_StaffAppointment] alter column [StaffID] add masked with (function='default()')
alter [dbo].[HRM_StaffAppointment] alter column [RankDesc]
add masked with (function='default()')
When User A logged in and query on HRM_StaffAppointment, StaffID and RankDesc are perfectly masked. But User A can remove the masking using another table:
CREATE TABLE [dbo].[staffID](
[staffID] [nvarchar](255)
) ON [PRIMARY]
select a.*
from dbo.HRM_StaffAppointment as a
left join dbo.staffID as b
on a.StaffID = b.StaffID
It looks like a security hole to me, or I'm doing anything wrong?
I am trying to implement data masking based on user login and not sure why this is not working. I have the dimensions DimBrand, DimProduct and DimUser. I should mask the BrandCode with 'XXXX' nothing but in the report all the BrandCode should appear but few of the code will be masked if the user is not belongs to that group. I have a fact table FactProduct in this. In the cube I created all these 3 dimensions and the fact table. I created a new dimension DimBrandMask and I separated the code over there with a relationship with the actual DimBrand dimension. In the cube a reference relationship is set up with the measure group. Created a role with read access.
In the dimension data tab of role I put the below MDX to allowed set.
NonEmpty([DimBrandMask].[Brand Code].Members, (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]") ,[Measures].[Dim User Count]))
And in Denied Member set i put the below MDX
IIF( (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]"), [DimUser].[Access Right].&[False]), NONEMPTY( [DimBrandMask].[Brand Code].Members,(StrToMember("[DimUser].[Login Name].[Login Name].["
+ UserName() + "]"), [DimUser].[Access Right].&[False], [Measures].[Dim User Count])),{})
Note I created one measure group from the DimUser table and the measure [Dim User Count] is used in the above query.
I am expecting some result like below
Brand BrandCode Count
Brand1 b1 6
Brand2 XXXXX 5
Brand3 XXXXX 10
Hello,
how can I mask an char such as: ' or " in an sql-statement.
Thanks a lot, Chris
We are in the process of doing a database redesign. Our system architect is planning to a bit masked integer field vs. lookup tables. I personally have never used bit masking at the db level. I was wondering if this is a good design choice.
I should also note that our application will be db agnostic so it will need to work on SQL, Oracle, MySQL, etc.
hi
is there any option by which i can mask my parameter for password.
Is there a way to mask the contents of a cell or column? I have a table that stores passwords and I would like to mask the password much like Access does with asterisks. I know I can restrict the column based on user but that creates other problems on the frontend.
View 1 Replies View RelatedAnyone who knows a smarter way to select a special part of a text string.
A have done like this now but mabye the textstring changes. I know that the GO02 in the future will expand.
KnowgoodP_GO02_AA_K_20070807_2010_L.BBB
But the underscore _ sign is like a seperator and will always be there to separate the words.
I need the 20070807_2010 and fomat to 2007-08-07 20:10
SELECT @state = STATE,
@thetime = CONVERT(char(20),STATE_TIME,20),
@id = ID,
@textstrr = TEXTSTRRWHERE SUBSTRING(FILENAME, 21,4)+ '-'+ SUBSTRING(FILENAME, 25,2)+'-'+SUBSTRING(FILENAME, 27,2)+' '+ SUBSTRING(FILENAME, 30,2)+ ':'+ SUBSTRING(FILENAME, 32,2)+':00' between @starttime and @theEndTime
I need to give the below script which contains CREATE CREDENTIAL query to an app team.
CREATE CREDENTIAL crdntl WITH IDENTITY = '<service_acct>',
SECRET = '<pwd>'
GO
My concern is i don't want the password to be visible. Basically i want to use this credential to create a proxy which is then used to run SQL Agent backupjob on number of SQL servers. Also, i cannot leave the SECRET value as blank (as the MSDN suggests.)
Is there any way to mask the password OR any other alternative solution.
I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...
View 4 Replies View RelatedI have a dataset which contains sensitive person information that can be sliced by various demographic groups.
When I use PowerPivot to query it if I use more than say 5 groups the resulting value within a cell may fall below an acceptable value of say 10 and render the group of people identifiable.
What I would like is some way to generate the lowest group such that any combination < 10 returns 10 and any hierarchy/group above the detail uses this new value for its summary.
Here is an example of how it would look now (I have used only 3 groups Gender followed by Age group and Age to keep it simple):
Gender Age group Age Count
Male 32
20-25 30
21 9
24 10
25 11
26-30 2
30 2
This is how I would like it to look:
Gender Age group Age Count
Male <41
20-25 <31
21 <10
24 10
25 11
26-30 <10
30 <10
I have actually tried to do it myself but the DAX expression is beyond me. I use ISFILTERED(<column name>) or HASONEVALUE to get the lowest level group (Age) to work but of course I can't get the levels above it in the hierarchy to calculate based upon the interim result...
IF(HASONEVALUE(Measure[Age]),if([Count]>5,[Count],if(ISBLANK([Count]),0,5)),BLANK())
I have designed a form and created two parameter fields to accept username and password from the user. Based on the values entered above, I am validating inside a stored procedure. I am unable to mask the text the user enters in the password field. I could not locate any mask property. Anybody can help me?
View 1 Replies View RelatedIs there an elegant way to fill the empty space leading a textbox in a table with periods, similar to a table of contents effect?
When I do not allow the textbox to grow and just append a long string of periods it looks fine in my report preview, but after I deploy everything appended after the primary field in that text box is missing?
My report has a lot of data to the right but it is collapsible so the fields to the left are a good distance away. This is why I am trying to include light visual aids that assist lining up data values. I am not stuck on the leading period idea but it seems the least cluttered.
Aaron
Msg 7314, Level 16, State 1, Line 42
The OLE DB provider "SQLNCLI10" for linked server "192.168.0.40" does not contain the table ""CP_DW"."dbo"."StgDimSalesTargetSetup"". The table either does not exist or the current user does not have
permissions on that table.
Insert into [192.168.0.40].CP_DW.dbo.StgDimSalesTargetSetup
Select t.SalesTargetCode,t.SalesTargetId,t.TargetDefination,
--LEFT(DATENAME(MM, t.PeriodFrom), 3) + '-' + CONVERT(VARCHAR(4),DATEPART(YY, t.PeriodFrom)) AS MonthYear
--CONVERT (char(8),PeriodFrom,112) [Datkey],
Left(CONVERT (char(8),PeriodFrom,112),6) [Monthkey],t.PeriodFrom,t.PeriodTo,t.TargetAmount
FROM [SO_SalesTargetSetup] t
How can I copy data like firstname, lastname, email from Active Directory into a SQL Server table?
View 4 Replies View RelatedI have a database that is off site, it has a database one it that I have a Linked server connection to. We have no other means of connecting to that server database except via that linked server connection. Is there a way I can Copy eplicatemoves etc... that database to an internal server with out doing it table by table. It is apx 80gig db
Copying a backup file is not an option either, and Mailing it on media make it a significant delay.
Tried loading JSON data to Sql Server ? Sample format is given below..Don't see any easy way doing it except writing some C# code deserialize it.
[ {
"name" : "peter_2.jpg",
"createdDate" : 1259728960000,
"lastModifiedDate" : 1308174976000,
"Secondary" : [ {
"Id" : 106275817,
"Sid" : 1
[code]...
I need to store file(s) to SQL without streaming / reading at Server. I have created a Web API with AngularJS and SQL. e.g.
var fileType = httpRequest.Files[file].ContentType;
var fileStrm = httpRequest.Files[file].InputStream;
var fileSize = httpRequest.Files[file].ContentLength;
byte[] fileRcrd = new byte[fileSize];
var file_Name = Path.GetFileName(filePath);
fileStrm.Read(fileRcrd, 0, fileSize);
Is it possible to send file data to SQL (in bytes) without streaming / reading at server?I don't want to put a load on server for large files. just read the data and send them to SQL where SQL will do the streaming and store data as varbinary.
I have a business requirement to build a tabular data model, where I need to mask information of other Agents from a given Agent but I still need to show the overall sales of the given product.
For eg: IF an Agent is in APAC region he should see APAC region sales and also should be able see the sales of the same product in other region without knowing region specific break down.
For Agent "Tom" in APAC region, the numbers will look like this
APAC_Sales = 100,000
Other_Sales = 500,000
And if "John" is in NA region, then the number will look like this for him
NA_Sales = 200,000
Other_Sales = 400,000
I wanted to create "Roles" based on the Region, so all the agents belong to "APAC" region will have same view as Tom and "NA" region agents will have John's view.
In C# .NET I have the possible to create some validations of my data, with regulary expressions. Do SQL have the same feature? I will like to do an data validation of all my insert statement inside the sql-server. Is that possible?
View 5 Replies View Relatedi want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation
[code]...
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
I am trying to import data from an excel Sheet to SQL Database using OPENROWSET. After import I found that all the cells containing data of more than 2000 length got truncated to 255 characters only. I tried finding the solution and found that We need to have the data with length more than 255 in first 8 rows of Excel sheet. It worked for me also. But In real scenario the data that I cant do the manual work on excel. I tried out with Dot Net utility and SSIS package also but the truncation is still the issue.
INSERT into tmp_Test
SELECT
*
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel
12.0;Database=D:Book1.xlsx', [Sheet1$])
I have a simple table data i want want to show row data in to column data.
SELECT clblcode,mlblmsg
FROM warninglabels
My expected result will be
0001 0002 0003 0004
------- ------- -------- --------
May Cause.... Important..... Take madi........... Do Not Take.......
I need to create SQL to convert multiple rows data to single row for given subscriber#. Below is the example. In below example , I've 4 family members with same subscriber # and each members have separate rows, I want to combine member data for same subscriber in 1 row, so there would be a 1 row for each subscriber.
View 6 Replies View RelatedI have a simple query
select distinct
bl_rec_ref
,freq_cd
from hgmprty1
where(bl_rec_ref is not null and bl_rec_ref not like '%SCH%')
That produces the following output (part shown)
However what I need to show is only bl_rec_ref values where there is no corresponding freq_cd of QTLY.
Looking at the example data above Rows 45 and 46 have the same bl_rec_ref value with a MTHLY and a QTLY value in freq_cd so therefore I don't want this to appear in my output. Similarly with Rows 47 and 48 were the bl_rec_ref has both QTLY and MTHLY values so again should be excluded.
However looking at Rows 51 to 55 which has a bl_rec_ref only showing once for each value of MTHLY and no value for QTLY so these Rows are required in the output.
I have a temp table
drop table #temp
create table #temp (id int, dat datetime)
insert into #temp values(1,'2015-03-01')
insert into #temp values (2,'2015-02-01')
insert into #temp values (2,'2015-01-01')
insert into #temp values (3,'2015-01-01')
insert into #temp values (3,'2015-03-01')
insert into #temp values (3,'2014-12-01')
insert into #temp values (2,'2014-12-01')
insert into #temp values (1,'2014-12-01')
select * from #temp
The result i am expecting is 2014-12-01. I need to get the max(dat) for data available for all ids.In the sample set, only for 2014-12-01, there is a row for id 1,2,3. I need to get that date as result.
I have a table that capture the size of databases every week.
CREATE TABLE [dbo].[DBGrowthRate](
[DBGrowthID] [int] IDENTITY(1,1) NOT NULL,
[DBName] [varchar](100) NULL,
[DBID] [int] NULL,
[CurSize] [decimal](10, 2) NULL,
[Gatherdate] [datetime] NULL,
[Serversname] [varchar](50) NULL
) ON [PRIMARY]
What I want is to make a query that will return
DBName, Cursize (oldest Gatherdate, CurSize (most recent GatherDate, ServersName
How to make this happen, I have tried working with Max and Min Gatherdate but that doesn't work.
how I can transform this input (in SQL Server 2008 R2)
SiteID EdateTime
L5500 2015-07-30 05:00:48.000
L5500 2015-07-29 05:00:49.000
To be…
SiteID StartDate EndDate
L5500 2015-07-29 05:00:49.000 2015-07-30 05:00:48.000
I have a date into format YYYYMM. Data type is int. I need to make query where every time it will return me last 3 months, but without current one. For example I have data for months below
201510
201509
201508
201507
Query should return all records for 201509, 201508, 201507.
I was trying lot of solutions founded in internet like this one:
Date_Column >= DATEADD(MONTH, -3, GETDATE())
or
DATEDIFF(MONTH, my_date_column, GETDATE()) <= 3
but it doesn't work.