PADDING of blanks, how to turn off?

Here's a potential stumper.
I have 20 columns in a SQL table: I am accessing the data through a view. I
am acessing this data through SP's via ASP.NET.
Two of the columns are identical, "nvarhar 50"
One of these columns automatically pads extra blanks when the data is
written, the other does not do the padding.
The column that is doing the padding was created as a "char 50" after the
other column was created as a nvarchar. I then changed the 'char 50' to an
'nvarhar 50' in Enterprise Manager.
I read up un ANSI PADDDING option, but nothing I know of would have changed
the padding setting of the database between these 2 changes (except one was
colum was possibly created under SQl Server and the 'broken one' under
MSDE...). Anyway. my documentation says that nvarhar's are always padded
(but mine aren't for one column).
So my questions:
1) Did the conversion from char to nvarchar do something screwy with the
padding behavior of this column?
2) Is there any way to fix this problem in a live database? From what I
read, youy can't change the padding nature after a field is already created.
Mark McFarlane
Hi
Look at the UsesAnsiTrim setting for COLUMNPROPERTY
COLUMNPROPERTY(OBJECT_ID('MyTable'),'MyColumn', 'UsesAnsiTrim')
More information in books online.
John
"mark mcfarlane" <markamcfarlane@.hotmail.com> wrote in message
news:uvJDsGuXFHA.2080@.TK2MSFTNGP15.phx.gbl...
> Here's a potential stumper.
> I have 20 columns in a SQL table: I am accessing the data through a view.
> I am acessing this data through SP's via ASP.NET.
> Two of the columns are identical, "nvarhar 50"
> One of these columns automatically pads extra blanks when the data is
> written, the other does not do the padding.
> The column that is doing the padding was created as a "char 50" after the
> other column was created as a nvarchar. I then changed the 'char 50' to
> an 'nvarhar 50' in Enterprise Manager.
> I read up un ANSI PADDDING option, but nothing I know of would have
> changed the padding setting of the database between these 2 changes
> (except one was colum was possibly created under SQl Server and the
> 'broken one' under MSDE...). Anyway. my documentation says that nvarhar's
> are always padded (but mine aren't for one column).
> So my questions:
> 1) Did the conversion from char to nvarchar do something screwy with the
> padding behavior of this column?
> 2) Is there any way to fix this problem in a live database? From what I
> read, youy can't change the padding nature after a field is already
> created.
> Mark McFarlane
>
|||For some reason I can't get a query to return this property. The return
value is a NULL. Maybe this is because I am using MSDE?
USE gad_projects
SELECT COLUMNPROPERTY(
OBJECT_ID('dbo.IssueTracker_Issues'),'IssueFunctio nalArea','UsesAnsiTrim')
Also, this will tell me if the column was created with null padding off, but
it wont tell me how to fix it
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3F0RquXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi
> Look at the UsesAnsiTrim setting for COLUMNPROPERTY
> COLUMNPROPERTY(OBJECT_ID('MyTable'),'MyColumn', 'UsesAnsiTrim')
> More information in books online.
> John
>
|||"mark mcfarlane" <markamcfarlane@.hotmail.com> wrote in message
news:e1pgrcwXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> For some reason I can't get a query to return this property. The return
> value is a NULL. Maybe this is because I am using MSDE?
> USE gad_projects
> SELECT COLUMNPROPERTY(
> OBJECT_ID('dbo.IssueTracker_Issues'),'IssueFunctio nalArea','UsesAnsiTrim')
> Also, this will tell me if the column was created with null padding off,
> but it wont tell me how to fix it
By default varchar and nvarchar do not pad values with spaces; conversely,
char and nchar columns are padded to length. So it would be no surprise to
find that all of your existing data is still padded after the conversion,
UPDATE mytable SET paddedcol = LTRIM(paddedcol) would fix it nicely.
If you're certain that newly inserted values are still being padded, that
would be a little surprising [to me] but none-the-less, the simple solution
is to create a new nvarchar column, copy/LTRIM the data in the existing
column to it, then drop the old col and rename the new one. Or maybe
simpler, dump it to a new table, drop, rename, end of problem.
Forest obscured by trees syndrome, maybe?
-Mark

> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e3F0RquXFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
|||Hi
A NULL value is returned for nvarchar.
Rather than changing the datatype of the existing column, you can
create a new column update it with the existing columns data and then
drop the exiting column.
John
|||> By default varchar and nvarchar do not pad values with spaces; conversely,
> char and nchar columns are padded to length. So it would be no surprise
> to find that all of your existing data is still padded after the
> conversion, UPDATE mytable SET paddedcol = LTRIM(paddedcol) would fix it
> nicely.

>If you're certain that newly inserted values are still being padded, that
>would be a little surprising [to me] but none-the-less, the simple solution
>is to create a new nvarchar column, copy/LTRIM the data in the existing
>column to it, then drop the old col and rename the new one. Or maybe
>simpler, dump it to a new table, drop, rename, end of problem.
>
Thanks Mark,
that's what i'll do, make a new column, move the data, and delete the old
column.
Newly insserted values are still being padded, which is as I expected after
reading the documentation. Once a column is set to pad, there is apparently
no way to change that, so since they were created as chars (which pad by
defult), the padding setting stuck when I later changed them to nvarchars.
|||>
> A NULL value is returned for nvarchar.
> Rather than changing the datatype of the existing column, you can
> create a new column update it with the existing columns data and then
> drop the exiting column.
Why would a NULL be returned for an nvarchar column for
SELECT COLUMNPROPERTY(
OBJECT_ID('IssueTracker_Issues'),'IssueFunctionalA rea','UsesAnsiTrim')?
Books Online says I should get a true or false and only a NULL if there was
an invalid input.
UsesAnsiTrim ANSI padding setting was ON when the table was initially
created. 1= TRUE
0= FALSE
NULL = Invalid input
|||Hi
I guess you could argue that it should always return 1 as ANSI_PADDING
is always on for nchar/nvarchar
http://msdn.microsoft.com/library/de...a-nop_9msy.asp
or that it is NULL as UsesAnsiTrim reports the ANSI_PADDING value when
the column was created and as this is ignored it would not make sense
to return a value.
http://msdn.microsoft.com/library/de...a-nop_9msy.asp
The second option seems to have been chosen, but if you disagree then
you could send in a request to SQLWish@.microsoft.com.
John
mark mcfarlane wrote:[vbcol=seagreen]
then
> Why would a NULL be returned for an nvarchar column for
> SELECT COLUMNPROPERTY(
>
OBJECT_ID('IssueTracker_Issues'),'IssueFunctionalA rea','UsesAnsiTrim')?
> Books Online says I should get a true or false and only a NULL if
there was
> an invalid input.
> UsesAnsiTrim ANSI padding setting was ON when the table was
initially
> created. 1= TRUE
> 0= FALSE
> NULL = Invalid input

Package.Execute/IDTSEvents vs DTExec

I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events, but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..

Info: 2006-09-26 14:22:27.97
Code: 0x40016041
Source: DWB02130
Description: The package is attempting to configure from the XML file "D:\SSIS\ConfigurationsDatabase.dtsConfig".
End Info
Info: 2006-09-26 14:22:28.05
Code: 0x40016040
Source: DWB02130
Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
End Info

I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.

Any help is greatly appriciated.

Thanks!
Harry

Do you set fireAgain parameter to true for events like OnInformation, OnProgress? If not, the event might not be fired again to avoid overhead associated with sending an event (that presumably nobody listens too).

You can also implement IDTSLogging interface and pass it to Execute method in addition to IDTSEvents interface.sql

Package.Execute/IDTSEvents vs DTExec

I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events, but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..

Info: 2006-09-26 14:22:27.97
Code: 0x40016041
Source: DWB02130
Description: The package is attempting to configure from the XML file "D:\SSIS\ConfigurationsDatabase.dtsConfig".
End Info
Info: 2006-09-26 14:22:28.05
Code: 0x40016040
Source: DWB02130
Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
End Info

I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.

Any help is greatly appriciated.

Thanks!
Harry

Do you set fireAgain parameter to true for events like OnInformation, OnProgress? If not, the event might not be fired again to avoid overhead associated with sending an event (that presumably nobody listens too).

