Permissions problem (I think)

O.k. Here's the background on my situation. We've got an Access program
running against a SQL 2000 DB. The program was created using Access 2003.
The user that runs this program had Access 2000 on their PC, which was
upgraded to Access 2003. One of the first steps the program does is copy
data from 2 external files into 2 seperate tables(1 table for each file).
This is done with the TransferText command. When the user runs the program,
a new table with the same name as the original is created with the user id
shown as the owner. The data is then copied into this new table instead of
the one already in the DB. Thus, when the program tries to read the origina
l
table it fails b/c it is empty. This happens ONLY for this user. If anyone
else logs in on the users PC, the program works fine. I've tried changing
every permissions option available, including giving the user FULL rights an
d
even making them an enterprise admin. Nothing seems to work. All security
is the same as it was before we upgraded the user to Access 2003, and it was
all working before. Any ideas'If the following is the issue and only happens for that
user:
"When the user runs the program, a new table with the same
name as the original is created with the user id shown as
the owner"
then it sounds like the user is in different database roles
than the other user. You didn't say what authentication
method is used or how the access program is connecting, if
it's a project or an mdb, etc. You may want to try running
profiler, capture the Login, user info and compare it when
others run the same routine. Then look at the differences in
database role, server role membership.
-Sue
On Thu, 25 Aug 2005 14:26:09 -0700, "CD"
<CD@.discussions.microsoft.com> wrote:

>O.k. Here's the background on my situation. We've got an Access program
>running against a SQL 2000 DB. The program was created using Access 2003.
>The user that runs this program had Access 2000 on their PC, which was
>upgraded to Access 2003. One of the first steps the program does is copy
>data from 2 external files into 2 seperate tables(1 table for each file).
>This is done with the TransferText command. When the user runs the program
,
>a new table with the same name as the original is created with the user id
>shown as the owner. The data is then copied into this new table instead of
>the one already in the DB. Thus, when the program tries to read the origin
al
>table it fails b/c it is empty. This happens ONLY for this user. If anyon
e
>else logs in on the users PC, the program works fine. I've tried changing
>every permissions option available, including giving the user FULL rights a
nd
>even making them an enterprise admin. Nothing seems to work. All security
>is the same as it was before we upgraded the user to Access 2003, and it wa
s
>all working before. Any ideas'

permissions problem

I have what I'm sure is a simple problem with setting permissions on some
objects. Here's the situation... I have one database (Central) that I want
to allow inserts to a table (Queue) on via a stored procedure. That stored
procedure I am storing in the master database so that any of the other
databases I have running can execute it.
I am doing the insert in the procedure by referencing "INSERT INTO
Central.dbo.Queue..." That procedure is set to allow EXEC permissions by
the guest and public roles.
I have logins from the other databases 'Permit in Database Role' set to
public for the Queue table. The error I am getting is "INSERT permission
denied on object 'Queue', database 'Central', owner 'dbo'.
What am I missing'> What am I missing'
If your stored procedure is owned by 'dbo', then your Central database needs
to also be owned by 'sa' so that the ownership chain is unbroken. Also, if
you are running SQL 2000 SP3+, you'll need to enable cross-database chaining
('db chaining' database option) un the Central database.
Note that you should enable cross-database chaining in an sa-owned database
when only symin role members can create dbo-owned objects in that
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Random" <cipherlad@.hotmail.com> wrote in message
news:u2otNyjOFHA.244@.TK2MSFTNGP12.phx.gbl...
>I have what I'm sure is a simple problem with setting permissions on some
>objects. Here's the situation... I have one database (Central) that I want
>to allow inserts to a table (Queue) on via a stored procedure. That stored
>procedure I am storing in the master database so that any of the other
>databases I have running can execute it.
> I am doing the insert in the procedure by referencing "INSERT INTO
> Central.dbo.Queue..." That procedure is set to allow EXEC permissions by
> the guest and public roles.
> I have logins from the other databases 'Permit in Database Role' set to
> public for the Queue table. The error I am getting is "INSERT permission
> denied on object 'Queue', database 'Central', owner 'dbo'.
> What am I missing'
>|||Thank you for replying. The owner on the Central database is 'sa'.
Where is the option for 'db chaining' set? I can't locate it in books
online.
I have also ensured that only symin members can create dbo-owned objects
in the database.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ecJw5QkOFHA.2708@.tk2msftngp13.phx.gbl...
> If your stored procedure is owned by 'dbo', then your Central database
> needs to also be owned by 'sa' so that the ownership chain is unbroken.
> Also, if you are running SQL 2000 SP3+, you'll need to enable
> cross-database chaining ('db chaining' database option) un the Central
> database.
> Note that you should enable cross-database chaining in an sa-owned
> database when only symin role members can create dbo-owned objects in
> that database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:u2otNyjOFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||The 'db chaining' database option is aka cross-database chaining. One
method to turn it on is with sp_dboption via Query Analyzer:
EXEC sp_dboption 'Central' ,'db chaining', true
Hope this helps.
Dan Guzman
SQL Server MVP
"Random" <cipherlad@.hotmail.com> wrote in message
news:OErPV0rOFHA.3444@.tk2msftngp13.phx.gbl...
> Thank you for replying. The owner on the Central database is 'sa'.
> Where is the option for 'db chaining' set? I can't locate it in books
> online.
> I have also ensured that only symin members can create dbo-owned
> objects in the database.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:ecJw5QkOFHA.2708@.tk2msftngp13.phx.gbl...
>|||FYI, setting the 'db_chaining' option did the trick. I found the
documentation and read about the security implications, so I've got it
covered now. Thanks for the tips!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OY7$75vOFHA.2520@.tk2msftngp13.phx.gbl...
> The 'db chaining' database option is aka cross-database chaining. One
> method to turn it on is with sp_dboption via Query Analyzer:
> EXEC sp_dboption 'Central' ,'db chaining', true
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:OErPV0rOFHA.3444@.tk2msftngp13.phx.gbl...
>

