Application Role & 2nd Database

I'm trying to use application roles for the 1st time and have a problem.
Most of the select statements I use reference tables in a separate database.
After reading the BOL I find that this only works through the GUEST account.
Drat!
Any suggestions to fix this? I'm considering setting up views in the local
DB, but this will involve changing the code and creating the views and I
still don't know if it will work.
--
Jeffrey R. Price
Database Manager
Computing & Communication Services
Max M. Fisher college of Business
The Ohio State University
320F Mason Hall
250 W. Woodruff Avenue
Columbus, OH 43210-1309Creating referencing views will work fine. If you don't want to grant
permissions on the underlying tables to guest or public, you can use an
unbroken cross-database ownership chain so that the app role needs only
permissions on views in the application role database.
To maintain an unbroken chain, the owners of the objects involved need to
map to the same login. If your objects are owned by 'dbo', this
necessitates that the owners of both databases be the same. Also, the
cross-database chaining option (introduced in SQL 2000 SP3) needs to be
enabled for those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use application roles for the 1st time and have a problem.
> Most of the select statements I use reference tables in a separate
database.
> After reading the BOL I find that this only works through the GUEST
account.
> Drat!
> Any suggestions to fix this? I'm considering setting up views in the
local
> DB, but this will involve changing the code and creating the views and I
> still don't know if it will work.
> --
> Jeffrey R. Price
> Database Manager
> Computing & Communication Services
> Max M. Fisher college of Business
> The Ohio State University
> 320F Mason Hall
> 250 W. Woodruff Avenue
> Columbus, OH 43210-1309
>|||Thanks, I can run it fine now from Query Analyzer, but not my app... more
work to do there.
I had to set the DB owners as the same account and a few other tasks. The
following MSDN article helped:
http://msdn.microsoft.com/library/d.../>
up_1cj5.asp
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ecqxs2D8DHA.2056@.TK2MSFTNGP10.phx.gbl...
> Creating referencing views will work fine. If you don't want to grant
> permissions on the underlying tables to guest or public, you can use an
> unbroken cross-database ownership chain so that the app role needs only
> permissions on views in the application role database.
> To maintain an unbroken chain, the owners of the objects involved need to
> map to the same login. If your objects are owned by 'dbo', this
> necessitates that the owners of both databases be the same. Also, the
> cross-database chaining option (introduced in SQL 2000 SP3) needs to be
> enabled for those databases.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> database.
> account.
> local
>|||Correction......
I did not get the Application role to work across Databases. I continue to
receive the error "Server user 'price_9' is not a valid user in database
'FCoB_Contacts'."
I've tried both
EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE
and
EXEC sp_configure 'Cross DB Ownership Chaining', '0';RECONFIGURE with
EXEC sp_dboption 'FCoB_Contacts', 'db chaining', 'TRUE'
Both DBs are owned by the same account.
Drat!
"Jeff Price" <price.9@.osu.edu> wrote in message
news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use application roles for the 1st time and have a problem.
> Most of the select statements I use reference tables in a separate
database.
> After reading the BOL I find that this only works through the GUEST
account.
> Drat!
> Any suggestions to fix this? I'm considering setting up views in the
local
> DB, but this will involve changing the code and creating the views and I
> still don't know if it will work.
> --
> Jeffrey R. Price
> Database Manager
> Computing & Communication Services
> Max M. Fisher college of Business
> The Ohio State University
> 320F Mason Hall
> 250 W. Woodruff Avenue
> Columbus, OH 43210-1309
>|||Since an application role is only known in a single database, you need to
enable the 'guest' user in the other database so you have a security context
in the other database. No guest user permissions need to be granted. For
example:
Use MyOtherDatabase
EXEC sp_adduser 'guest'
Also, you don't need to enable cross-database chaining at the server level.
Your can specify it at the database level for only the databases that need
this option turned on:
EXEC sp_dboption 'MyDatabase, 'db chaining', 'TRUE'
EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:epWnGnO8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> Correction......
> I did not get the Application role to work across Databases. I continue
to
> receive the error "Server user 'price_9' is not a valid user in database
> 'FCoB_Contacts'."
> I've tried both
> EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE
> and
> EXEC sp_configure 'Cross DB Ownership Chaining', '0';RECONFIGURE with
> EXEC sp_dboption 'FCoB_Contacts', 'db chaining', 'TRUE'
> Both DBs are owned by the same account.
> Drat!
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> database.
> account.
> local
>|||Thanks! That did the trick.
Do we need to be concerned with the existence of the "Guest" account?
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e4vgGmQ8DHA.4060@.tk2msftngp13.phx.gbl...
> Since an application role is only known in a single database, you need to
> enable the 'guest' user in the other database so you have a security
context
> in the other database. No guest user permissions need to be granted. For
> example:
> Use MyOtherDatabase
> EXEC sp_adduser 'guest'
> Also, you don't need to enable cross-database chaining at the server
level.
> Your can specify it at the database level for only the databases that need
> this option turned on:
> EXEC sp_dboption 'MyDatabase, 'db chaining', 'TRUE'
> EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:epWnGnO8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> to
problem.
I
>|||> Do we need to be concerned with the existence of the "Guest" account?
As long as you haven't granted additional permissions to guest or public,
guest user access is limited to default public role permissions. This
includes the ability to view meta data, (e.g. table aned column names) but
no access to user objects and data. Users will still need an account to
connect to SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:OkTtG8j8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> Thanks! That did the trick.
> Do we need to be concerned with the existence of the "Guest" account?
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e4vgGmQ8DHA.4060@.tk2msftngp13.phx.gbl...
to
> context
For
> level.
need
continue
database
with
> problem.
the
and
> I
>