You can also implement IDTSLogging interface and pass it to Execute method in addition to IDTSEvents interface.

Package wont run in a scheduled job

Hello,

I have put together a simple SSIS package that runs fine both via BIDS and via Mgt Studio.

The problem I have is that I can't get a scheduled job to run it. My scheduled job has only 1 step - and that step runs the package.

When I right click in Mgt Studio (SQL Agent) and choose "Start Job" I get this error :

Executed as user: NMR044BRASQL886\SYSTEM. ....3033.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 14:10:56 Error: 2007-01-09 14:10:56.50 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-01-09 14:10:57.04 Code: 0xC0202009 Source: B2B_Sales_Territory_Send_Back Connection manager "b2b_datamart" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". End Error Error: 2007-01-09 14:10:57.04 Code: 0xC020801C... The package execution fa... The step failed.

Can anyone help please?
I'm new to SSIS so clear+simple answers appreciated.

Thanks in advance
Mgale1

Check the ProtectionLevel proeprty of your package. My guess is that it is set to EncryptSensitiveWithUserKey. Is that correct?

This means that all passwords and such are encrypted with the username of the person that built the package hence when you run it as another user (which you are attempting to do here), it doesn't work. Read in BOL about the ProtectionLevel property to get a full understanding and then reply here with any problems.

-Jamie

|||

Hi Mgale1,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

|||

You may want to check this KB article:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/kb/918760

|||

Admin,

This topic is a good candidate for a "sticky" for the forum.

This question is asked once a day... we (the forum) could come up with a synopsis on package encryption.

|||

I always use ProtectionLevel=DontSaveSensitive. Without exception.

This actually forces you to use configurations - and that is no bad thing in my opinion.

-Jamie

Package variables

Couldn't quite find the answer I was looking for via a forum search.

I have 9 packages that are currently called by a master package - all child packages take a datetime variable from the master package which is subsequently used in the child processes.

The question I have is that if I run the master package manually from Visual Studio I can set the master package variable manually to a date of my choosing and run it, which works fine. However we will be wishing to automate the package execution at some point and want to know the best way to run the package(s) on an automated basis and have the data variable supplied to the master package.

What would be the best way to do this ?

Presently we have a variable called MasterPackage_vLoaddate which is a DateTime data type.

Any help appreciated.

You can use the /SET option of dtexec.exe to set your variable at run time.

Or, you can use expressions on that variable if you want to use something like getdate().|||

Thanks Phil..

OK.. you'll need to tell me take me thru this in a little more basic detail

Let's say I wanted to run this job at 07:00 every morning. The variable (date) should always be for the prior day.

So I am guessing i'd need to use somthing like

dateadd(day,-1,getdate())

Can I just add that to the package in VS ? Or where exactly would I use this in order to set the variable correctly. Sorry for being such a noob!!

|||Yes, you have the correct formula.

So, in the properties of the variable you wish to work with, set "EvaluateAsExpression" equal to true. Then, in the "expression" parameter, add your formula. It's that simple.

Every time the package runs, this formula will be evaluated.|||

Will,

Keep in mind that if you want the ability of running the package for any date you choose, you would need to put extra logic on this (e.g. you need to reprocess a day that is not the current day; or if for some reason the package did not run in a specifc day).

You could easily implemnet that logic with a table that keeps track of the executions dates.

Just my two cents

|||

OK...

I'll test that in the morning. I used

set "EvaluateAsExpression" equal to true

and then used the formula getdate("d",-1,getdate())

I am supposing that it will just be a straightforward job of scheduling the master package run in SQL Agent and then the other packages will be called as per expectations ?

Cheers... your reply helped

|||

Will Riley wrote:

I am supposing that it will just be a straightforward job of scheduling the master package run in SQL Agent and then the other packages will be called as per expectations ?

Yep, that should be pretty straightforward.|||

Hi Rafael,

I do already have a table that logs package execution so that in theory, the same day cannot be processed twice. Would you suggest that I derive the date variable from that table (i.e. max date + 1 i.e. something like a SQL command.... SELECT dateadd("d",1,MAX(load_dates.date)) )