Permission tracking

Hi all,
i'm facing the following situation: user rights must be granted for a very
short period of time, so that the user does his insert/update/delete job.
The problem is that there are many users who have requests and giving &
revoking permissions are taking a lot of time. Did anybody implemented an
automated tracking system, and if yes, could you give me a hint please? A
good way to implement permission tracking would be triggers but unluckily,
in Sql 2000, they can't be used on system tables...:-(
--
Tudor Sofron
MCSE, MCSA
Ipsos- NMRTudot
I am not sure what did you mean?
Do you want the users to be able INSERT/UPDATE/DELETE operations for a short
time?
What is a short time ( one an hour, two minutes) ?
I suggest you using STORED PROCEDURES for security reasons. Don't grant
access on underlying tables ,instead grant EXECUTE permissions for the users
on stored procedures that they need to be run.
"Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
news:uc9kgMXxEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> i'm facing the following situation: user rights must be granted for a very
> short period of time, so that the user does his insert/update/delete job.
> The problem is that there are many users who have requests and giving &
> revoking permissions are taking a lot of time. Did anybody implemented an
> automated tracking system, and if yes, could you give me a hint please? A
> good way to implement permission tracking would be triggers but unluckily,
> in Sql 2000, they can't be used on system tables...:-(
> --
> Tudor Sofron
> MCSE, MCSA
> Ipsos- NMR
>
>|||Well...
this is the table i've created:
CREATE TABLE [User_RightsGranted] (
[User_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[User_ID] [smallint] NULL ,
[User_SID] [varbinary] (85) NULL ,
[DB_Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[Object_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[Db_Owner] [tinyint] NULL ,
[Data_Reader] [tinyint] NULL ,
[Data_Writer] [tinyint] NULL ,
[Exec] [tinyint] NULL ,
[Select] [tinyint] NULL ,
[Insert] [tinyint] NULL ,
[Update] [tinyint] NULL ,
[Delete] [tinyint] NULL ,
[DateStart] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_DateS
tart]
DEFAULT (getdate()),
[DateEnd] [datetime] NULL ,
[OpDate] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_OpDate]
DEFAULT
(getdate()),
[OpUser] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL
CONSTRAINT [DF_User_Rights_OpUser] DEFAULT (suser_sname() + '.' +
host_name())
) ON [PRIMARY]
GO
The column names are pretty explicit so it's no need to explain their
function. The problem I have is that I can't automatize the whole process,
so I have to complete the table manually. So...did anybody faced such
problems, and if yes, how did you solved them? See my comments to your post
below...
Thanks,
Tudor Sofron
MCSE, MCSA
Ipsos- NMR
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eUVFySXxEHA.2040@.tk2msftngp13.phx.gbl...
> Tudot
> I am not sure what did you mean?
> Do you want the users to be able INSERT/UPDATE/DELETE operations for a
short
> time?
yes. I grant users INSERT/UPDATE/DELETE rights and complete the above table.
> What is a short time ( one an hour, two minutes) ?
about 30 minutes, after that I revoke the granted permissions...but I have
to do that manually...and update a similar table...

> I suggest you using STORED PROCEDURES for security reasons. Don't grant
> access on underlying tables ,instead grant EXECUTE permissions for the
users
> on stored procedures that they need to be run.
well...it's not that easy to implement the use of sp's...
>
>
> "Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
> news:uc9kgMXxEHA.1988@.TK2MSFTNGP12.phx.gbl...
very[vbcol=seagreen]
job.[vbcol=seagreen]
an[vbcol=seagreen]
A[vbcol=seagreen]
unluckily,[vbcol=seagreen]
>|||Tudor
I am sorry but the table looks like a mess.
There is no primary key, lots of colums does allow NULL's

> The problem I have is that I can't automatize the whole process,
> so I have to complete the table manually
You mean that you would like to insert into the table all DML that users do?
If so, create a trigger on this table and start to manipuilate with DELETED
and INSERTED virtual tables
In my opinion I'd create an AUDIT table that will be gathering all info
about new/old columns
Something like that
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
news:%23QH8akXxEHA.3896@.TK2MSFTNGP10.phx.gbl...
> Well...
> this is the table i've created:
> CREATE TABLE [User_RightsGranted] (
> [User_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

,
> [User_ID] [smallint] NULL ,
> [User_SID] [varbinary] (85) NULL ,
> [DB_Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
> [Object_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [Db_Owner] [tinyint] NULL ,
> [Data_Reader] [tinyint] NULL ,
> [Data_Writer] [tinyint] NULL ,
> [Exec] [tinyint] NULL ,
> [Select] [tinyint] NULL ,
> [Insert] [tinyint] NULL ,
> [Update] [tinyint] NULL ,
> [Delete] [tinyint] NULL ,
> [DateStart] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_Da
teStart]
> DEFAULT (getdate()),
> [DateEnd] [datetime] NULL ,
> [OpDate] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_OpDat
e] DEFAULT
> (getdate()),
> [OpUser] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> CONSTRAINT [DF_User_Rights_OpUser] DEFAULT (suser_sname() + '.' +
> host_name())
> ) ON [PRIMARY]
> GO
> The column names are pretty explicit so it's no need to explain their
> function. The problem I have is that I can't automatize the whole process,
> so I have to complete the table manually. So...did anybody faced such
> problems, and if yes, how did you solved them? See my comments to your
post
> below...
> Thanks,
> Tudor Sofron
> MCSE, MCSA
> Ipsos- NMR
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eUVFySXxEHA.2040@.tk2msftngp13.phx.gbl...
> short
> yes. I grant users INSERT/UPDATE/DELETE rights and complete the above
table.
> about 30 minutes, after that I revoke the granted permissions...but I have
> to do that manually...and update a similar table...
>
grant[vbcol=seagreen]
> users
> well...it's not that easy to implement the use of sp's...
> very
> job.
&[vbcol=seagreen]
> an
please?[vbcol=seagreen]
> A
> unluckily,
>|||well...the table design is in 'development phase' :-)...but i hope that soon
this will be done.
Tudor Sofron
MCSE, MCSA
Ipsos- NMR
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuqlkwXxEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Tudor
> I am sorry but the table looks like a mess.
> There is no primary key, lots of colums does allow NULL's
>
>
> You mean that you would like to insert into the table all DML that users
do?
> If so, create a trigger on this table and start to manipuilate with
DELETED
> and INSERTED virtual tables
> In my opinion I'd create an AUDIT table that will be gathering all info
> about new/old columns
> Something like that
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
>
>
> "Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
> news:%23QH8akXxEHA.3896@.TK2MSFTNGP10.phx.gbl...
NULL[vbcol=seagreen]
> ,
,[vbcol=seagreen]
,[vbcol=seagreen]
process,[vbcol=seagreen]
> post
> table.
have[vbcol=seagreen]
> grant
a[vbcol=seagreen]
giving[vbcol=seagreen]
> &
implemented[vbcol=seagreen]
> please?
>