How To Assign Permissions Properly- 3rd Parties
Jan 17, 2008
I have a question about assigning permissions for SQL Server 05's reporting services.
We have an outside party that needs access to run reports on our reporting services server.
However, I want to make sure they can't see anything else on our database server. (They need logins to connect to the server and then at that point in reporting services I only grant them access to a single folder that has the reports they need in the web interface).
I just want to be sure they can't poke around and see anything else in the server.
What would be the best way to handle that?
Just create their logins and grant them connect / login permissions only?
Let them fall into the PUBLIC role?
Should I create a new role just for them?
View 3 Replies
ADVERTISEMENT
Jan 15, 2008
A login named UserLogin1 has db_dataReader and db_dataWriter roles, and three schemas which are db_owner, dbUser1, and dbUser2 in a database named Database1. I wanted to have some tables for read-only, so I right clicked on the table and select properties. In the Permissions tab, I added UserLogin1 and checked Alter, Delete, Insert, Take Ownership, and Update under Deny. It worked for schema dbo only. I did the same steps for the tables that have schema dbUser1 or dbUser2, but UserLogin1 I added didn't stay. Why? How can I make those tables read-only?
Thanks.
DanYeung
View 3 Replies
View Related
May 6, 2014
Only to a specific schema? Can this be done?
View 5 Replies
View Related
Aug 2, 2006
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
View 5 Replies
View Related
Apr 23, 2001
how do i use "sp_attach_db" properly so that i can make a new database with only the .mdf file?
View 2 Replies
View Related
Jan 12, 2008
I have writen a small program in a Query window that runs fine if I hilight and run small chuncks. (I have listed the statements with out the clauses so it is more easly viewed here.)
The problem is if I simply execute the Query window I get massive errors that don’t make sence. I am guessing I need some GO and BEGIN/END statements? But I don’t know where I should use them.
I would really appreciate a few pointers so I can just run the Query window.
drop table SourceFile
drop table ReferenceFile
SELECT TOP
INTO SourceFile
FROM
SELECT TOP
INTO ReferenceFile
FROM
-- test data
INSERT ReferenceFile
(AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip)
values ()
INSERT SourceFile
(AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip)
values (22)
-- test data
drop table MATCHTEST
SELECT TOP 1 AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip
INTO MATCHTEST
FROM AddressCleaned_npidata_20050523_20071112
DELETE MATCHTEST
ALTER TABLE MATCHTEST
ADD REF_ML VARCHAR(2)
INSERT MATCHTEST ()
SELECT
FROM ReferenceFile r
inner join SourceFile s
on s.New_Last_Name = r.New_Last_Name
select * from MATCHTEST
View 3 Replies
View Related
Sep 11, 2007
Hi,
How do i deploy IS package properly? can i just copy the .dtsx and assign that in an sql jobs?
thanks a lot
cherriesh
View 3 Replies
View Related
Jun 30, 2006
Hi,
I'm very new in SSIS. I've created 3 packages in the project. sometimes when i modified the project and save/save-all it, when i tried to build (isn't this used to deploy?), I am being asked by this:
Package 1 has been modified outside the source editor. Do you want to reload it?
When I press on yes, all my modifications were not save. If i answered no, the build process stops. i dunno if this is because the build process is already finished or it was terminated because i chose 'NO'. When i tried to rebuild it again, it will ask me the same question.
What is the proper way to save and build the project? When it says ' do you want to reload it?', does it mean reloading the old copy before modification?
Thanks!
cherrie
View 3 Replies
View Related
Jan 17, 2007
Hello, i have something like this, i want the annoucements (status = 0) to be on top, then topics with (status = 1) below, then the rest of the topics.
So i tried:
SELECT forum_topics.id, forum_topics.status, forum_topics.username AS starter, forum_topics.subject, forum_topics.closed, forum_topics.answerpostid, forum_topics.views, forum_topics.answers, forum_topics.lastanswer, forum_topics.lastanswerid, forum_topics.created AS started, forum_answer.username, forum_answer.answer, forum_answer.created FROM forum_topics LEFT OUTER JOIN forum_answer ON forum_answer.id = forum_topics.lastanswerid WHERE (boardid = @ID OR boardid = 0) ORDER BY (status) ASC, (created) ASC
Problem is that they are not sorted diffrently, when i change the (created) ASC to (created) DESC i get the same result and the rows are not sorted, they only get sorted by status so i have status=0 at the top, then status=1 then the rest. How do i get them to be sorted first by status ASC then by created ASC/DESC?
Patrick
View 1 Replies
View Related
May 22, 2002
I installed MDAC 2.6 SP1 on my 2000 machine in order to get SQL server 7.0 to work properly on my machine. I'm trying to get installed on server which is on NT 4.0 SP6 so that when I save a DTS package on my 2000 machine and run it on the server it doesn't get the error client access ODBC driver not capable. I intalled MDAC 2.6 SP1 on the server but it doesn't look like its updating it. The install goes fine. I reboot but when I go into the ODBC connections and look at the driver for the client acceess it still has version 6.00.00.05 while my 2000 machine says 6.00.07.00
Any ideas on why its not updating. Can I just take the DWBODBC.DLL from my machine and copy it to the server?
View 1 Replies
View Related
Sep 28, 1999
We have SQL Server 7.0 running on an NT 4.0 sp5 box. Whenever we stop the SQL Server Service, we get the message that SQL Agent will also be stopped. If we do this, all the services seem to stop fine, but when we try to start the SQL Server Agent Service back up, it won't let us. In order for it to restart, we have to reboot the server so that it starts 'automatically'. Does anyone have any ideas as to why this is?
View 1 Replies
View Related
Jun 19, 2001
I define one of my columns as FLOAT. Some of the values are negative and positive with precision 8 or 12. When I run updates against that column and then check the data all values are 0.
Any idea why?? I appreciate it.
Thanks,
David
View 1 Replies
View Related
May 3, 2004
I have the following trigger that will insert the correctly if none of the items in the #tempKCS exist in the destination table. However if one of the items in the #tempKCS table do exist in the Sales table then the insert does not execute for the other itmes in the #temoKCS list that are not already listed in the Sales Table. Below is the trigger. Tell me where I am making my mistake. Thanks
CREATE TRIGGER [Insert_KCSales] on [dbo].[Reclines]
FOR INSERT, UPDATE
AS
SELECT KitItemSum.Date, Kits.KitItemNo AS ItemNo, (Kits.Quantity * KitItemSum.QtySold) As QtySold, ((Kits.[Percentage]/100) * KitItemSum.AmtSold) AS AmtSold, KitItemSum.Division, 0 AS QtyReturned, 0 AS AmtReturned Into #TempKCS
FROM (SELECT Summary.Recdate AS [Date], Summary.ItemNo, Sum(Summary.Qty) AS QtySold, SUM((Summary.Amount-Summary.DiscAmt)) AS AmtSold, Summary.Division
FROM (SELECT DISTINCT R.Recno, R.Recdate,I.RecLineNo, I.ItemNo, I.Qty, I.Amount, I.DiscAmt, I.Division FROM Inserted I
INNER JOIN Kits K ON I.ItemNo = K.MasterItemNo INNER JOIN Receipt R ON I.RecNo = R.RecNo INNER JOIN Items ON I.ItemNo = Items.ItemNo) AS Summary
GROUP BY Summary.RecDate, Summary.ItemNo, Summary.Division) AS KitItemSum INNER JOIN Kits ON KitItemSum.ItemNo = Kits.MasterItemNo;
INSERT INTO Sales ([Date], ItemNo, Division, QtySold, AmtSold, Qtyreturned, AmtReturned)
SELECT [Date], ItemNo, Division, QtySold, Amtsold, Qtyreturned, Amtreturned
FROM #TempKCS
WHERE NOT EXISTS (SELECT S.[Date], S.[ItemNo] FROM Sales S INNER JOIN #TempKCS KCS on S.ItemNo = KCS.ItemNo AND S.[Date] = KCS.[Date]);
View 1 Replies
View Related
Apr 21, 2008
Hello friends, I have developed an ASP.NET 2.0 application. The problem is that the IIS server returns the next error:
SQL Server does not allow remote connections
Sometimes returns:
SQL Server does not exists.
My question is if there is any known bug with ASP.NET and SQL Server. My sql server have activated the Remote Connections (its working properly on other web page).
Its a problem of the number of connections??
Its a problem ofbad configuration??
Its a IIS problem??
Please bring me some help.
View 8 Replies
View Related
Jul 18, 2007
Hi,
We installed SQL Server 2005 on windows 2000 with IIS 5. After installation, I can see report manager using http://localhost/reports. But I can only the top part. There's no My Reports or anything, just "Home / My subscriptions / Help".
Is there anything wrong and how to fix it?
Any help would be appreciated.
Thanks
View 5 Replies
View Related
Mar 11, 2008
Hi i given my query here. in that joings betweens tables is correct, but i am getting the following error
ora-00933 sql command not properly ended
can we have exists function inside if condition??
select distinct loc.authorization from account acct, location loc, iom_order iom
where loc.location_id = acct.location_id and acct.account_id = iom.account_id and iom.iom_order_id =140
if(loc.authorization is null and exists(
select distinct line.INTRALATA_PIC_FREEZE ,line.INTERLATA_PIC_FREEZE
from line line, iom_order iom, activity act
where ((line.INTRALATA_PIC_FREEZE='F' or line.INTERLATA_PIC_FREEZE='F')and line.line_id = act.component_id and act.component = 23 and act.iom_order_id = 140)
union all
select distinct trunk.INTRALATA_PIC_FREEZE ,trunk.INTERLATA_PIC_FREEZE
from trunk trunk, activity act
where ( (trunk.INTRALATA_PIC_FREEZE='F' or trunk.INTERLATA_PIC_FREEZE='F')andtrunk.trunk_id = act.component_id and act.component = 25 and act.iom_order_id = 140)
select distinct rcf.INTRALATA_PIC_FREEZE ,rcf.INTERLATA_PIC_FREEZE
from remote_call_fwd rcf, activity act
where((rcf.INTRALATA_PIC_FREEZE='F' or rcf.INTERLATA_PIC_FREEZE='F')and rcf.remote_call_fwd_id = act.component_id and act.component = 24 and act.iom_order_id = 140)
select distinct tsg.INTRALATA_PIC_FREEZE ,tsg.INTERLATA_PIC_FREEZE
from nodal_tsg tsg, activity act
where ((tsg..INTRALATA_PIC_FREEZE='F' or tsg.INTERLATA_PIC_FREEZE='F')and tsg.nodal_tsg_id = act.component_id and act.component = 5 and act.iom_order_id = 140))) Then
select distinct loc.authorization from order2misc o2m, location loc, iom_order iom where loc.location_id =
o2m.serv_loc_id and iom.iom_order_id =140
else
select distinct loc.authorization from account acct, location loc, iom_order iom
where loc.location_id = acct.location_id and acct.account_id = iom.account_id and iom.iom_order_id =140
end if;
View 1 Replies
View Related
Jul 20, 2005
Hi,Currently, I am on SQL2000 SP3 and Windows 2003.I am trying to restore the full database backup and transaction logbackup on the development server. The full database restore is not aproblem, but the transaction log restore happened during the day is.Everytime when I try to compare the transaction in the database(select the last update date from one of my table as a benchmark, thenbackup the transaction log for restore) to the development db with therestored transaction log, I got none of the transaction from the logbackup.What did I do wrong? Is my concept about using the transaction logrestore wrong?Thanks in advance.
View 4 Replies
View Related
Jul 20, 2005
I have about 7 tables I need to join, but am having a lot ofdifficulty with the joins, that I need some help on.I'll provide the details of four tables to illustrate the scenario.I have one table called Product, which contains a complete list ofproducts.I have another table called Date, which contains a complete list ofdates.I have a table called sales, which contains Product, date and Qty.I have a table called Purchases which contains product, date and qty.I want to link my Purchases and sales tables to my Product and datetables.On each row I want product from the Product table, Date from the datetable, Sales Qty from the sales table and Purchases Qty from thePurchases table.I've been messing around with full outer, inner joins etc, but can'tget it right.All help greatfull.Regards,Ciarán
View 5 Replies
View Related
Aug 31, 2007
Hi,
I have 2 servers running in a clustered mode(active/active). In which we installed SP2 on peak production time but somehow it resulted in failure i.e it installed partially and sql server is unstable as of now. I'd like to know if it would be fine to restart the server without full installation of SP2 and what could be the implications of a server restart in such a scenario.
Pls help urgent!!!!!!!
View 1 Replies
View Related
Dec 25, 2007
Hi,
I am exporting a table to a fixed width flat file using "Ragged Right" format. Though i specify carriage return for the last column, the text file is not taking it up.
I am doing the following for connection
cmFlatFile.properties("Format").SetValue(cmFlatFile,"RaggedRight")
cmFlatFile.properties("RowDelimiter").SetValue(cmFlatFile,vbCrLf)
and while adding the column i am setting up the width as follows
vColumns = flatFileDestination.InputCollection(0).GetVirutalInput().VirtualInputColumnCollection
For cols = 0 to vColumns.Count -1
Dim col As IDTSConnectionManagerFlatFileColumn90 = ff.Columns.Add()
if cols = vColumns.Count - 1 Then
col.ColumnDelimiter = vbCrLf
end if
cols.ColumnType = "FixedWidth"
cols.Columnwidth = 20
......
Next cols
Still the rows are exported next to each other just like when we use FixedWidth.
View 4 Replies
View Related
Jul 17, 2007
Hi,
I've my SSIS Packages, having multiple Configuration stored in SQL Server Table named [SSIS Configurations].
And it's devloped on BHUDEV Server ON Devlopmentdb Database. So durring development Default Cofigurations are set for BHUDEV Server.
Problem is that when I install my Package on SQL Server on different Server for example JOHN, and now my database is ProductionDB and [SSIS Configurations] table will be on ProductionDB database. And I've made required changes in [SSIS Configurations] table. So when I run the Package even then it picks configuration information from BHUDEV.Developmentdb.dbo.[SSIS Configurations] table. That's my problem.
But If when during execution time, I change the connection information then it works fine.
Please Help!
Bhudev
View 6 Replies
View Related
Sep 5, 2007
Hi,
Long story, Ihad SQL 2000 happily installed and running for a long time. Attempted to load SQL 2005 alongside, but in retrospect see that I used the same install directory as SQL 2000. Found that the SQL 2000 databases still ran, in that I was able to run applications that rely on them. Enterprise manager ans Query Analyzer are still in the start/program list, but when I try to open them they say that the DSN isn't there, and the odbc driver is missing. Also if I try to configure the system SQL DSN in ODBC datasources I get the same message. I guess the SQL 2005 install has scrambled the ODBC setup.Tried deinstalling SQL 2005, but the problem persists. Tried reloading mdac-typ, same problem. Tried reinstalling SQL2000, same problem. Tried fully deinstalling SQL2000 from Add/remove program, said it completed, but the shortcuts are still there, the applications that use the db's still work, only it no longer appears in Add/Remove programs.
Is there a command line string to manually remove it, or where do I go to repair my mdac, or is this what I actually want to do?
Thanks in advance
Hugh Esler
View 5 Replies
View Related
Aug 24, 2007
Hi,
I am using a sub-report in my main report, passing a customerid parameter into the subreport. The subreport dataset then either returns rows, or it doesn't.
When the dataset returns rows, I am using either COUNT(field) or CountRows(dataset) in a textbox, which then displays fine.
However, if the dataset returns NO ROWS, i need the textbox to return a '0' - which it does if i run the subreport on it's own.
If i run the main report, and the subreport returns no rows, no matter what expressions I use to try and get the '0' to display on the main report....it doesn't.
As a test I also put a background colour on the text control in the subreport, and it looks like the whole text control is not displaying
This is only an issue when 0 rows are returned, but i must display a 0 in this case!?!
Help!
Cheers
Chris
View 8 Replies
View Related
Oct 2, 2007
Hi,
I have several packages where I set up a SQL Server log provider. The packages are all called from jobs.
However, for some reason, only ONE of the packages is logging to the sysdtslog90 table in my specified database.
All of packages have the identical set up for the logging. Here are my settings under logging:
PROVIDERS AND LOGS:
Name: SSIS Log Provider for SQL Server (box is checked)
Configuration: ServerName.DBName (I use this same connection for all the packages)
DETAILS:
OnError is checked
CONTAINERS:
All items are checked
So what's going on here? I have successfully logged to this table using test cases in BIDS, and as I mentioned, ONE of the packages is actually logging to the table when it's called from the job.
Thanks
View 5 Replies
View Related
Feb 12, 2007
I am trying to sort by a date and then use a secondary sort on another column date field. The first date sorts properly, but the second is not in sorted order. I have also tried ORDER BY first date, second date and that queries the information in sorted by the first date, but then not sorted by the second date. Any ideas?
BJ
View 1 Replies
View Related
Nov 17, 2006
Hello everyone,
i have the parameter in my stored procedure that i am using as a sqldatasource.
Now in one of the events, i need to assign a value to the parameter. How can i do that?
Microsoft is changing the syntax so often, all solutions i found on this forum just don't work anymore, like:
SqlDataSource1.SelectParameters["@CompareInteger"].value= "1"
OR
SqlDataSource1.SelectParameters["@CompareInteger"].DefaultValue= "1"
I guess the SelectParameter - became 'ReadOnly'..But how to assign value to a parameter now?!?
Thanks for any help
View 5 Replies
View Related
Nov 23, 2006
I use SQL Server 2005 and in a Stored Procedure I want to execute a sql statement and assign the result to a variable. How can I do that?The name of the column I want to retreive the value from is "UserID"Here's my SP so far:
ALTER PROCEDURE [dbo].[spUnregisterUser]
@UserCode int
AS
BEGIN
SET NOCOUNT ON;
declare @uid uniqueidentifier
--get userid
SELECT @uid=UserID FROM tblUserData WHERE UserCode=@UserCode
-- Delete user
UPDATE tblUserData SET IsDeleted='True' WHERE UserCode=@UserCode
END
View 1 Replies
View Related
Nov 11, 2000
Hi
How can I assigne a value that return from a stored procedure into a variable?
Example:
if I execute this:
declare @szquery as nvarchar(256)
declare @icount as int
--I must pass a string query because the name of the field and the table are ---the variable that i passed
set @szquery='Select count(reckey) as dummy from table1 '
exec sp_executesql @szquery
the result is:
dummy
-----------
15770
(1 row(s) affected)
declare @b as int
I want assigned 'dummy' into a variable
set @icount= ???
if I ecexute this:
print @icount
the result :
15770
please help me out in this issue and i'd appreciate that.
tanks
Emiliano
View 1 Replies
View Related
May 23, 2002
hi, can somebody help me to solve this problem?
first, declare 2 variables
declare @num int
declare @str varchar(255)
then set the varchar variable to '100/10'
set @str = '100/10'
is there anyway to assign a value to @num using @str, so that @num has a value 10??
set @num = @str ????????
View 1 Replies
View Related
Oct 6, 2015
How to make it workable code..
declare @i int=1
declare @numweek int=2
declare @a int=35
declare @b int=29
declare @Wkstr1 date,@Wkstr2 date
[Code] .....
View 7 Replies
View Related
Apr 22, 2008
I have this query and I tried this but am getting the error for the case statement when I assign the nulls to a 0:
The error is they data types of the result expression of a CASE expression are not compatible.
SELECT
'C' as account,
FUND_dim.fund_cde as FUND,
case when sum(BRKGRPTT.daily_brkg_fact.accum_unit_cnt) is null then '0' else sum(BRKGRPTT.daily_brkg_fact.accum_unit_cnt) end as Units_Purchased
FROMFUND_DIM
left outer join BRKGRPTT.daily_brkg_fact
on FUND_DIM.FUND_ID_NUM = BRKGRPTT.daily_brkg_fact.FUND_ID_NUM
and BRKGRPTT.daily_brkg_fact.SEP_ACCT_ID_NUM <> 1
left outer join SEP_ACCOUNT_DIM
on BRKGRPTT.daily_brkg_fact.sep_acct_id_num = SEP_ACCOUNT_DIM.sep_acct_id_num
group by BRKGRPTT.sep_account_dim.sep_acct_cde, BRKGRPTT.fund_dim.fund_cde
order by FUND_dim.fund_cde
View 4 Replies
View Related
Jul 12, 2006
How do I assign XmlDocument results to a variable (so that I can pass it to a sp)?
I know that the following code works....
select * from tblUsers where userId = 1225 for XML raw
It returns "<row UserId="1225" LastName="Evans" FirstName="Stephanie" MiddleInitial=...."), which is what I want. But when I try to assign it, I get an error "Incorrect syntax near 'XML'."
declare @strXml nvarchar(1000)
set @strXml = (select * from tblUsers where userId = 1225 for XML raw)
Ideas?
View 1 Replies
View Related
Oct 20, 2006
I'm developing a vb 2005 application and I€™m creating the users directly to the database. I want to assign them names.
I want to do something like this:
CREATE TABLE admin.db_users (
id INT CONSTRAINT db_user_pk PRIMARY KEY,
[name] VARCHAR(50) CONSTRAINT db_user_name_nn NOT NULL,
authentication VARCHAR(25) CONSTRAINT db_user_authentication_nn NOT NULL,
CONSTRAINT db_user_fk FOREIGN KEY(id)
REFERENCES sys.database_principals (principal_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
GO
This is the error that i'm getting:
Msg 1767, Level 16, State 0, Line 1
Foreign key 'db_user_fk' references invalid table 'sys.database_principals'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
How do I solve this problem or how can I do something similar.
View 5 Replies
View Related