If so , how would I pass that to the package ? it might be something I hold in reserve in case the above proves unworkable from Phil.

Cheers

|||

Will Riley wrote:

Hi Rafael,

I do already have a table that logs package execution so that in theory, the same day cannot be processed twice. Would you suggest that I derive the date variable from that table (i.e. max date + 1 i.e. something like a SQL command.... SELECT dateadd("d",1,MAX(load_dates.date)) )

If so , how would I pass that to the package ? it might be something I hold in reserve in case the above proves unworkable from Phil.

Cheers

My solution will work, it just won't be as flexible as Rafael's.

For the table derived option, use an Execute SQL task in the control flow to select the max(date)+1 and then store the result into your package's date variable.|||

Just one more thing....

Am I likely to run in to an issue with times?

The above solution will equate to a datetime value of something like

11/02/2007 07:00

I would rather have

11/02/2007 00:00

As there may well be issues if the time is anything other than 00:00

Is there a simple bit of SSIS "expression" that will evaluate to "yesterday" at 00:00 ?

|||

Will Riley wrote:

Just one more thing....

Am I likely to run in to an issue with times?

The above solution will equate to a datetime value of something like

11/02/2007 07:00

I would rather have

11/02/2007 00:00

As there may well be issues if the time is anything other than 00:00

Is there a simple bit of SSIS "expression" that will evaluate to "yesterday" at 00:00 ?

Put this in front of your formula: (DT_DBTIMESTAMP)(DT_DBDATE)

So you would have:
(DT_DBTIMESTAMP)(DT_DBDATE)dateadd("d",-1,getdate())

If you don't even want the 00:00 on the end, don't include the (DT_DBTIMESTAMP) cast.|||

Phil Brammer wrote:

My solution will work, it just won't be as flexible as Rafael's.

I never meant it won't work. I just wanted to add a point I thought was important to get that issue solved.

|||

Will Riley wrote:

Hi Rafael,

I do already have a table that logs package execution so that in theory, the same day cannot be processed twice. Would you suggest that I derive the date variable from that table (i.e. max date + 1 i.e. something like a SQL command.... SELECT dateadd("d",1,MAX(load_dates.date)) )

If so , how would I pass that to the package ? it might be something I hold in reserve in case the above proves unworkable from Phil.

I think the approach beeing discussed would work fine; you just need to base the logic on that table you already have (good you already have it!).

Having a logic that depends in 'today' date is asking for trouble in long long term; when things do not happen/run as expected.

|||

Rafael Salas wrote:

I never meant it won't work. I just wanted to add a point I thought was important to get that issue solved.

I agree. Will mentioned it.|||

Phil, Rafael,

Thanks both - between you you provided both the answer(s) I needed and some good advice in terms of the use/dangers of dynamic variables.

Cheers

Package from Package Sore does not work as Job

Dear All,

I try to run a Package which is placed in the Package Store via a job - but this does not work.

Some things about the package:
- The package evokes some warnings, because I do not use all columns from the datasource
- Package runs in BI Studio - log is written
- Package runs if startet from the Package Store - log is written
- Package Execution stopps immediately if started via job - without writting in the log

As hint - needless to say :-) - that the log provider is configured in all three spaces...
Does anybody now, if I could use the configured log provider from the package directly in the jobs _without_ doing the settings?

Other jobs - without the warnings - are running and placing there step results in the log.

Thanks in advice!

Cheers
Markus

Some info that may help-

Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)

|||

Darren,

sorry for this late reply - you are right!
The user did not have appropriate rights on the package store folder.

cheers,
markus

Overlapping Report Items via HTML?

Has anybody managed to get Overlapping Report Items to display properly
in Internet Explorer or some other browser? (Ignoring Microsoft's
statement that overlapping items are not supported in HTML)
Extensions or additions to the Rendering Extension, or any other
suggestions would be welcome.I also want to know has any idea about this issue!
Angi
"Sam" <samadams001@.googlemail.com>
'?:1148978623.733811.128950@.j33g2000cwa.googlegroups.com...
> Has anybody managed to get Overlapping Report Items to display properly
> in Internet Explorer or some other browser? (Ignoring Microsoft's
> statement that overlapping items are not supported in HTML)
> Extensions or additions to the Rendering Extension, or any other
> suggestions would be welcome.
>

