Cross Database Ownership Chaining Not Working With Windows Group
Mar 18, 2008
We are having a problem with cross database ownership chaining. Below is a description of the problem:
I have a domain group named DOM1GROUP1
I have a domain user DOM2USER1 who is a member of DOM1GROUP1 (note that they are in different domains)
I have a database DB1 which contains a stored procedure (st_insertdata) that does an insert in a table (tb_data) on DB2
DOM1GROUP1 has been granted login rights on the SQL Server
DOM1GROUP1 is a user in both DB1 and DB2
DOM1GROUP1 has execute rights on procedure st_insertdata and insert rights on table tb_data.
All objects are owned by the dbo schema.
The database owner for DB1 and DB2 is sa
When DOM1USER1 executes st_insertdata an error is returned:
The server principal "DOM1USER1" is not able to access the database "DB2" under the current security context.
I've played around with the options "trustworthy" and "db chaining" but these do not make any difference. The only thing that fixes this problem is if I create a login for DOM2USER1 and grant it access to DB2 (with no other rights other than membership of the public role).
It seems that SQL Server does not recognize that DOM2USER1 is a user in DB2 by virtue of its membership of the domain group DOM1GROUP1. Is there a way to get this to work without granting explicit rights to DOM2USER1?
View 4 Replies
ADVERTISEMENT
Oct 5, 2006
I saw this option in SSMS under "Servers, Property, Security tab". I looked at BOL but I still don't understand what it is or what it is used. Any help appreciated.
TIA,
Barkingdog
View 3 Replies
View Related
Mar 10, 2008
i have a database with cross database ownership chaining enabled. data base was detached and reatached as a result owner changed from sa to account that was used during reataching. will this affect chaining?
View 1 Replies
View Related
Apr 22, 2014
I am getting the following error when trying out DB chaining and Ownership chains
Msg 916, Level 14, State 1, Line 1
The server principal "user" is not able to access the database "Test" under the current security context.
I did run these scripts to setup DB Chaining
ALTER DATABASE [Test1] SET DB_CHAINING ON;
ALTER DATABASE [Test] SET DB_CHAINING ON;
I then setup user using a sysadmin role with select permission to a view. The View is on Test1 and points to Test database using a synonymn setup on Test1.
According to all the articles I have read this is all I should do and the view should then run.
View 3 Replies
View Related
Oct 25, 2007
i have enable cross database chain,but it return error message:
The server principal "S-1-9-3-1149532189-1170944071-2610337685-3868961652." is not able to access the database "db2" under the current security context.
I list the sql script as follows:
Code Block
use master;
go
create database db1;
create database db2;
go
use db2
go
create table table1
(
col int
)
go
use db1
go
create user u1 without login
go
create proc p1
as
insert into db2.dbo.table1 values(1)
go
grant execute on p1 to u1
execute as user='u1'
exec p1
thanks
View 9 Replies
View Related
Jun 17, 2007
full outer joins and cross joins not working!?!?
am using vc++2005, ADO, and MSAccess 2003. MS "documentation" straight out of the VC++2005 help facility at
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_vdt01/html/419ef633-5a89-41a2-aefe-03540afc9112.htm
provided the following code samples for different types of joins
inner join
Code:
SELECT title, pub_name
FROM titles INNER JOIN
publishers ON titles.pub_id = publishers.pub_id left join
Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles LEFT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idright join
Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idfull join
Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles FULL OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idjoin
Code:
SELECT *
FROM authors CROSS JOIN publishers i created two MSAccess Tables:
Merge1:
K1 x
---- ----
a 1
b 2
c 3
Merge2:
K1 x
---- ----
b 20
c 30
d 40
e 50
and executed the following code to test the different joins. the first three joins worked but the last two did not. would appreciate any insight. DBM is an instance of an AccessDBManager class i have written to encapsulate interactions with Access DBs.
Code:
DBM.SQLExtractString = "select * from Merge1 INNER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 2 records returned (K1 = b,c)
Code:DBM.SQLExtractString = "select * from Merge1 LEFT OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 3 records returned (K1 = a,b,c)
Code:DBM.SQLExtractString = "select * from Merge1 RIGHT OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 4 records returned (K1 = b,c,d,e)
Code:DBM.SQLExtractString = "select * from Merge1 FULL OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this did not work - 0 records returned instead of 5 (K1 should = a,b,c,d,e)
Code:DBM.SQLExtractString = "select * from Merge1 CROSS JOIN Merge2";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this did not work - 0 records returned instead of 20 (5 * 4)
appreciate any ideas/comments to get the last two joins to work.
thanks - j
View 3 Replies
View Related
Feb 13, 2007
Hi There
I have service broker working 100% with an initiator - forwarder - target, all in the same domain using windows authentication on the endpoints, for all instances' the sql server service run under the same domain account, which in turn is granted connect on the various endpoints. This all works 100%
However i have a scenario where the initiator will be in a different trusted domain.
I need to know if i am correct in thinking that the initiator sql server servcie account can run under DomainAsqlservice, the target instance sql server servcie can run under DomainBsqlservice, then on the forwarder i simply grant connect on the forwarder endpoint to both DomainAsqlservice and DomainBsqlservice.
Is this correct, will this work?
Thanx
View 6 Replies
View Related
Sep 25, 2003
I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager
Now when I try to add Mikel, Im getting error 15401.
Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?
Can I go into the Master database and just change Mike login to Mikel ?
Thank you
View 3 Replies
View Related
Jul 10, 2015
If I Have a table like
Id(identity), PupilPersonId, EducationTypeId,VehicleTypeId,EducationDate, EducatorId,Canceled
661187 9242382 2 1 2015-07-07 00:00:00.000 O_2 False
661183 9242382 2 1 2015-07-08 00:00:00.000 O_2 False
661186 9242382 1 1 2015-07-08 00:00:00.000 O_2 False
661178 9242382 2 1 2015-07-10 00:00:00.000 O_2 False
661185 9242382 2 1 2015-07-10 00:00:00.000 O_2 False
The result I want is the unique rows from columns:
PupilPersonId, EducationTypeId,VehicleTypeId AND there MAX EducationDate
SELECT er1.* FROM EducationResult er1
INNER JOIN
(
SELECT
er.PupilPersonId, er.EducationTypeId, er.VehicleTypeId, MAX(er.EducationDate) as EducationDate
[Code] ....
I like to know is there another approach with CTE and or Cross Apply I can use instead?
View 5 Replies
View Related
Aug 3, 2007
Hello All,
I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.
Consider that we have Product Category, Product and its monthly sales information retrieved as follows:
CategoryID
ProductID
ProductName
Month
UnitPrice
QtySold
SalesAmount
1
1
Panel
Jan
5
10
50
1
1
Panel
Feb
5
15
75
1
1
Panel
Mar
5
20
100
1
2
Frame
Jan
10
30
300
1
2
Frame
Feb
10
25
250
1
2
Frame
Mar
10
20
200
1
3
Glass
Jan
20
10
200
1
3
Glass
Feb
20
20
400
1
3
Glass
Mar
20
30
600
I would like it to be converted into following result set:
CategoryID
ProductID
ProductName
UnitPrice
QtySold_Jan
SalesAmt_Jan
QtySold_Feb
SalesAmt_Feb
QtySold_Mar
SalesAmt_Mar
1
1
Panel
5
10
50
15
75
20
100
1
2
Frame
10
30
300
25
250
20
200
1
3
Glass
20
10
200
20
400
30
600
I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.
Thanks.
View 1 Replies
View Related
Apr 1, 2008
My system was crashing so I reinstalled everything. including SQL 2005 Express with management studio. I renamed the computer from the previous install. but for some reason the sql 2005 express management studio remembers the old machine name even when I create a new DB inside the management studio with the new computer name.when I try to change the ownership in properties-files it cant find the 'new computer nameuser'i double checked the computer name its correct. The funny thing is i installed express with the 'new computerame' anyone have a clue that may help with this what file needs to be edited?Thanks inadvance for any help.
View 2 Replies
View Related
Jun 8, 2006
I have created tables within SQL Server database. My tables I have created do not have the dbo ownership(?)...how does one transfer a table to dbo?
advTHANKSance
View 7 Replies
View Related
Aug 25, 2006
I have a database called sky and its tables, views, procs and functions owned by sky. I need to replicate the sky database to another server. I had problem because those objects have ownership sky not dbo. I can not change ownership when replicate the database. How do I replicate database objects that are not owned by dbo? Is this possible or I have to change ownership from sky to dbo before replicate the database?
Thank you very much for your input and suggestions.
View 1 Replies
View Related
Jun 26, 2006
we have a group of developers which have created and asked us (DBAs) tocreate many objects in the databases including tables / storedprocedures / functions / etc.since our company is growing, however we have an increasing amount ofobjects that have either been abandoned or have several versions.in an effort to clean of the huge amount of clutter and anytime thatsomething simple like a stored proc needs to change, it is almostimpossible to predict exactly where we will see negative effects ofthis change.i am looking for a system (preferably without developing our own tool)that would keep track of history of database objects (in terms of whocreated it and what purpose it has) as well as link that to all thedevelopers/users we might need to notify of any changes to that object.Also, this should be linked to the application which rely on theobject.bottom line... every object in the database needs to have at least 1corresponding contact as well as the applications which us it.with this information, we can much more easily maintain objects in ourDBs.thx
View 3 Replies
View Related
Dec 25, 2005
I installed VS2005 on my laptop at the office and of course, got the default instance of SQLEXPRESS. When I take my laptop home and log in (I use my NETWORKlogin login everywhere), I can't authenticate to use the database -- it doesn't like my login unless I'm physically at my office.
So, thinking I'm clever, I changed my SQLEXPRESS instance to use 'SQL Server and Windows Authentication mode' and tried to use the 'sa' login in my connection string. Login failed for 'sa' and since I had never set a password (yes, I tried using a blank) I executed the following query on my db:
exec sp_password @new='WHATEVERPASSWORD', @loginame = 'sa'
The query works (?) but it my connection string doesn't and I'm still unable to use the management tool when not on the original network.
So, I thought I'd add a new, super secret special user, but alas, that has completely escaped me. That frightens me a bit because I'd really like to be able to deploy my database to my web host and trust the my connection strings will work.
So, here are my questions:
1. Can I set up my instance so that I don't have to be on the network on which I installed it in order for it to recognize me (since it's self contained on my machine)? If so, how?
2. How do I create new SQL users?
3. Has the connection string changed significantly, and consequently, am I missing something fundamental in my attempts to connect? If someone could toss out a working SQL Server Express 2005 connection string that doesn't use Windows authentication, that would be really appreciated.
Here's some info from my box, if it helps.
Microsoft SQL Server Management Studio Express 9.00.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
Thanks for any help out there.
View 3 Replies
View Related
Mar 8, 2004
I'm having a really odd problem.. this is basic SQL, so it should be easy for most of you. For some strange reason, the quantity is not being summed up. Any idea why this would be happening? Everything else is grouped. The SQL is below, and some of the results are below that (you can see that it SHOULD be grouping them properly).
SELECT TOP 100 PERCENT dbo.cp_elements.campaign_id, dbo.cp_campaigns.name AS campaign_name, dbo.cp_elements.item_no, dbo.cp_elements.name,
dbo.cp_orderables.est_qty, dbo.cp_orderables.qty_increment, SUM(dbo.cp_order_detail.qty) as qty, dbo.cp_attribs.price
FROM dbo.cp_orderables INNER JOIN
dbo.cp_elements ON dbo.cp_orderables.element_id = dbo.cp_elements.element_id INNER JOIN
dbo.cp_attribs ON dbo.cp_orderables.orderable_id = dbo.cp_attribs.orderable_id INNER JOIN
dbo.cp_order_detail ON dbo.cp_attribs.attrib_id = dbo.cp_order_detail.attrib_id INNER JOIN
dbo.cp_selected_shiptos ON dbo.cp_order_detail.shipto_id = dbo.cp_selected_shiptos.shipto_id INNER JOIN
dbo.cp_campaigns ON dbo.cp_elements.campaign_id = dbo.cp_campaigns.campaign_id
GROUP BY dbo.cp_elements.campaign_id, dbo.cp_campaigns.name, dbo.cp_elements.item_no, dbo.cp_elements.name, dbo.cp_selected_shiptos.state,
dbo.cp_orderables.qty_increment, dbo.cp_attribs.price, dbo.cp_orderables.est_qty
ORDER BY dbo.cp_elements.item_no
RESULTS:
16,Project Z,10S,Ten Spot,10,1,0,9.9900
16,Project Z,10S,Ten Spot,10,1,15,9.9900
16,Project Z,10S,Ten Spot,10,1,0,9.9900
That 2nd from last column is the qty_increment.. it's spitting out 3 rows when it should just show one, with 15 as the qty_increment.
Thanks for any help/clues!
View 2 Replies
View Related
Apr 17, 2008
This query gives me Customer Sales data. SQL Server 2005. This is grouped by Region and Location. A customer such as 'ABC' should only show up once per Location, but it is pulling in some companies more than once. Can anyone help me figure out how to group this so this gets fixed?
SELECTRegion,
Location,
WarehouseCode,
CASE
WHEN CustRank <= 19 THEN CustomerNumber
ELSE 'AllOthers'
END AS CustomerNumber,
CustomerName,
MonthLessEleven,
MonthLessTen,
MonthLessNine,
MonthLessEight,
MonthLessSeven,
MonthLessSix,
MonthLessFive,
MonthLessFour,
MonthLessThree,
MonthLessTwo,
MonthLessOne,
CurrentMonth,
CurrentYearTotal,
LastYearYTD,
LastYearTotal,
CASE
WHEN CustRank <= 19 THEN CustRank
ELSE 20
END AS CustRank
FROM(
SELECTgla.Region,
gla.Location,
ihh.WarehouseCode,
ihh.CustomerNumber,
cm.CustomerName,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 11 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEleven,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 10 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTen,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 9 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessNine,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 8 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEight,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 7 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSeven,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 6 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSix,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 5 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFive,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 4 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFour,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 3 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessThree,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 2 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTwo,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessOne,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentMonth,
SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentYearTotal,
SUM(CASE WHEN DATEADD(YEAR, - 1, GETDATE()) > ihh.SOTransDate AND DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS LastYearYTD,
SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) LastYearTotal,
ROW_NUMBER() OVER (PARTITION BY gla.Region ORDER BY gla.Region, SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) DESC) AS CustRank
FROMMAS_BIF_AR1_CustomerMaster AS cm
INNER JOINMAS_BIF_ARN_InvHistoryHeader AS ihh ON ihh.CustomerNumber = cm.CustomerNumber
INNER JOINMAS_BIF_ARO_InvHistoryDetail AS ihd ON ihd.InvoiceNumber = ihh.InvoiceNumber
INNER JOIN
(
SELECTAccountKey,
Account,
CASE SUBSTRING(Account, 5, 3)
WHEN '936' THEN 'North Region'
WHEN '908' THEN 'East Region'
ELSE 'Unknown'
END AS Region,
CASE SUBSTRING(Account, 5, 3)
WHEN '900' THEN 'ALE'
WHEN '902' THEN 'ATO'
WHEN '904' THEN 'BOW'
WHEN '906' THEN 'BRY'
WHEN '908' THEN 'BPT'
WHEN '910' THEN 'BYD'
WHEN '912' THEN 'BUF'
WHEN '914' THEN 'CLE'
WHEN '916' THEN 'GRN'
WHEN '920' THEN 'DXN'
WHEN '924' THEN 'CTH'
WHEN '926' THEN 'ELC'
WHEN '928' THEN 'FTL'
WHEN '930' THEN 'FTW'
WHEN '932' THEN 'I35'
WHEN '936' THEN 'GAI'
WHEN '000' THEN 'GAI'
WHEN '939' THEN 'STW'
WHEN '940' THEN 'GRE'
WHEN '942' THEN 'HEN'
WHEN '944' THEN 'FTS'
WHEN '948' THEN 'JAC'
WHEN '952' THEN 'JEN'
WHEN '956' THEN 'KIL'
WHEN '957' THEN 'MCA'
WHEN '958' THEN 'MIN'
WHEN '960' THEN 'NOC'
WHEN '962' THEN 'ODE'
WHEN '964' THEN 'BTP'
WHEN '966' THEN 'RA'
WHEN '968' THEN 'RIF'
WHEN '970' THEN 'SWD'
WHEN '971' THEN '3PS'
WHEN '972' THEN 'ROC'
WHEN '976' THEN 'SJO'
WHEN '978' THEN 'SMB'
WHEN '980' THEN 'STO'
WHEN '982' THEN 'TOL'
WHEN '984' THEN 'VEL'
WHEN '985' THEN 'CFP'
WHEN '986' THEN 'CLM'
WHEN '988' THEN 'WHI'
WHEN '992' THEN 'WRA'
WHEN '995' THEN 'ADM'
ELSE 'Unknown'
END AS Location
FROMMAS_BIF_GL_Account
) AS gla ON gla.AccountKey = ihd.SOGLSalesAcct
GROUP BYSUBSTRING(Account, 5, 3),gla.Region, gla.Location, ihh.CustomerNumber,
cm.CustomerName,
gla.Account,
ihh.WarehouseCode
) AS x
View 2 Replies
View Related
Jan 21, 2008
Hi,
I am trying to access the SQL server using windows authentication but its not working. But others can work on their windows authentication for the same server. Anything needs to be done from my end. Please explain.
View 7 Replies
View Related
May 9, 2007
Running Server 2003, had to do a re-install from CD (wrote over existing Windows install). Now SQL won't run. I also lost all program shortcuts, since the new install created new user settings for me.
When attempting to run any SQL task, such as the Management Studio, the following appears: "The application has failed to start because the application configuration is incorrect. Reinstalling the application may fix the problem."
What options do I have to re-register the applications into Windows? Is my only hope to re-install from the SQL CD? Have I lost all of my SQL settings (users, etc.) or is there hope that I can regain it after re-installing SQL? My data files are on separate partitions, and I have a backup from the previous night, if that helps. Conceivably I could do a full restore of Windows from the backup, but that would probably not work properly.
View 4 Replies
View Related
Sep 17, 2015
This is for SQL Server 2005
When logged onto a server and connecting using windows authentication (either by server name or localhost) I am getting in fine, but when I connect using the IP I get the following error.
I can log in using SQL Server authentication with the IP.
View 2 Replies
View Related
Feb 12, 2008
Using SQL Server Express 2005, I have two databases. AppDB - The main application database.GeoDB - A somewhat static ZIP code / states / other geographic stuff databaseI need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I
think I know:You cannot create foreign keys that reference tables in another database in SQL Server.You
cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).SQLServer
2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like it
wouldn't be as portable as keeping the GeoDB info in its own database,
but maybe I'm being too software engineer-ish here - afraid of low
cohesion, high coupling.I will greatly appreciate any advice I can get, or any more options I am missing. Thanks,Adam Nofsingerucnmedia.com
View 2 Replies
View Related
Oct 21, 2015
I have a query taken from a Crystal Report, and I've been working to modify it for a slightly different purpose. The initial report was designed to take an input of an end date, go back to the last day of the previous month, get Actuals (sales totals), then add up all sales, costs, and purchases up to the end date, then display assorted data.
The new query needs to take a begin date and and end date, go back to the last day of the previous month for actuals, add all sales, costs, and purchases to those actuals until it gets to the begin date. That is now the new Actual, and we need to sum up all transactions from then to the end date.
My problem lies in the fact that I can't get the query to add up the numbers.
The query is here:
SELECT DailyReport.Store, DailyReport.Report_Date, sum(dailyreport_Detail.sales) as Sales, sum(dailyreport_detail.actual) as Actual, sum(dailyreport_detail.cost) as Cost, CompanyGroups.Category, DailyReport_Detail.Product, CompanyGroups.Retail_Inv
FROM (`DailyReport` `DailyReport`
INNER JOIN `DailyReport_Detail` `DailyReport_Detail` ON
[Code] ...
The return data looks like this:
3212,2015-07-31,126.35,1781.20,0.00,Fountain,Hot Dsp Bev,0
3212,2015-07-31,149.17,1311.94,0.00,Fountain,Cold Dsp Bev,0
3212,2015-07-31,666.63,4930.02,0.00,Food Service,My Deli,0
3212,2015-08-01,88.67,0.00,0.00,Fountain,Hot Dsp Bev,0
3212,2015-08-01,109.62,0.00,0.00,Fountain,Cold Dsp Bev,0
[Code] ....
I want it to look like this:
3212,2015-08-04,595.53,1311.94,400.76,Fountain,Cold Dsp Bev,0
3212,2015-08-04,485.85,1781.20,165.66,Fountain,Hot Dsp Bev,0
3212,2015-08-04,2762.05,4930.02,1388.00,Food Service,My Deli,0
View 8 Replies
View Related
Dec 23, 2014
I tried SQL server 2014 express edition for Silent installation using the below procedure. The process got exited without completion in few minutes.
Command used for silent installation is
SQLEXPRADV_x86_ENU /q ConfigurationFile="ConfigurationFile.ini"
Created Configuration File Manually: Below is the File Data
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
IACCEPTSQLSERVERLICENSETERMS="True"
; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will reflect the instance ID of the SQL Server instance.
INSTANCEID="SHAREPOINT"
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION="Install"
; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, and Tools. The SQL feature will install the database engine, replication, and full-text. The Tools feature will install Management Tools, Books online, Business Intelligence Development Studio, and other shared components.
FEATURES=SQLENGINE,REPLICATION
; Displays the command line parameters usage
HELP="False"
; Specifies that the detailed Setup log should be piped to the console.
INDICATEPROGRESS="False"
; Setup will not display any user interface.
QUIET="True"
; Setup will display progress only without any user interaction.
QUIETSIMPLE="False"
; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
X86="False"
; The directory for the extracted service pack files used to update the setup media.
PCUSOURCE="d:d8ad8f24d77c0777a5PCUSOURCE"
; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.
ERRORREPORTING="False"
; Specify the root installation directory for native shared components.
INSTALLSHAREDDIR="C:Program FilesMicrosoft SQL Server"
; Specify the root installation directory for the WOW64 shared components.
INSTALLSHAREDWOWDIR="C:Program Files (x86)Microsoft SQL Server"
[code].....
View 1 Replies
View Related
May 28, 2008
Hi,
I have the following query which is working fine, but its returning a row for each day.
I'd like to modify it somehow so that we had the same data returned, but 1 row for each month. Can anyone offer some insight ?
Any help is much appreciated !!
thanks once again,
mike123
CREATE PROCEDURE [dbo].[select_Stats_LoginHistory]
(
@numDays int
)
AS SET NOCOUNT ON
SELECT CONVERT(varchar(10),LL.loginDate,112) as loginDate,
COUNT(LL.userID) AS TotalLogins,
COUNT(DISTINCT LL.userID) AS TotalLogins_Unique
FROM tblLogins_Log LL WITH (NOLOCK)
WHERE DateDiff(dd, LL.loginDate, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),LL.loginDate,112)
ORDER BY loginDate DESC
table structure below
CREATE TABLE [dbo].[tblLogins_Log](
[loginID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[IP] [varchar](15) NOT NULL,
[loginDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
View 1 Replies
View Related
Nov 17, 2000
Hello, I do not succeed in authorizing the access of a group Windows NT to a linked server, on the other hand if I specify user's name it works.
Please inform me.
Pascal
View 1 Replies
View Related
Nov 12, 2003
Hello,
We use local server windows groups to allow or disallow use of our SQL databases. We are now being asked to move off of our current server. My thoughts are to dettach and attach the database. What is going to happen to all my permissions based on the user groups of our old server? I intend to create the same group names on the new server but they will be newservergroupname as opposed to oldservergroupname. Is it just a case of scripting the database user, roles and object level permissions an manually changing oldserver to newserver?
View 3 Replies
View Related
Jul 20, 2005
Hello,I am putting together a row level security plan for our salesdatabase. I will give a brief description of the method I am thinkingof using to give you an idea of how I will need to be able to discoverthe group or login the user is using to access the data.I have a table called salesfact, it has all the sales info for all thebranches of our company. Each order(row) that is inserted has an entryin the division_number column to describe which branch the orderbelongs to. I have created another table(Branch_Folks) that has fourcolumns; username, windows_group, SQL_Login and division_number.I am using a view and SQL logins to control access to the data basedon the user, the SQL logins give windows groups access to the view.Only users that are added to the specific branch groups will haveaccess to the logins, but if a user is added to the group without mebeing notified, then he will not have a corresponding entry in theBranch_Folks table. Currently I am using the SUSER_SNAME() function todetermine which user is accessing the data from the view that I havecreated. The view uses this select statement to filter the data basedon the user & division.Select * from tsalesfact A, Branch_Folks B where SUSER_SNAME() =B.username and A.division_number = b.division_numberThis method works fine, but I will have to manually maintain the userlist in the Branch_Folks table in case a new user joins the windowsbranch group. I would like to use a function similar to SUSER_SNAME()that can determine the windows group or SQL Login a user is using.Does anyone know of a way to do this??Thanks a ton,Tim
View 1 Replies
View Related
Nov 6, 2007
Hi All,
I am getting error when I try to create a windows group and set default schema to db_owner.
Error message
Alter failed for user 'Domainuser'.(Microsoft.Sqlserver.SMO)
Additional information
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.Sqlserer.ConnectionInfo)
The DEFAULT_SCHEMA clause cannot be used wth a windows group or with principals mapped to
Certificates or asymmetric keys.(Microsoft SQL Server, Error:15259)
Please help.
Regards
Hassan
View 7 Replies
View Related
May 23, 2008
Does anyone know how windows authentication works in SQL Server 2005 with regard to group membership? If a user is a member of two NT groups that have the same permissions in SQL then which group will be used to authenticate their login? The reason I ask is that logins have default languages associated with them so if a user is a member of NT group A with a default language of British English and is also a member of group B with a default language of US English then which one will be used and will it be the same one every time?
View 4 Replies
View Related
Mar 26, 2008
Hallo there,
I just upgraded from Windows XP Pro to Windows Vista Bussiness and tried to reinstall SQL Server 2005 Developers Edition. After the installation i downloaded (using microsoft windows update) and installed all the service packs for sql and vista available.
My problem is when i open sql server management studio and try to connect to my default instance using windows authentication and database engine, an 18456 error occurs.
I enabled all the protocols and all the ports
I disabled windows firewall and antivirus (eset nod32)
I installed all service packs available
I have also installed Visual Studio 2005 without installing sqlexpress
But nothing happens!
Please i am very desperate, any information will be gratefully accepted.
This is my installation Information
Code Snippet
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.6001.18000
Microsoft .NET Framework 2.0.50727.1434
Operating System 6.0.6001
Thank you in advance,
Patonios
View 3 Replies
View Related
Apr 29, 2015
This morning I attempted to connect to my local copy of SQL Server (2012) (Win 7) using Windows Authentication. I was unable to do so and per the error log it was due to error 18456 (invalid credentials).
Yesterday I changed my windows password. Is it possible for this to be the cause?
FYI, following is from the error logs:
2015-04-27 10:10:32.98 Backup BACKUP DATABASE successfully processed 0 pages in 0.451 seconds (0.000 MB/sec).2015-04-28 00:00:41.90 spid18s This instance of SQL Server has been using a process ID of 2096 since 4/21/2015 9:08:05 AM (local) 4/21/2015 1:08:05 PM (UTC). This is an informational message only; no user action is required.2015-04-28 09:58:51.16 Logon Error: 18456, Severity: 14, State: 38.2015-04-28 09:58:51.16 Logon Login failed for user 'NT AUTHORITYSYSTEM'. Reason: Failed to open the explicitly specified database 'model'. [CLIENT: xx.xx.xxx.xxx]2015-04-28 17:09:59.87 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
The logs showed the error occurred yesterday (4/28) which is about when I changed my password. The 17:09 log (last one) is when I shut down my computer. Note that I was logged into SQL Server when I changed my Windows password and worked with it several times through out the day.
Also, per the configuration manager SQL Server (MSSQLSERVER) (and all other services) is stopped and I was unable to start it ("The request failed or the service did not respond in a timely fashion).
View 2 Replies
View Related
Jun 18, 2007
Hi
In our sql server we have around 40 windows group.
Say a Windows user = "X"
This X user does not have a direct windows login, he is present in one or more windows groups registered in the sql server.
I need to know throught which group he is logging in.
If I know this , this will help in my auditing process.
Thanks in advance.
Regards
Soorya
View 1 Replies
View Related
Jun 7, 2015
I am setting up security for access of database tables for members in a specific Windows User Group.I want to check in a SQL script if this Windows User group is added and if so, add database users and grant SELECT on specific tables.
I have tried this:
SELECT * FROM master.sys.syslogins WHERE name like '%FoeUsers' AND isntgroup = '1'
but that selects a SQL user or group and not a Windows Group.Is there a way to check if a Windows NT (active Directory) user group exists?
View 5 Replies
View Related
