Hi All,
Is there a way in SQL Server 7.0 or 2000, where I can grant/deny/revoke access rights on a database objects like Table, Stored Procedures for a particular time of the day.
Example: I want to prevent user A from acessing Table x and Stored Procedure Y from 9Am to 12 noon everyday. After 12 Noon till 8.59 AM he can have access to Table x and Stored Procedure Y.
Is there a way to do this at SQL Server level.
Thanks
Sri
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
hi, I am having a database in sql server 7.0. it has a web front end database. how can I grant access to the tables. do I create a guest logins in the security folder, then in the database user tab, I give access as read,write. Or there is another way to do it.
Thanks
Ali
I remember seeing a document on this site a couple of years ago that explained reasons why a DBA needs sa access rights. I can go into BOL and generate a list of things you can only do with sa rights. However the article I am looking for was well written, much better than I could do.
My infrastructure team has decided that the DBA's and Sr. Developers will not have sa access rights. All schema changes, stored proc creation, view creation, database backups, maintenance plans, etc will go through their server engineers. They do not understand what they are getting into.
Does anyone have a nice document that would aid me in my efforts to convince the Infrastructure group to change their "new" policy?
Thanks for any info!!
Jeff
Hello,
I want to restrict the database not to be accessed from anywhere except
my webservice...I mean, my client applications or anyone else can not
be able to access the database...
How can I do this?
Thanks very much...
I am having trouble openning a connection to a sql server database that resides on another machine. When the web server and SQL server run on the same machine, everything works fine. When the web server and sql server are located on different machines, I get an access rights error when i try to open the connection. I suspect that this involves trust levels, but all the tweaking I have done has not resolved this issue. Any help would be much appreciated!
View 1 Replies View RelatedGuys,
What are roles and access rights I need to assign my backup operator so that he can see Maintainence plans under Management node of SQL Server Management Studio. I do not want to assign any admin related privileges.
Thanks
I'm attempting to grant rights to Report Builder as deployed as part of my TFS install. My problem is that I have to add my users to the builtinadmin group in order for them to see the report builder button on the SQL Server Reporting homepage & then have rights to launch the ReportBuilder click once app. I obviously do not want make users admins on the box, but I've tried adding them to all of the other groups having to do with SQLServer to no avail. How can I grant users access to launch the Report Builder app?
View 6 Replies View RelatedI dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).
Detailed description of problem:
So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.
The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.
Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.
Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).
The access rights for A1 enduser cannot be set by no means i know because:
1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.
2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)
3) I cannot set the rights via application role because two application roles cannot be set concurrently.
4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).
Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.
I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a database
But, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures
I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working
Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.
The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\localhost
eports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:
Title-Reporting services configuration manager
Error-There was an error refreshing the UI. bla bla bla
A WMI error has occurred and no additional error information is availiable
Title-Reporting services configuration manager
Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla
A WMI error has occurred and no additional error information is availiable
then when he's in sql server 2005 surface area configuation
Title-Surface Area Configuration
Error-Access denied (system.management)
Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS???
Please help!!
Thanks for any time anyone has taken to review this thread!!
Hi!
Need some help building a query that does the following :
I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)
In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]
Both Time Dimensions are Year -> Month -> Day and have the same Members.
I have every option available, using calculated Members and/or Measures to do this.
The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.
Appreciate all help!
Regards,
Stian Bakke
can i make a trigger to fire based on given date and time?
View 2 Replies View RelatedWhile using a DateTime field, is there an easy way of filtering based on time of day? Ex. Anything that happened after 3:00 PM on any given day?
The easiest way I've found of doing so is :
Code:
SELECT *
FROM MyTable
WHERE ({ fn MOD(DATEDIFF(ss, '01/01/2000 00:00:00', [Date]), 3600 * 24) } >= DATEDIFF(ss, '00:00:00', '15:00:00'))
To me that seems a bit complicated... but it works...
Anyone that knows of a better way any help would be appreciated (or if you don't think there's a better way knowing that would help as well)
-MBirchmeier
Hello
I want to delete my record after ten days of the Posted date(field)..how do I do it? I'm able to insert date(short date , long time) to the database. I'm using sql server and C#. this is what I have so far.I would appreciate your help..
Thanks!!!<%@ Page Language="C#"%>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat=server>
void Page_Load(Object sender , EventArgs e)
{
SqlConnection conPubs;
string strDelete;
SqlCommand cmdDelete;
conPubs = new SqlConnection( @"Server=localhost;Integrated Security=SSPI;database=Book" );
strDelete = "Delete tblbook Where Posted_Date =???????????";
cmdDelete = new SqlCommand( strDelete, conPubs );
conPubs.Open();
cmdDelete.ExecuteNonQuery();
conPubs.Close();
Response.Write("Records Deleted!");
}
</script>
//
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<!-- Insert content here -->
</form>
</body>
</html>
Right now i'm developing a time attendance system.
So in that case i'll be playing with time where that time represents what time an employee come to office and go home.
I'm a little bit confuse which data type i'll be used in this system.
Because there will be a lot of calculation according time data inside the database, such as: how many times an employee comes before 08.00, how much hours that he/she spend in the office that they, etc.
Can somebody help me with this problem, what data type most suitable with this kind of system and how can I do the calculation more easily?
thanx before,
In SQL Server 2005, I want to do a set query on the following data that results in 3 groups:
Id
EventName
EventTime
1
First
41:40.2
2
First
41:41.6
3
First
41:43.1
4
First
41:44.4
5
Second
41:46.4
6
Second
41:48.3
7
Second
41:49.7
8
First
41:51.2
9
First
41:53.3
10
First
41:55.0
So, I want to have a query that returns one aggregate row for each of rows 1-4, 5-7 and 8-10 based on the EventName. Every time EventName 'changes' in the order that I sort it, I want to start a new grouping:
Group
EventName
Count
1
First
4
2
Second
3
3
First
3
With this query, I could also get the Min() and Max() EventTime for each group, etc.
However, this is proving difficult to do in set SQL. Obviously, if I group on EventName, then rows 1-4 *and* 8-10 will be rolled into my 'First' group. However, there is no other partitioning information that I can factor in that splits this data into *only* 3 groups, based on the order of the Event Time.
I have tried the various ranking functions, but the problem persists through any combination of function, PARTITION BY and ORDER BY that I can find.
Any insights would be appreciated!
Hello~,
The table has columns like this.
________________________________
time smalldatetime
value1 int
value2 int
----------------------------------------------------------
for example,
....
'2006-11-16 12:00:00',100,200
'2006-11-16 13:00:00',110,210
'2006-11-16 14:00:00',120,220
....
The record is inserted at every hour.
I want get daily,monthly,yearly average and display the result ordered by time.
A table gets data every 4 minutes, I only need spread of every 15 minutes. Can I select only records spread every 15 minutes apart from this table without having to run a scheduled job every 15 minutes and loading one record closest to getdate() at that point into another table(this is how I am doing it now) Is there a better way. Please help
Thanks
I have a number of databases with large tables. I need to update them from time to time. I want to get the recordcount of the table and calculate based on that the amount of time it would take to update the table. Any idea who I can do this? I'm using coldfusion 8 with sql to do this. Any advice would be appreciate!
Thanks
Shuvi
Hi,
I was wondering how you perform a select statement based on a specific date that will show all the records no matter which times belong to the specific date.
I have been having trouble with this one when using a single date, I think this is because of the time property as no records are displayed.
Thanks for any help.
I have a table Mytable which has two fields (Date, Value) and record is based on every hour like this:
Date Value
1999-01-01 00:00:00 10
1999-01-01 01:00:00 8
1999-01-01 02:00:00 6
...
1999-01-01 23:00:00 12
1999-01-02 00:00:00 9
1999-01-02 01:00:00 5
...
1999-01-02 23:00:00 2
How can I use a query to get accumulate value for the day? the result should look like this:
Date Value Accumulated_Value
1999-01-01 00:00:00 10 10
1999-01-01 01:00:00 8 18
1999-01-01 02:00:00 6 24...
1999-01-01 23:00:00 12 36
1999-01-02 00:00:00 9 9
1999-01-02 01:00:00 5 14...
1999-01-02 23:00:00 2 16
The accumulated value should be based on every hour for the day and then repeat for the second day and so on.
What is the best way to do this? I am using SQL 2008
Hello all,
I am using SQL Server in a project where I want to fetch the records that were inserted after a time specified in my query.
Suppose 10 records were inserted at 10:00 AM(morning) and 5 were inserted at 10:15 AM( 15 minutes later). I am running a query at 10:20 AM( 5 minutes after the second transaction). I need this query to be such that it selects the records inserted 10 minutes before. So it will show only the records inserted at and after 10:10 AM and willl not show those inserted at 10:00 AM.
Please help me in making such a query.
I am trying and I think that some Date & Time functions will help but still not able to achieve it.
Thanks in advance
Hi
I have an VB.NET application connected to a SQL Server Express. I want to let the application to run in either "Normal mode" or "Holiday mode" according to current weekday is normal day or state public holiday.
My approach is to find out all the public holidays in a year and enter them into a Holiday Table. Then some code in my application constantly check the current weekday against the one in the holiday table, if matches, the application goes into holiday mode.
This approach is not perfect as "State public holidays" are confirmed by the state government in the current year and the coming year. So state public holidays are unconfirmed for the third year afterward. The system is required by client to support public holiday in the next 10 years.
I wonder what is the best approach to this problem?
Thanks
Hi all,
I have two tables (staging and Cdate) and neither objects has any constraints.
staging table has ID, date, A, B, and C fields and Cdate has id,date and day fields. I need to update/insert date from Vdate into staging where staging ID=' ' and date is null
Here is the code I wrote, however, it seemed the information was updated to one date only instead of time series - Cdate contains time series in column date.
Anyone can help to fix it? Thank you for the help!
update s
set s.date=c.date
FROM cdate c join staging s on(s.id=c.id)
Where s.date is null and id=2
I'm dealing with a problem.
The record information example
DateTimeStart , DateTimeEnd , action , duration (seconds)
2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600
what i want is for every half hour between start and end a record
10.30 action1
11.00 action1
11.30 action1
how can i create this, i'm a little stuck on this
Frank writes "Any suggestion on the best design to store data for resource management so that data can be displayed afterwards in something like a gantt chart.
For example, you have to asign resources, say personID 1 to 100 to fill positions A, B, C, D, E and F
A position must always be filled. A person can only fill one position at a time. You want to be able to detect overlaps when you do your planning.
So if person 2 is in position B from 1 Jan to 15 March, he is currently unavailable, but he should be availabe in my planning for any position from 16 March onwards etc.
Possible queries -
1) list all positions not filled during period XXXX to YYYY
2) List all persons available to fill a certain postion during period XXXX to YYYY
3) List any overlaps where a person is assigned to different postions during overlapping time frames..."
Hi,
I have the following scenario :
CustomerDetail
customerid
customername
status
app_no
[status = 0 means customer virtually deleted]
CustomerArchive
archiveno [autoincrement]
customerid
customername
status
At the end of the month, I have to physically delete customers. I have written two stored procs:
proc1
create proc spoc_startdeletion
as
declare @app_no int
select @app_no = (select app_no from customerdetail where status=0)
EXEC spoc_insertcustomerarchive @app_no
-- After transferrin, physically delete
delete from customerdetail where status=0
proc2
create proc spoc_insertcustomerarchive
@app_no int
as
insert into customerarchive(customerid,customername,status)
select customerid,customername,status from customerdetail where app_no = @app_no
It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value'
How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred.
Vidkshi
I am trying to write a stored procedure that will select information from a SQL table based on a specific time.
For example I have a name field and a time field, I want to return just the names that were created between a specific time frame. ex between 3pm and 4pm.
Any thoughts?
To give you some context we have a new amendments application (nothing fancy, excel based with SQL Server back end) that allows users to submit amendments to product data (Product Info, PO Prices, Dates etc.). There is also an admin tool that a team uses to action these amendments in the various systems.
The old version of this tool, users submitted amendments by style and could if need be submit multiple amendments against one product at the same time. The new tool, I believe for audit reasons, users submit by amendment type, so for example I would submit a cost price change for a given style.
The issue now is that on the occasions where a user has multiple amendments, they now come through separately. So cost price would be Amendment 1 and a date change would be amendment 2 even though they could be the same product. This could potentially mean that the admin team would be duplicating work if the paperwork is updated and sent after each amendment, whereas before they would make both changes and only send the paperwork once.
Having not built either of these tools, I've been tasked with trying to fix this, my two thoughts being either to amend the user form to somehow capture/ allow users to submit amendments together or try to use the existing data and doing the grouping dynamically in the back end. Use that lag to look at grouping any submitted amendments that occur within 30mins of the first occurrence of that style
This grouping would then be given a joint time so when the 'time lag' period passes the amendments will be visible together.I've tried a few things and a few head on desk moments trying to get a set based approach but haven't been able to get where i want, its either an issue where amendments span an hour, such as 9:59 and then 10:03 or grouping together amendments that happen after the 30mins of the first one.
Here is some sample data
USE FF_Winning_Together;
IF OBJECT_ID(N'tempdb..#AmendTest',N'U') IS NOT NULL
DROP TABLE #AmendTest;
CREATE TABLE #AmendTest
(
AmendmentIDINT IDENTITY(1,1)NOT NULL,
StyleCHAR(1)NOT NULL,
AmendmentStatusVARCHAR(10)NOT NULL,
DTDATETIMENOT NULL
[code]....
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.
i have a table of phonecall records detailing amongst other things the time and duration of the call. there is a relationship linking this table to a table of handsets and from there to a table of users. this users table lists the start date the user had the phone. as ever sample data might make this easier to show:
PhoneUsers table
PhoneUserID Name StartDate PhoneID
1 john 1/08/2006 1
2 bob 20/08/2006 1
3 fred 2/08/2006 2
etc
Phones table:
PhoneID
1
2
etc
PhoneCalls table:
PhoneCallID PhoneID CallDate Duration
1 1 10/08/2006 25
2 1 23/082006 20
3 2 23/08/2006 20
i want the following result set:
user calldate duration
john 10/08/2006 25
bob 23/08/2006 20
fred 23/08/2006 20
essentially i am trying to link the phonecalls to the user of the phone at the time of the call. i make the following assumptions regarding the data:
the user is responsible for all calls for a given handset from the 'startdate' until the next 'startdate' for a different user, if such a record exists, if not then for all calls after that date
thanks
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00 69
b6e4 2015-03-18 13:00 2015-03-20 04:00 39
i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-15 24:00 22
a3er 2015-03-16 00:00 2015-03-15 24:00 24
a3er 2015-03-17 00:00 2015-03-15 23:00 23
[code]...