Overlapping Permissions

I would think the following scenario should work, but it does not:
I have a table, Products, for which all users, via an NT domain group (e.g.
Domain Users) have only select permissions.
There is another group, ProductManagers, who are also members of the above
group, who need update, delete, and insert permissions. To accomplish this,
I
created a database role ProductMgmt, and added the ProductManagers to it.
This role has select, insert, update and delete permissions on the table.
The members of this group, however, get an error when attempting to delete
from the table. These members belong to both the Domain User and
ProductManagers groups.
I've also given the ProductManager group full permissions on the table. I'm
confused as to why all of this is not working, obviously I'm missing
something.
Thanks for any assistance,
TomtDoes the NT group which has only select permissions have a
deny on delete? Do any users or groups have deny set on the
table?
Permissions are cumulative but deny will take precedence.
-Sue
On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
wrote:

>I would think the following scenario should work, but it does not:
>I have a table, Products, for which all users, via an NT domain group (e.g.
>Domain Users) have only select permissions.
>There is another group, ProductManagers, who are also members of the above
>group, who need update, delete, and insert permissions. To accomplish this,
I
>created a database role ProductMgmt, and added the ProductManagers to it.
>This role has select, insert, update and delete permissions on the table.
>The members of this group, however, get an error when attempting to delete
>from the table. These members belong to both the Domain User and
>ProductManagers groups.
>I've also given the ProductManager group full permissions on the table. I'm
>confused as to why all of this is not working, obviously I'm missing
>something.
>Thanks for any assistance,
>Tomt
>|||Sue,
Thanks for your reply. No, there are no deny's on delete. I did know that
one, but am missing something...
Thanks
Tom
"Sue Hoegemeier" wrote:

> Does the NT group which has only select permissions have a
> deny on delete? Do any users or groups have deny set on the
> table?
> Permissions are cumulative but deny will take precedence.
> -Sue
> On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||And there are no other Windows groups and no other roles in
that database? Just the two roles and the two NT groups?
And members of the ProductMgmt role can select, insert and
update but not delete?
-Sue
On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>Sue,
>Thanks for your reply. No, there are no deny's on delete. I did know that
>one, but am missing something...
>Thanks
>Tom
>"Sue Hoegemeier" wrote:
>|||There's the Domain Users and two other non-NT, SQL Server accounts for web
access to the table.
There are no other roles other than the built-in roles. That group has
select, insert, update and delete permissions.
I'm going to have them try it again tomorrow, I might have overlooked
checking the delete permission, which is just due to trying to do too many
things at once...
I'll post the results tomorrow. Thanks for your assistance with this.
Tom
"Sue Hoegemeier" wrote:

