Is user allowed?

I thought this would be easy, but it's now seeming way more complex - so I'm
hoping someone has a shortcut.
How can you figure out if a User_Name is allowed in a specific role? Ok,
sounds easy at first, but what roles that that role is a sub-role of?
That's where it's gotten more complex than I thought --
So, pretend that we've got MasterRole of which BabyRole is a member. MyUser
is a member of BabyRole. And since BabyRole is a member of MasterRole,
MyUser is allowed to do all the things that MasterRoll can do. However if I
run sp_helplogins on MyUser, it will list BabyRole but not MasterRole. Is
there any sp or easy code that can enumerate all those roles (or even
better, is there any sp that I can call with a role name and a user and it
returns if the user is allowed in that role?)
TIA!Duhhh.. Ok, I found the command IS_MEMBER... I feel dummmbbb... Sorry to
waste the bandwidth :)
"Brett Wickard" <brettwickard@.nospam.nospam> wrote in message
news:OX7xqibfGHA.4828@.TK2MSFTNGP05.phx.gbl...
>I thought this would be easy, but it's now seeming way more complex - so
>I'm hoping someone has a shortcut.
> How can you figure out if a User_Name is allowed in a specific role? Ok,
> sounds easy at first, but what roles that that role is a sub-role of?
> That's where it's gotten more complex than I thought --
> So, pretend that we've got MasterRole of which BabyRole is a member.
> MyUser is a member of BabyRole. And since BabyRole is a member of
> MasterRole, MyUser is allowed to do all the things that MasterRoll can do.
> However if I run sp_helplogins on MyUser, it will list BabyRole but not
> MasterRole. Is there any sp or easy code that can enumerate all those
> roles (or even better, is there any sp that I can call with a role name
> and a user and it returns if the user is allowed in that role?)
> TIA!
>
>sql

is this possible? ...not an SQL master

Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
RickAn example:

INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable

Assumes that the character is always the last position and length of 1.

Originally posted by entangled
Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
Rick|||HI,

try to use decode(substr(backPM,length(backPM)-1,1),'R',substr(backPM,1,length(backPM)-1,backPM)

and use the same formula for AheadPM column

Originally posted by entangled
Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
Rick|||Thank you for the example. This one example pretty much addresses both issues. I will work with this and see how I can apply this approach.

many thanks,
Rick Sperling

Originally posted by dmmac
An example:

INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable

Assumes that the character is always the last position and length of 1.