Convert

I am using the following snippet of code to help me convert the date and tim
e
in a query I am writing.
SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
dbo.Subjects.Subject, Convert
(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
The date converts fine to the format that I need. The time does not. It is
being displayed as military times and I want it to appear as a standard 12
format. i.e 9:00 AM
I tried all of the codes I found in BOL and none gave me what I wanted. Is
it possible to do what I want to do?
ThanksI think you can use the following:
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
HTH
Barry|||Thanks. But it still shows up as military time when I use 100.
"Barry" wrote:

> I think you can use the following:
> Convert
> (Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
> HTH
> Barry
>|||Umm not sure why - I have just checked the BOL and it confirms my
suggestion in the CAST and CONVERT section.
What are storing the Time as? Datetime?
Barry|||right(convert(varchar,dbo.TrainingSchedule.RequestedTime,100),7) as Time
Brennan wrote:

>I am using the following snippet of code to help me convert the date and ti
me
>in a query I am writing.
>SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
>dbo.Subjects.Subject, Convert
>(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
> Convert
>(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
>The date converts fine to the format that I need. The time does not. It i
s
>being displayed as military times and I want it to appear as a standard 12
>format. i.e 9:00 AM
>I tried all of the codes I found in BOL and none gave me what I wanted. Is
>it possible to do what I want to do?
>Thanks
>|||Usually, formatting is best left to the client since most client languages
have far better capabilities in this area. It isn't particularly clear what
datatypes you are using for the columns in question - the assumption is that
they are both datetime (or smalldatetime). If this assumption is not valid,
then you should clarify what the datatypes are and the expected formats of
the data (if applicable). One can question the wisdom of separating these
two intimately related bits of information into two separate columns -
especially given the dbms support.
If you must persist in this quest, you will most likely need to "generate"
the appropriate information in some convoluted and complex expression (and
possibly multiple queries). For the convert function, none of the available
formats has a space between the time and the AM/PM characters. If this can
be ignored, the 100 format is the closest - convert to this format and take
the last 7 characters (or all the characters from the last space to the end
of the string). You could also use the datepart functions to strip off and
convert the bits that are of interest. Experiment a bit - I think you will
understand the reason for the my first statement.|||Can you post a repro? Is the datatype really datetime? Also, I agree that fo
rmatting is best
performed in the client application.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:753F4CC7-DC7F-496E-8834-51667F2BFD0B@.microsoft.com...
> Thanks. But it still shows up as military time when I use 100.
> "Barry" wrote:
>|||Thanks I agree with you about the client. I am using smalldatetime.
My problem is that my client is a DNN portal. I am using an add in module
that let's me dynamically display the results of an SQL statement in a grid
on any selected page.
Unfortunately, it does not give me the opportunity to adjust any formatting
which was why I was trying to approach it from a Convert perspective. And I
know nothing about asp so I can't approach the problem from the client side.
I'll try some of the solutions mentions here, but I think I'm going to end u
p
writing an RS report to provide this information to our end users.
Thanks
"Scott Morris" wrote:

> Usually, formatting is best left to the client since most client languages
> have far better capabilities in this area. It isn't particularly clear wh
at
> datatypes you are using for the columns in question - the assumption is th
at
> they are both datetime (or smalldatetime). If this assumption is not vali
d,
> then you should clarify what the datatypes are and the expected formats of
> the data (if applicable). One can question the wisdom of separating these
> two intimately related bits of information into two separate columns -
> especially given the dbms support.
> If you must persist in this quest, you will most likely need to "generate"
> the appropriate information in some convoluted and complex expression (and
> possibly multiple queries). For the convert function, none of the availab
le
> formats has a space between the time and the AM/PM characters. If this ca
n
> be ignored, the 100 format is the closest - convert to this format and tak
e
> the last 7 characters (or all the characters from the last space to the en
d
> of the string). You could also use the datepart functions to strip off an
d
> convert the bits that are of interest. Experiment a bit - I think you wil
l
> understand the reason for the my first statement.
>
>

conversion from CHAR to DATETIME error

on a column DateNew = DateTime

i am trying :
INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')

and i get an error :
conversion of char data type to datetime data type resulted in an out of range datetime value

I had never this error before , do you know why ?
i must enter a yyyy/mm/dd format because this database will be used for Fr and Us langages

thank you for helpingIm not getting any error with ur insert statement.I think u are passing date value as a variable which is in char.

try trim that variable on both side using ltrim,rtrim before inserting.|||lookup SET DATEFORMAT in Books online. Maybe that would help.|||You can try this -
insert <tablename>
select convert(datetime,'2003/01/31 10:04:14')

Well, for more info check this out...
http://groups.google.co.in/group/microsoft.public.sqlserver.programming/browse_frm/thread/ada70fc46e7005ac/419fae5346ac4bc0?lnk=st&q=dateformat()+in+ms+sql+server+200&rnum=3&hl=en#419fae5346ac4bc0|||try this instead --

INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003-01-31 10:04:14')|||INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')

insert <tablename>
select convert(datetime,'2003/01/31 10:04:14')

or this one

try this instead --

INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003-01-31 10:04:14')
__________________

These statements all are working fine in my machine also,so no problem in statements...I think mailler made a point ..plz check that.
Joydeep|||i got it with
INSERT INTO [dbo].[Users] (DateNew) VALUES (convert(datetime,'2003/01/31 10:04:14',111))

thank you|||i got it with convert(datetime,'2003/01/31 10:04:14',111)

thank you|||I know that I'm being pendantic here, but I'd invest a bit of time now into making your application much more portable/flexible/etc. The ISO 8601 (http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html) format for date/time information is CCYY-MM-DD HH:MM:SS.TTT and that format is used by virtually the entire computing universe. It has been adopted by W3C (http://www.w3.org/TR/NOTE-datetime) which means that almost anywhere you find time on the Internet, you'll find it in this format.

You'll almost certainly save yourself lots of time and energy if you switch to using this format now, instead of having to switch to it later!

As a side note, if you expect your application to grow to the point where you may need to support more than one server, I'd suggest you spend the time to convert your application to use UCT (aka GMT) now too... This is easy to do up front, and almost impossible to do "after the fact" due to many very difficult problems caused by different locales.

-PatP|||...due to many very difficult problems caused by different locales...
PatP
You mean time zones, right?|||You mean time zones, right?You can think of the problem that way, but it is really more complex than just time zone... A locale rolls the problem up into a nice tidy (but not simple bundle). Time zones reflect a difference between local time and UCT, essentially a time offset. The problem comes from Daylight Savings Time, where different locales observe different shift dates, not all of which adjust by the same amount (some only move 30 minutes).

Unravelling the mess is easy if done while recording an event because it is easy for a computer to find UCT from its local time if necessary. Once the time is stored, there may not be any way to recover true UCT again. This gets really hard to explain, but there have been a couple of good whitepapers done on the problem.

-PatP|||Thanks a lot I shall do it at once but HOW do you convert a normal date into ISO 8601 ?

what is the SQL command for it ?|||for the moment I store all my dates time inthe format
yyyy/MM/dd hh:mm:ss

2000/12/31 18:50:06
I have just to replace / by - ?

thanks a lot|||for the moment I store all my dates time inthe format
yyyy/MM/dd hh:mm:ss

2000/12/31 18:50:06
I have just to replace / by - ?

thanks a lotYes! Exactly.

This is a relatively small change "up front", but it makes your date/time format match the format used by nearly everything else. That makes your code much easier to port to other programming languages, databases, etc at a later time. It is a small investment up front, that can pay off hugely in the future.

As a side note, SQL Server stores the data internally in a completely different form... Once you get the data into a column or variable, the work has been done. The only place you need to change anything is in the actual conversion from a character representation to a DATETIME.

-PatP|||Pat I did it , I have inserted 100 rows in my SQL database
in the good format but the database seems change it for the french format
31/12/2005 18:20:45

Once you get the data into a column or variable
I store the date in a datetime format column ?!

thanks a lot

and for searching any row in my database where a date
>
<
=
<>
=<
<=
to another date but on the date not on the datetime (yyyy-MM-dd) ?|||What is actually happening is that the database stores the value internally as a bunch of bits... They don't look like anything to the average human eye, and are logically close to a pair of integer counters. When your application retrieves the DATETIME value, the client component of the software converts those bits to a human readable form based on the locale that I mentioned in an earlier post, and the rules for that conversion happen to make the converted text appear "French" on your machine.

There are a number of ways to search for dates within a range (such as entered at any time on a given date). I prefer to do this by finding the minimum value (the very start of the day, at midnight) and the maximum value (or just past the maximum value if that is easier), then finding values between the minimum and maximum that I've selected. So for instance to find values that happened on Saint Valentine's Day 2006, I'd use:SELECT *
FROM myTable
WHERE '2006-02-14' <= myDate -- Note "equals"
AND myDate < '2006-02-15' -- Note no "equals"Using this logic is a bit strange at first, but it allows the database to use indicies to find dates of interested quickly and easily. That makes it possible to pick the rows for one day out of ten years worth of data in seconds instead of hours!

-PatP|||you helped me a lot Pat !!! in a few answers more than a few weeks looking everywhere, thanks a TON

a last question !!

I am using now

WHERE CONVERT(CHAR(10), myDate, 120) = CONVERT(CHAR(10), myDateValue, 120)

it is very easy with server side language to generate it, but for the database on millions of rows (the application will be very big) is it faster or slower than your method ?

WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'

because with your method I must add a day to the normal date and it is more complicated for server side programming

thanks again for helping|||I am using now

WHERE CONVERT(CHAR(10), myDate, 120) = CONVERT(CHAR(10), myDateValue, 120)

it is very easy with server side language to generate it, but for the database on millions of rows (the application will be very big) is it faster or slower than your method ?slower, much slower

first of all, you don't have to convert a datetime value such as '2006-02-15' to datetime, as you do on the right side of that condition, because the database will treat it that way (as a datetime value) by default

however, if you convert your table column to a string, as you do on the left side of that condition, then the database cannot use the index, if any, on that column, and will do a table scan

in other words, performing a function on a column means that the condition is not sargable (http://netknowledgenow.com:81/CS/blogs/onmaterialize/archive/2006/01/11/65.aspx) (this link is not working today but it was fine yesterday, it's a really good explanation -- you can also do a quick search to find other articles which also explain that word)|||then i must absolutly keep this only way ? :

SELECT FROM myTable
WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'

but in my database datetimes are stored in that was yyyy/mm/dd hh:mm:ss
and for the moment i couldnt get any row comparing yyyy/mm/dd hh:mm:ss to yyyy/mm/dd

of course the column is a datetime datatype|||then i must absolutly keep this only way ? :

SELECT FROM myTable
WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'that is the only way to achieve good performance (except you need to change the first operand from <= to >=)

but in my database datetimes are stored in that was yyyy/mm/dd hh:mm:ssno, actually, they are not stored that way -- datetimes are stored as two integers|||and is it better to use a datetime columns or a smalldatetime
all my dates are starting after 2000 ?

thank you|||that depends on whether you need precision in the time|||smalldatetime : Date and time data from January 1, 1900, through June 6, 2079,
with an accuracy of one minute

datetime :Date and time data from January 1, 1753, through December 31, 9999,
with an accuracy of 3.33 milliseconds

if i dont need (who needs ?) a precision of 1 minutes is it better for performances on millions of rows to use smalldatetime ?|||now I get all int that way and it seems to work :

-----------

>= 2006-02-10

SELECT FROM Users
WHERE (DateColumn > '2006-02-11')

-----------

< 2006-02-10

SELECT FROM Users
WHERE (DateColumn < '2006-02-10')
-----------
<= 2006-02-10

SELECT FROM Users
WHERE (DateColumn < '2006-02-11')
-----------

= 2006-02-10
SELECT FROM Users
WHERE
(DateColumn >= '2006-02-10')
AND
(DateColumn < '2006-02-11')

-----------

<> 2006-02-10

SELECT FROM Users
WHERE
(DateColumn > '2006-02-11')
OR
(DateColumn <= '2006-02-10')
-----------

>= 2006-02-15

SELECT FROM Users
WHERE (DateColumn < '2006-02-15')|||if i dont need (who needs ?) a precision of 1 minutes is it better for performances on millions of rows to use smalldatetime ?Yes, a SMALLDATETIME will perform better than a DATETIME for many reasons. Maybe looking at things from the machine's perspective will help (and maybe that will just confuse issues even more):DECLARE @.d DATETIME, @.s SMALLDATETIME

SELECT @.d = GetUTCDate()
SELECT @.s = @.d

SELECT @.d, Convert(VARBINARY(20), @.d)
SELECT @.s, Convert(VARBINARY(20), @.s)

SELECT @.d = DateAdd(minute, 1, @.d)
SELECT @.s = @.d

SELECT @.d, Convert(VARBINARY(20), @.d)
SELECT @.s, Convert(VARBINARY(20), @.s)-PatP|||thanks again a lot Pat that was really usefull, a deep help

thank you to everybody|||2006-02-16 03:10:53.967 | 0x0000976A00346E9E

2006-02-16 03:11:00 | 0x976A00BF

2006-02-16 03:11:53.967 | 0x0000976A0034B4EE

2006-02-16 03:11:53.967 | 0x0000976A0034B4EE

2006-02-16 03:12:00 | 0x976A00C0

here is the result of your Query
not easy to read and understand|||hard to understand?

these numbers -- 0x0000976A00346E9E, 0x976A00BF -- show you exactly how datetime values are stored internally in sql server

:)|||not easy to read and understandThe results show a couple of the issues that I was trying to explain, in a concrete form (so we don't have to talk abstractly, but can deal with real values. Please bear with me, this explanation is long, but I think it will help.

The first two results show the difference between a DATETIME (as displayed in character form) and how that DATETIME value converts to both raw binary (on the same line), and how it converts to a SMALLDATETIME (which appears one line down), and also to the SMALLDATETIME expressed as raw binary. All of the values are different, but they represent the same moment in time in different ways!

A DATETIME is accurate to +/- 3 milliseconds (I know the docs say 3.33, but that is a case where the doc writers took some liberty with what is actually stored... There's no such thing as a third of a bit). It is actually stored as a bunch of bits that mean very little to the untrained eye, except for one minor thing I'll get to later.

A SMALLDATETIME is accurate to +/- 1 minute. When you assign a DATETIME to a SMALLDATETIME, rounding takes place to the nearest whole minute. The same time is represented in a slightly less acurate form. The binary value is also quite a bit smaller, and radically different.

As an interesting side note (of little practical value), note that the value 976A appears in both of the binary strings (although in different places). This is not an accident. It has to do with how the date values are actually stored.

Another more interesting note is that the binary values of a DATETIME and the corresponding SMALLDATETIME are not directly comparable. If you take the time to understand the details, you can work around this, but it is of very little use except as an academic exercise.

Things become a bit more interesting when we add a minute to the original DATETIME value. The character form makes it easy to see this addition, and it makes perfect sense.

When we convert the changed DATETIME value to a SMALLDATETIME, the same rounding takes place, and the binary values are still quite different from each other.

The interesting part comes when you compare the binary values of the DATETIME before and after adding a minute, and the binary values of the SMALLDATETIME before and after adding a minute. The important part to notice is that the binary values of the later values have larger binary values too! There is a direct, one to one relationship between the time and the corresponding binary value.

This is why Rudy pointed out earlier that computing the minimum and maximum values of interest was much more efficient than converting the date values to character form and comparing them... You can convert a DATETIME into many different character forms, most of which are not usable for range computations like this, so in order to search for a character form SQL Server has to query every possible row. SQL Server understands dates as either DATETIME or SMALLDATETIME values, and knows how to search an index for values in a specific range. This means that it can "ride the index" to only the rows of interest in your range, and it knows exactly when it has reached the end of that range. For large sets of data, this is MANY times more efficient!

-PatP|||wow ! perfect !
I never like to apply something without understanding it
now I get a better idea of the way SQL is working with dates

and I think that generally i need only smalll
datetime datatype, i had never used it before

thanks a lot once more !|||One more thing to throw out, just so you don't get surprised... The SMALLDATETIME datatype allows entry of temporal (time based) values from 1900-01-01 through 2079-06-06 23:39. This is fine for many purposes (I won't be alive to deal with any problems it might cause when it runs out, and I sincerely doubt that SQL Server will still be in use (at least in its present form) 70 years from now! However, many contracts (like Japanese mortgages) already extend well past that limit, so I usually use DATETIME even though a SMALLDATETIME would do.

I'm a lazy bum... If I can code/create something once then safely forget about it, I'll almost always do that instead of something a bit simpler that will work for a while, but might be a problem for me (or my successor) in the future. I won't do a lot of work to avoid a potential problem, but I'll do easy things to avoid getting a call at 03:00 wondering why a job failed and how soon can I get it fixed!

-PatP|||Pat your code works fine for me in SQL 2005, but a customer with SQL 2000 get errors again everywhere on dates, i have started a new thread here >> http://dbforums.com/showthread.php?t=1212861

i am really lost with dates .. and i dont know what to do

thanks again

controlling security through stored procedures -- 2005 behaviour

Hi!

I'm trying to control security through sps -- meaning execute permissions are granted on stored procedures, and no users have read/write permissions on tables, etc directly.

Which works fine as long as all objects referenced are in the same db as the procedure.

An issue arises when a stored procedure accesses a table in another database:

Getting a : Msg 229 SELECT permission denied on object 'blah' Even though the procedure is created by sysadmin.

Has this changed since 2000? I'm pretty sure in 2000 it would've worked as the sp would be executed in sp owner's security context.

Moreover, when I try to use EXECUTE AS in the sp as a workaround, I am getting the following, no matter what account I try to impersonate:

Msg 916, Level 14, State 1, Procedure vvv, Line 4
The server principal % is not able to access the database "blah" under the current security context.

any ideas?
Thanks!

Most likely this scenario worked on Windows 2000 with cross-database ownership chaining enabled. Turning on this feature is not recommended, as it may lead to an elevation of privileges (i.e. the DB administrators of the source database may escalate their privileges to become DB administrators on the target DB).

The reason why your stored procedure marked with “execute as” is not working is because the impersonated context is (by default) scoped only to the surrent (source) database, and stripped down from it's server-scoped permissions and privileges. If you wish to use this impersonated context outside the source DB, you need to establish a trust relationship on the target DB.

To solve this problem, you can probably use digital signatures to solve your problem; by signing the stored procedure with a certificate you have a way to ensure that the code has not been tampered with. If at run time the signature matches the code, the certificate can be used in two ways:

* As a secondary identity for the execution context. This means that if there is a user mapped to the signing certificate, the permissions on that user will be used to calculate the permissions on the object.

* When the module (SP) is marked with execute as, the signature will work as an authenticator, that means the signature will be used to vouch for the impersonated context in the stored procedure

Note that for the secondary identity approach, the signature will be added to the current context therefore, if the current context is not a valid one on the server scope (i.e. the caller is an approle), the certificate as secondary identity cannot be used on cross database scenario.

The second approach on the other hand establishes a whole new context on top of the calling context, and it is the signature the one vouching for this new context on the target database.

I am posting a small demo at the end taht I hope will help you.

Thanks a lot for your comments and feedback.

-Raul Garcia
SDE/T
SQL Server Engine
This posting is provided "AS IS" with no warranties, and confers no rights.

-

/*******************************************************************

*

* This posting is provided "AS IS" with no warranties, and
* confers no rights.

*

* Author: Raulga

* Date: 08/24/2005

* Description:

* This demo shows how to use digital signatures to access
* resources on a different database by using digitaly signed stored
* procedures to control the access rather than using cross database
* ownership chaining.

*

* The first SP will be using the siganture as a secondary identity
* on top of the calling context. This means that only a context with
* a server-presence will succeed on this call (i.e. approles will not
* be able to accsss the resources on the target database as they

* don't have a server presence).

*

* The second approach will be by specifying a context switch
* (EXECUTE AS) on the stored procedure and using the signature as an
* authenticator; this means that the signature can vouch for the
* impersonated context (specifid on the module). This mechanism will
* allow to access the resources regardless of the original calling
* context because a new context (vouched by the signature) is placed
* on top of the orginal one, but requires more managment.

*

* (c) 2005 Microsoft Corporation. All rights reserved.

*

***********************************************************************************************/

CREATE DATABASE db_Source

go

CREATE DATABASE db_Target

go

CREATE LOGIN dbo_db_Source WITH PASSWORD = 'My S0uRc3 D8 p@.55W0rD!'

CREATE LOGIN dbo_db_Target WITH PASSWORD = 'My +@.r637 D8 p@.55W0rD!'

go

-- Change the ownership for the source and the target databases

ALTER AUTHORIZATION ON DATABASE::db_Source to dbo_db_Source

ALTER AUTHORIZATION ON DATABASE::db_Target to dbo_db_Target

go

-- This principal will be the data owner, he can access the data on

-- the target database, and he controls the stored procedures on the

-- source database

CREATE LOGIN data_owner WITH PASSWORD = 'd@.+4 0wn3R'

-- This principal should only have access to the data via the stored

-- procedures

CREATE LOGIN someuser WITH PASSWORD = 's0m3 p@.55w0Rd'

go

use db_Target

go

CREATE USER someuser

CREATE USER data_owner WITH DEFAULT_SCHEMA = data_owner

go

CREATE SCHEMA data_owner AUTHORIZATION data_owner

go

CREATE TABLE data_owner.MyTable( data nvarchar(100) )

go

INSERT INTO data_owner.MyTable values ( N'My data' )

go

use db_Source

go

CREATE USER someuser

CREATE USER data_owner WITH DEFAULT_SCHEMA = data_owner

go

CREATE SCHEMA data_owner AUTHORIZATION data_owner

go

-- ALlow someuser to execute any module on the schema called data_owner

GRANT EXECUTE ON SCHEMA::data_owner TO someuser

go

-- Create a stored procedure that uses the default execution context

-- (the caller's context) at runtime

CREATE PROC data_owner.sp_GetMyData01

AS

select * from db_Target.data_owner.MyTable

go

-- Create a stored procedure similar to teh previous one, but this time we will explicitly

-- use the data_owner context via EXECUTE AS

CREATE PROC data_owner.sp_GetMyData02

WITH EXECUTE AS 'data_owner'

AS

select * from db_Target.data_owner.MyTable

go

-

-- Let's see what is the behavior without any signatures

--

-- You can either start new connections or just use the

-- EXECUTE AS LOGIN & REVERT statements I show here for testing

-- Execute as the data owner

-

EXECUTE AS LOGIN = 'data_owner'

go

-- will succeed

EXEC data_owner.sp_GetMyData01

go

-- Will fail as the impersonated context is not trusted on the target
-- database

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Execute as someuser

-

EXECUTE AS LOGIN = 'someuser'

go

-- will fail due to the lack of permissions on the target database

EXEC data_owner.sp_GetMyData01

-- will fail as the impersonated context is not trusted on the target
-- database

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Signing the stored procedures

--

-- Create 2 certificates one to sign each SP.

-- Note that I am using passwords to protect the private keys.

-- It is also possible to use a DB master key to protect private the
-- keys, please refer to BOL for more information on the key
-- hierarchy

CREATE CERTIFICATE cert_GetMyData01

ENCRYPTION BY PASSWORD = 'GetMyData01 c3r+ p@.55w0Rd'

WITH SUBJECT = 'Certificate to sign sp_GetMyData01'

go

CREATE CERTIFICATE cert_GetMyData02

ENCRYPTION BY PASSWORD = 'GetMyData02 c3r+ P455W0Rd'

WITH SUBJECT = 'Certificate to sign sp_GetMyData02'

go

-- Now sign the stored procedures, as the cert's

-- private keys are protected by passwords, we have to use the
-- passwords to sign

ADD SIGNATURE TO data_owner.sp_GetMyData01 BY CERTIFICATE cert_GetMyData01

WITH PASSWORD = 'GetMyData01 c3r+ p@.55w0Rd'

go

ADD SIGNATURE TO data_owner.sp_GetMyData02 BY CERTIFICATE cert_GetMyData02

WITH PASSWORD = 'GetMyData02 c3r+ P455W0Rd'

go

-- Let's take a quick look to the metadata for the signed modules

SELECT schema_name( c.schema_id ) as schema_name, c.name,

b.name, a.crypt_property as 'module siganture' FROM

sys.crypt_properties a,

sys.certificates b,

sys.objects c

WHERE a.thumbprint = b.thumbprint AND a.class = 1
AND a.major_id = c.object_id

go

-- Depending on your application and environment, sometimes you may
-- not want to leave the private keys on the database, and either
-- destroy the private keys (this way, they can never be used to
-- sign anything else), or back up a copy of the private keys and
-- store them in a safe place. For this demo I will just destoy the
-- private keys as we don't need them anymore

ALTER CERTIFICATE cert_GetMyData01 REMOVE PRIVATE KEY

ALTER CERTIFICATE cert_GetMyData02 REMOVE PRIVATE KEY

go

-- Now, we need to create a backup for the certificate public data.

-- We will need to import it back on teh target database.

BACKUP CERTIFICATE cert_GetMyData01 TO FILE = 'cert_GetMyData01.cer'

BACKUP CERTIFICATE cert_GetMyData02 TO FILE = 'cert_GetMyData02.cer'

go

use db_Target

go

-- Import the certificates on the target database, note that we don't
-- need the private keys

CREATE CERTIFICATE cert_GetMyData01
FROM FILE = 'cert_GetMyData01.cer'

go

CREATE CERTIFICATE cert_GetMyData02
FROM FILE = 'cert_GetMyData02.cer'

go

-- Now let's create users mapped to each one of the certificates.

-- As permissions can only be granted to principals and not directly

-- to a certificate, we need to map the certificate to a user.

-- Note: The cert-mapped user SID is derived from teh certificate
-- thumbprint

-- therefore any 2+ principals (login or user in any database)
-- mapped to the

-- same certificate will have the same SID and will refer to the same

-- principal for practical purposes.

CREATE USER cert_GetMyData01 FOR CERTIFICATE cert_GetMyData01

go

CREATE USER cert_GetMyData02 FOR CERTIFICATE cert_GetMyData02

go

-- For the first SP, grant the permissions to the cert-mapped
-- user directly

GRANT SELECT ON data_owner.MyTable TO cert_GetMyData01

go

-- For the second SP, we want only AUTHENTICATE permissiion, this
-- will allow teh certificate to vouch for the context only on this
-- database.

-- Note: As the trust is only accross database and not accross the
-- instance, the new context is only valid for database operations,
-- and will not honor any server-scoped permissions.

GRANT AUTHENTICATE TO cert_GetMyData02

go

USE db_Source

go

-

-- Let's see what is the behavior without any signatures

--

-- You can either start new connections or just use the

-- EXECUTE AS LOGIN & REVERT statements I show here for testing

-- Execute as the data owner

-

EXECUTE AS LOGIN = 'data_owner'

go

-- will succeed

EXEC data_owner.sp_GetMyData01

go

-- will succeed as the module is executing as "data_owner"

-- (the module is specifying the context itself), and the

-- signature is vouching for this context

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Execute as someuser

-

EXECUTE AS LOGIN = 'someuser'

go

-- will succed as the certificate will be granting the required
-- permission to select the data from the table

-- Note that someuser is a valid context accross the server at
-- this point

EXEC data_owner.sp_GetMyData01

-- will succeed as the module is executing as "data_owner"

-- (the module is specifying the context itself), and the

-- signature is vouching for this context

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- cleanup

USE master

go

DROP DATABASE db_Source

go

DROP DATABASE db_Target

go

DROP LOGIN dbo_db_Source

DROP LOGIN dbo_db_Target

DROP LOGIN data_owner

DROP LOGIN someuser

go

|||

Raul -- thanks a lot for taking the time to do this. Excellent explanation and demo!

Controlling Access

Is there a method of only allowing users to access SQL
Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
I am not referring to the "Application Roles" available
within SQL Server, rather I would like the following to
take place: -
Example 1.
User connects to SQL Server Database, by either using
Commercial Software or Bespoke System, using "NT
Authentication".
SQL Server recognises the user and the Application/System
and allows access to the Database.
Example 2.
User connects to SQL Server Database, by linking/Importing
Tables to a new Database Document, using "NT
Authentication".
SQL Server recognises the user but not the
Application/System and denies access to the Database.
I am using SQL Server 7.0, although we will soon be
migrating to 2000.
TIA
Tony C.Unfortunately no. You could monitor for connections after
the fact, e.g. have a job that checks who is connected using
what application and kill processes that are connected using
the non-approved applications. But you can't really control
access based on what application is being used.
-Sue
On Fri, 2 Apr 2004 01:34:33 -0800, "Tony C"
<tony.chorleyRUBBISH@.mcalpineplc.com> wrote:

>Is there a method of only allowing users to access SQL
>Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
>I am not referring to the "Application Roles" available
>within SQL Server, rather I would like the following to
>take place: -
>Example 1.
>User connects to SQL Server Database, by either using
>Commercial Software or Bespoke System, using "NT
>Authentication".
>SQL Server recognises the user and the Application/System
>and allows access to the Database.
>Example 2.
>User connects to SQL Server Database, by linking/Importing
>Tables to a new Database Document, using "NT
>Authentication".
>SQL Server recognises the user but not the
>Application/System and denies access to the Database.
>I am using SQL Server 7.0, although we will soon be
>migrating to 2000.
>TIA
>Tony C.

Control Flow Task Error shouldn't fail package

Hi all,

I have a Send Mail Task in my control flow to notify users that the processing is done. I want to avoid the package to fall in error if the Send Mail task failed.

What is the best practice to do that ?

Should I raise the MaximumErrorCount of theSend Mail Task ? Should I play with ErrorHandler ?

Try setting the Send Mail Task's "ForceExecutionResult" to "Success"

Control Date Range in Rpt Svc

lHi,
I have two date parameters (Start Date and End Date) in one report and it
alows users input startdate and enddate. But I don't want users execute
reports more than 5 days from the start date. How do I limit it before the
reprot gets executed? Thanks.
ChuckProbably the easiest way to control this would be in the report parameters.
Instead of allowing them to enter a start date and end date allow them to
enter one of the dates, then set the second parameter as the number of days
to include in the report and set a drop down for those values, 1-5.
For instance, if you want them to be able to enter the end date and create a
report for the previous five days you wuold set your parameters up like this.
Parameter Name: @.EndDate
Type: Date/Time
Parameter Name: @.StartDate
Type: Date/Time
In the Report Parameters dialog for StartDate set the available values as:
Label Value
1 day DateAdd(day, -1, @.EndDate)
2 days DateAdd(day, -2, @.EndDate)
ect, ect...
This should allow them to select any ending date and from 1 to 5 days
previous for the start date.|||Hi JHoward,
Thank you for getting back to me. This is one solution. However, report
users does not like the format because they have to add days into start date
to figure out the End Date. Is there a way that if it is more than 5 days,
it will bring up an alert message and will NOT execute the report even though
a user click 'View Report'? Thanks.
Chuck
"JHoward" wrote:
> Probably the easiest way to control this would be in the report parameters.
> Instead of allowing them to enter a start date and end date allow them to
> enter one of the dates, then set the second parameter as the number of days
> to include in the report and set a drop down for those values, 1-5.
> For instance, if you want them to be able to enter the end date and create a
> report for the previous five days you wuold set your parameters up like this.
> Parameter Name: @.EndDate
> Type: Date/Time
>
> Parameter Name: @.StartDate
> Type: Date/Time
> In the Report Parameters dialog for StartDate set the available values as:
> Label Value
> 1 day DateAdd(day, -1, @.EndDate)
> 2 days DateAdd(day, -2, @.EndDate)
> ect, ect...
> This should allow them to select any ending date and from 1 to 5 days
> previous for the start date.
>
>
>

control concurrent users?

is it possible to limit the concurrent users in SQL connection string? Now, i m using .net 2003.

regards,

You can change the "max worker threads" option in SQL Server by running the statements below:

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'max worker threads',255
reconfigure
go

For more information about this option, please take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_09wu.asp

|||

the problem is how can i configure my customer's SQL Server configuration. Now, i get one way. is it the right one?

Data Source=XXX;database=XXX;UID=XXX;PWD=XXX;MAX POOL SIZE=5

|||Sorry for misunderstanding. Yes the MAX POOL SIZE property should work in this case.

Constraint expression not working

Hi!
I have a table with users in which I would like to have a constraint making
sure that for active users (IsActive=1) a valid user name always exists
(OSUserName <> '' AND OSUserName IS NOT NULL).
I enter the following constraint expression in the table designer:
(([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
[OSUserName] is null))
But when I look at what gets saved, the paranthesis separating the two AND
clauses are gone:
([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
[OSUserName] is null)
And this also make my constraint fail and allowing IsActive = 0 and
OSUserName = 'XYZ'. How can I write the expression to get the desired
effect?
Brgds
JonasYou can write the same expression in the following way:
SIGN(LEN(ISNULL([OSUserName], ''))) = [IsActive]
But I don't understand your logic. Do you really mean that if you change a
user from being active to non-active that you have set the OSUserName to
NULL as well? That is what you constraint enforces now. If you don't need
that, you can simplify your check to LEN(ISNULL([OSUserName], '')) > 0 OR
[IsActive] = 0. I.e. you either have to provide a username or the user must
be inactive.
Jacco Schalkwijk
SQL Server MVP
"Jonas" <jonas@.no.spam.pl> wrote in message
news:uMmM97iKFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a table with users in which I would like to have a constraint
> making sure that for active users (IsActive=1) a valid user name always
> exists (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||Instead of using query designer, do your work from Query Analyzer...
ALTER TABLE YourTable
ADD CONSTRAINT CK_IsActive_UserName CHECK
((([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
[OSUserName] is null))
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jonas" <jonas@.no.spam.pl> wrote in message
news:uMmM97iKFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a table with users in which I would like to have a constraint
making
> sure that for active users (IsActive=1) a valid user name always exists
> (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||Jonas,
Create the constraint from QA.
Example:
use northwind
go
create table t (
IsActive smallint not null check (IsActive = 0 or IsActive = 1) default (0),
OSUserName varchar(50) null,
)
go
alter table t
add constraint chk_OSUserName check (([IsActive] = 1 and
isnull([OSUserName], '') > '') or ([IsActive] = 0))
go
insert into t default values
insert into t values(1, 'MSSQLSERVER')
go
insert into t values(1, null)
go
update t
set IsActive = 1
where OSUserName is null
go
select * from t
go
drop table t
go
AMB
"Jonas" wrote:

> Hi!
> I have a table with users in which I would like to have a constraint makin
g
> sure that for active users (IsActive=1) a valid user name always exists
> (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||No you are not crazy: SQL Server does re-write the check constraint
source.
If a check is defined as "Column in ('a', 'b' )" it is re-written to
"[Column] = 'a' or [Column] = 'b'"
For complex nested conditions, the re-write includes awareness of the
precedence of NOT, AND and OR as defined in Books On Line.
Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Consolidation problem

I have 2 tables.
In the User Table I have a UserID and the CompanyID of the Company he
belongs to.
In the second table (EmailBlocked), are the users or company a particular
user has blocked. If a user blocks a company, then all users in that
company are blocked. If the company is not blocked, then only the
particular user in the record is blocked.
I can do this in 2 selects, but I am trying to get it to work in one.
DROP TABLE UserTable
go
CREATE TABLE UserTable
(
UserID int,
CompanyID varChar(15)
)
go
DROP TABLE EmailBlocked
go
CREATE TABLE EmailBlocked
( UserID int,
BlockedUserID int,
CompanyID int,
CompanyBlocked int
)
go
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyB
locked) VALUES
(309,150,5,1)
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyB
locked) VALUES
(168,130,Null,0)
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyB
locked) VALUES
(171,120,250,0)
INSERT UserTable (UserID,CompanyID) VALUES (152,4)
INSERT UserTable (UserID,CompanyID) VALUES (150,5)
go
This Select is close, but not quite right.
There are 2 tests;
1) Check if the 2 users match (UserID and BlockedUserID are in a record
in the table).
2) Check to see if the User (UserID) has the company blocked that the
2nd user (BlockedUserID) belongs to.
Pass back the BlockedUserID and null for the companyID #1 and Pass back Null
for the BlockedUserID and the CompanyID for #2. If neither (nothing is
blocked for these users), I should get no record back.
In the following, I am getting no record back. Test #1, there is no match.
Test #2, 309 has blocked Company 5, and 150 is part of company 5 so he
should have been blocked and I would like to get the record back as
"NULL,5". This tells me that the company is what is being blocked.
Declare @.UserID bigint,@.BlockedUserID bigInt
Select @.UserID = 309,@.BlockedUserID = 152
Select BlockedUserID,CompanyID
from EmailBlocked
where (UserID = @.UserID and
BlockedUserID = @.BlockedUserID) or
(CompanyID in (select CompanyID
from UserTable
where UserID = @.BlockedUserID) and CompanyBlocked = 1)
Thanks,
TomHi, Tom
I think this query gives the expected results:
select
case when CompanyID is null
then BlockedUserID
end as BlockedUserID,
CompanyID
from (
select (
Select BlockedUserID
from EmailBlocked
where UserID = @.UserID
and BlockedUserID = @.BlockedUserID
) as BlockedUserID,
(
select CompanyID
from EmailBlocked
where UserID = @.UserID and CompanyBlocked = 1
and CompanyID in (
select CompanyID
from UserTable
where UserID = @.BlockedUserID
)
) as CompanyID
) x where BlockedUserID is not null or CompanyID is not null
However:
1. It's pretty complicated; I would rather use two queries.
2. The DDL is somewhat strange:
a) the data type of CompanyID should be the same in both tables
b) you have no primary keys, foreign keys, check constraints, etc. You
should have (at least) primary keys for each table, foreign keys where
appropriate, and "not null" columns where a value is required. Ideally,
you should have check constraints (and/or other verifications) so you
cannot insert any inconsistent data in the database. For example, you
should have a check constraint that says: "CompanyBlocked=0 OR
CompanyID is not null", so you cannot block an unspecified company.
Also, a primary key in the UserTable, would make it clear if a user
belongs to only one company or if it is allowed that a user belong to
more than one company.
c) to block a company is it really necessary to specify a blocked user
(within that company) ? If no, a different DDL would be more useful. If
yes, a constraint should be added so to make sure that the blocked user
is really in the blocked company. This may be implemented using a
foreign key on two columns referencing the UserTable.
d) when you block only a user (i.e. CompanyBlocked=0), what is the
purpose of specifying the company where he works ? If there is no
purpose, a different DDL would be more useful, again.
Razvan|||Hi Razvan,
Razvan,
This seems to do exactly what I needed.
I thought I would need to use a derived table somehow, but couldn't figure
out how to make it work.
I am trying to figure out the thought process that leads to building the
first table and then building the table around that.
below:
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1135236075.969255.167240@.o13g2000cwo.googlegroups.com...
> Hi, Tom
> I think this query gives the expected results:
> select
> case when CompanyID is null
> then BlockedUserID
> end as BlockedUserID,
> CompanyID
> from (
> select (
> Select BlockedUserID
> from EmailBlocked
> where UserID = @.UserID
> and BlockedUserID = @.BlockedUserID
> ) as BlockedUserID,
> (
> select CompanyID
> from EmailBlocked
> where UserID = @.UserID and CompanyBlocked = 1
> and CompanyID in (
> select CompanyID
> from UserTable
> where UserID = @.BlockedUserID
> )
> ) as CompanyID
> ) x where BlockedUserID is not null or CompanyID is not null
> However:
> 1. It's pretty complicated; I would rather use two queries.
Why would you use 2 queries if one query works, as yours seems to?

> 2. The DDL is somewhat strange:
> a) the data type of CompanyID should be the same in both tables
You're right. This was a mistake. The companyID is supposed to be an Int.
In my actual table, it is created as an Identity.

> b) you have no primary keys, foreign keys, check constraints, etc. You
> should have (at least) primary keys for each table, foreign keys where
> appropriate, and "not null" columns where a value is required.
I agree. But this is just a quick DDL to allow you (and others) to see what
I am trying to do. It isn't exactly the same as my normal tables (which do
have primary keys, foreign keys and constraints). My UserTable does use
UserID as the Primary key. The EmailBlocked Table does have a Primary key
(EmailBlockedID Int Identity). My Foreign key would be UserID referencing
UserID in the UserTable.

>Ideally,
> you should have check constraints (and/or other verifications) so you
> cannot insert any inconsistent data in the database. For example, you
> should have a check constraint that says: "CompanyBlocked=0 OR
> CompanyID is not null", so you cannot block an unspecified company.
True, but I am enforcing this in my Code.

> Also, a primary key in the UserTable, would make it clear if a user
> belongs to only one company or if it is allowed that a user belong to
> more than one company.
And it is. It is an identity as I mentioned earlier. But I may change this
to some random number so it cannot be guessed.

> c) to block a company is it really necessary to specify a blocked user
> (within that company) ?
No. Actually, the UserID is irrelavant if CompanyBlocked = 1. Actually, in
my code, if I have 3 users defined from the same company (none have
CompanyBlocked = 1) and I later Block the company - I delete 2 of the
records and set the CompanyBlocked=1 on the record I choose to keep. This
way there is only one record with the CompanyBlocked set for any User.

> If no, a different DDL would be more useful. If
> yes, a constraint should be added so to make sure that the blocked user
> is really in the blocked company. This may be implemented using a
> foreign key on two columns referencing the UserTable.
> d) when you block only a user (i.e. CompanyBlocked=0), what is the
> purpose of specifying the company where he works ? If there is no
> purpose, a different DDL would be more useful, again.
It isn't. And the CompanyID is irrelavant if the CompanyID = 0.
As a matter a fact, if it were necessary to check the CompanyBlocked field
first to see if a company blocked before doing the UserID/BlockedID test, I
assume this would need to be done using 2 separate queries.
Thanks,
Tom
> Razvan
>