> And there are no other Windows groups and no other roles in
> that database? Just the two roles and the two NT groups?
> And members of the ProductMgmt role can select, insert and
> update but not delete?
> -Sue
> On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Tom
Grant them EXECUTE permission on SP that perform DELETE/INSERT/UPDATE on
this table.
"TomT" <tomt@.tomt.com> wrote in message
news:F6DB5A9A-6ADF-4F89-89E8-9656A8962BFF@.microsoft.com...[vbcol=seagreen]
> There's the Domain Users and two other non-NT, SQL Server accounts for web
> access to the table.
> There are no other roles other than the built-in roles. That group has
> select, insert, update and delete permissions.
> I'm going to have them try it again tomorrow, I might have overlooked
> checking the delete permission, which is just due to trying to do too many
> things at once...
> I'll post the results tomorrow. Thanks for your assistance with this.
> Tom
> "Sue Hoegemeier" wrote:
>
that[vbcol=seagreen]
group (e.g.[vbcol=seagreen]
above[vbcol=seagreen]
accomplish this, I[vbcol=seagreen]
to it.[vbcol=seagreen]
table.[vbcol=seagreen]
delete[vbcol=seagreen]
table. I'm[vbcol=seagreen]|||You really should check the other permissions as well as it
could make it easier for you to determine what has been
missed. Check the select, insert and update permissions as
well.
-Sue
On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>There's the Domain Users and two other non-NT, SQL Server accounts for web
>access to the table.
>There are no other roles other than the built-in roles. That group has
>select, insert, update and delete permissions.
>I'm going to have them try it again tomorrow, I might have overlooked
>checking the delete permission, which is just due to trying to do too many
>things at once...
>I'll post the results tomorrow. Thanks for your assistance with this.
>Tom
>"Sue Hoegemeier" wrote:
>|||I checked them all, for that particular group, and still no go. I have to
grant the permissions for the Domain Users group for insert, delete, etc.
otherwise the group I really need to have this access does not.
To summarize: Two groups (NT) Domain Users, to which all users belong,
member of the public role, and ProductManagers, member of public and
ProductMgmt roles.
A user, Rod, belongs to both Domain Users and ProductManagers groups.
ProductMangers have select, insert, delete and update permissions on table;
Domain Users have Select permission only, no other permissions granted or
denied.
Database role ProductMgmt has full permissions on the table.
With the scenario above, Rod cannot delete from the table. I have to grant
delete permissions to Domain Users in order for him to be able to delete row
s
from the table.
I gather from your replies that this should work, and I have set it up
correctly, is that right?
Thanks,
Tom
"Sue Hoegemeier" wrote:

> You really should check the other permissions as well as it
> could make it easier for you to determine what has been
> missed. Check the select, insert and update permissions as
> well.
> -Sue
> On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Yes it will work so you are still missing something. I can't
reproduce the issue rebuilding with the same groups and
roles - it works fine on my end.
Try using xp_logininfo to determine the group membership and
dsiplay information on the Product Managers group at the
Windows level.
-Sue
On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>I checked them all, for that particular group, and still no go. I have to
>grant the permissions for the Domain Users group for insert, delete, etc.
>otherwise the group I really need to have this access does not.
>To summarize: Two groups (NT) Domain Users, to which all users belong,
>member of the public role, and ProductManagers, member of public and
>ProductMgmt roles.
>A user, Rod, belongs to both Domain Users and ProductManagers groups.
>ProductMangers have select, insert, delete and update permissions on table;
>Domain Users have Select permission only, no other permissions granted or
>denied.
>Database role ProductMgmt has full permissions on the table.
>With the scenario above, Rod cannot delete from the table. I have to grant
>delete permissions to Domain Users in order for him to be able to delete ro
ws
>from the table.
>I gather from your replies that this should work, and I have set it up
>correctly, is that right?
>Thanks,
>Tom
>"Sue Hoegemeier" wrote:
>|||Sue,
I found the problem, the person who set up the NT user group
ProductManagers, set it up as a distribution group, not a security group.
Once that was fixed, everything works correctly.
BTW, I assume this would still work without the database role, i.e., just
the ProductManager group having the delete, etc. permissions assigned,
correct?
thanks for your help and patience,
Tom
"Sue Hoegemeier" wrote:

> Yes it will work so you are still missing something. I can't
> reproduce the issue rebuilding with the same groups and
> roles - it works fine on my end.
> Try using xp_logininfo to determine the group membership and
> dsiplay information on the Product Managers group at the
> Windows level.
> -Sue
> On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>

Overcoming orphans

How do you restore master to a new database and not orphan users in the
process?
Message posted via http://www.droptable.com
Look up sp_change_users_login in BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5d589125f94ca@.uwe...
> How do you restore master to a new database and not orphan users in the
> process?
> --
> Message posted via http://www.droptable.com

Overcoming orphans

How do you restore master to a new database and not orphan users in the
process?
Message posted via http://www.droptable.comLook up sp_change_users_login in BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5d589125f94ca@.uwe...
> How do you restore master to a new database and not orphan users in the
> process?
> --
> Message posted via http://www.droptable.com

Overcoming orphans

How do you restore master to a new database and not orphan users in the
process?
--
Message posted via http://www.sqlmonster.comLook up sp_change_users_login in BOL.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:5d589125f94ca@.uwe...
> How do you restore master to a new database and not orphan users in the
> process?
> --
> Message posted via http://www.sqlmonster.com