appending two datetimes which were converted from strings

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.

the time is converted from string.

thanksthe time is converted from string.

One method is to build a string containing both date and time in format is
'yyyymmdd hh:mm:ss'. SQL Server can then parse the string into a datetime
value. For example:

DECLARE
@.MyDate datetime,
@.MyTime datetime,
@.MyDateTime datetime

SET @.MyDate = '20061121'
SET @.MyTime = '12:13:14'

--concatenate date and time strings and assign to datetime data type
SET @.MyDateTime =
CONVERT(char(9), @.MyDate, 112) +
CONVERT(char(8), @.MyTime, 114)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
news:1164084180.584403.64710@.k70g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.
>
the time is converted from string.
>
thanks
>

|||thanks for the answer.it worked.

i have another problem.

how can i convert a char(4) into a time where the datatype of the
column is datatime?

i think this is my only problem now.thanks
Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

the time is converted from string.

>
One method is to build a string containing both date and time in format is
'yyyymmdd hh:mm:ss'. SQL Server can then parse the string into a datetime
value. For example:
>
DECLARE
@.MyDate datetime,
@.MyTime datetime,
@.MyDateTime datetime
>
SET @.MyDate = '20061121'
SET @.MyTime = '12:13:14'
>
--concatenate date and time strings and assign to datetime data type
SET @.MyDateTime =
CONVERT(char(9), @.MyDate, 112) +
CONVERT(char(8), @.MyTime, 114)
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
news:1164084180.584403.64710@.k70g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.

the time is converted from string.

thanks

|||paul_zaoldyeck wrote:

Quote:

Originally Posted by

how can i convert a char(4) into a time where the datatype of the
column is datatime?

Combine it with an appropriate date (today's date, a date from a
related record, whatever) in a similar fashion to the previous
answer. If you don't care about the date, then pick a fixed value
(e.g. January 1, 1970) and use that in all cases.|||The example below parses a string in hh:mm format into a datetime variable.
Note that a datetime always includes both date and time components so your
app will need to ignore the date part, if not needed. The date defaults to
'19000101'.

DECLARE
@.MyTimeString char(4),
@.MyDateTime datetime

SET @.MyTimeString = '1213' --hh:mm

SET @.MyDateTime = LEFT(@.MyTimeString, 2) + ':' + LEFT(@.MyTimeString, 2) +
':00'

SELECT @.MyDateTime

--
Hope this helps.

Dan Guzman
SQL Server MVP

"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
news:1164254851.864579.14760@.b28g2000cwb.googlegro ups.com...

Quote:

Originally Posted by

thanks for the answer.it worked.
>
i have another problem.
>
how can i convert a char(4) into a time where the datatype of the
column is datatime?
>
i think this is my only problem now.thanks
Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

the time is converted from string.

>>
>One method is to build a string containing both date and time in format
>is
>'yyyymmdd hh:mm:ss'. SQL Server can then parse the string into a
>datetime
>value. For example:
>>
>DECLARE
> @.MyDate datetime,
> @.MyTime datetime,
> @.MyDateTime datetime
>>
>SET @.MyDate = '20061121'
>SET @.MyTime = '12:13:14'
>>
>--concatenate date and time strings and assign to datetime data type
>SET @.MyDateTime =
> CONVERT(char(9), @.MyDate, 112) +
> CONVERT(char(8), @.MyTime, 114)
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
>news:1164084180.584403.64710@.k70g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.
>
the time is converted from string.
>
thanks
>

>