Permissions

We use an SQL backend ERP system on our Windows Small
Business Server 2k network.
The problem we have is this:
Certain parts of the ERP don't work when logged in as
Domain users, unless they have Domain Admin rights.
Here is the scenario
Pre-Requisites
---
Windows requires username and password
ERP system requires username and password
---
User A (Domain Administrator)
User B (Domain User)
1 - User A logs into PC1 (Windows2K/XP). He then then logs
into the ERP system. He can do everything he wishes as he
has full domain admin rights.
2 - After user A is finished with the ERP system he logs
out of the ERP system but leaves PC1 logged on. User B now
opens the ERP system and logs in with his username and
password. He can complete any task he attempts.
THe PC is now logged off.
3 - User B now logs into Windows2K/XP on PC1 with his
WIndows username and password and then logs into the ERP
system. He can access all parts of the ERP as before but
some screens fail to open/initilaize.
He now logs off of the ERP in order that User A can log on
but leaves Windows logged on.
User A logs onto the ERP system and attempts to complete
certain tasks. He can't seem to comlete them now either
(Same taks).
PC logged off
4 - Give User B admin rights and he can now achieve part 1
of this scenario. Obviously it isn't appropriate to give
every user on our network full Admin rights but does
anyone know what could be happening here and where best to
look for solutions? I.e Windows or SQL.It appears that
1. The ERP may be using trusted connections. That would explain why things
can or can not be done based on who logged into the OS.
2. Also there may be some issues related to connection pooling... When User
B logs into the OS and the ERP, then logs out of the ERP, the ERP continues
to behave as if userB was logged in... Either the ERP is somehow keeping a
connection open or perhaps the connection is not being re-initilized each
time it is returned to the connection pool... Check the connection pool
settings ( as per he ERP system documentation) and see if that helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert" <anonymous@.discussions.microsoft.com> wrote in message
news:22d0901c45db5$032581f0$a501280a@.phx.gbl...
> We use an SQL backend ERP system on our Windows Small
> Business Server 2k network.
> The problem we have is this:
> Certain parts of the ERP don't work when logged in as
> Domain users, unless they have Domain Admin rights.
> Here is the scenario
> Pre-Requisites
> ---
> Windows requires username and password
> ERP system requires username and password
> ---
> User A (Domain Administrator)
> User B (Domain User)
> 1 - User A logs into PC1 (Windows2K/XP). He then then logs
> into the ERP system. He can do everything he wishes as he
> has full domain admin rights.
> 2 - After user A is finished with the ERP system he logs
> out of the ERP system but leaves PC1 logged on. User B now
> opens the ERP system and logs in with his username and
> password. He can complete any task he attempts.
> THe PC is now logged off.
> 3 - User B now logs into Windows2K/XP on PC1 with his
> WIndows username and password and then logs into the ERP
> system. He can access all parts of the ERP as before but
> some screens fail to open/initilaize.
> He now logs off of the ERP in order that User A can log on
> but leaves Windows logged on.
> User A logs onto the ERP system and attempts to complete
> certain tasks. He can't seem to comlete them now either
> (Same taks).
> PC logged off
> 4 - Give User B admin rights and he can now achieve part 1
> of this scenario. Obviously it isn't appropriate to give
> every user on our network full Admin rights but does
> anyone know what could be happening here and where best to
> look for solutions? I.e Windows or SQL.|||One last thing i forgot to mention was that we had to
change a network card and driver on the server and before
this the parts of the ERP which do not function proplery
now worked prior to the new card/driver being installed.
Any advice/assistance in this matter greatly appreciated
>--Original Message--
>It appears that
>1. The ERP may be using trusted connections. That would
explain why things
>can or can not be done based on who logged into the OS.
>2. Also there may be some issues related to connection
pooling... When User
>B logs into the OS and the ERP, then logs out of the ERP,
the ERP continues
>to behave as if userB was logged in... Either the ERP is
somehow keeping a
>connection open or perhaps the connection is not being re-
initilized each
>time it is returned to the connection pool... Check the
connection pool
>settings ( as per he ERP system documentation) and see if
that helps...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Robert" <anonymous@.discussions.microsoft.com> wrote in
message
>news:22d0901c45db5$032581f0$a501280a@.phx.gbl...
>> We use an SQL backend ERP system on our Windows Small
>> Business Server 2k network.
>> The problem we have is this:
>> Certain parts of the ERP don't work when logged in as
>> Domain users, unless they have Domain Admin rights.
>> Here is the scenario
>> Pre-Requisites
>> ---
--
>> Windows requires username and password
>> ERP system requires username and password
>> ---
--
>> User A (Domain Administrator)
>> User B (Domain User)
>> 1 - User A logs into PC1 (Windows2K/XP). He then then
logs
>> into the ERP system. He can do everything he wishes as
he
>> has full domain admin rights.
>> 2 - After user A is finished with the ERP system he logs
>> out of the ERP system but leaves PC1 logged on. User B
now
>> opens the ERP system and logs in with his username and
>> password. He can complete any task he attempts.
>> THe PC is now logged off.
>> 3 - User B now logs into Windows2K/XP on PC1 with his
>> WIndows username and password and then logs into the ERP
>> system. He can access all parts of the ERP as before but
>> some screens fail to open/initilaize.
>> He now logs off of the ERP in order that User A can log
on
>> but leaves Windows logged on.
>> User A logs onto the ERP system and attempts to complete
>> certain tasks. He can't seem to comlete them now either
>> (Same taks).
>> PC logged off
>> 4 - Give User B admin rights and he can now achieve
part 1
>> of this scenario. Obviously it isn't appropriate to give
>> every user on our network full Admin rights but does
>> anyone know what could be happening here and where best
to
>> look for solutions? I.e Windows or SQL.
>
>.
>

Permission to use table variable?

I am trying to get around business logic that does not give the user permissions to create any temp tables or tables.

If a user falls into this category, will they still be allowed to create "table variables"? (Declare @.x table (i int)

...I'm obviously trying to find a way around this permission issue.

Thanks... :DYour question sounds strange...Why do you need users to have CREATE TABLE permissions? And creating temporary tables permission is transcendent from the default database security settings of TEMPDB and PUBLIC role...Unless whoever setup your business rules messed it up?|||I definitely need to clarify...I am writing stored procedures that require the use of temp tables or some sort of temporary storage for several result sets.

So I am investigating using table variables as another option because my stored procedures will not be giving my DBA's "blessing"...:)

He wants to eliminate all use of temp tables and he'll have a hissy fit if I have "create table" anywhere in my code. :) He also stated that the users are not allowed to create tables. I am not sure if that's how he set it up but that's what I was told.

I just want to make sure I won't have any issues with permissions he might have set up which is why I am looking at using table variables instead.

The "table" itself will have two columns and probably 100 rows (from the result sets).

My specific question would be, when initializing a table variable, is it treated just like any other variable?

Or is there potential that I can't declare a table variable if I am also not allowed to create temp tables?

Hope thats some what clearer...thanks :o|||2-column 100-row? Well, I guess your "DBA" doesn't trust your code ;)

We can trick your DBAby avoiding CREATE statements all together:
exec sp_addlogin 'test1', 'test1'
exec sp_adduser 'test1'
go
create table test (f1 int null, f2 int null)
insert test select 1,1 union select 2,2
go
create procedure sp_test2 as select * into #tmp from dbo.test; select * from #tmp
go
grant execute on sp_test2 to public
go
setuser 'test1'
exec sp_test2
setuser
go
drop procedure sp_test2
drop table test
go
exec sp_dropuser 'test1'
exec sp_droplogin 'test1'
go|||sigh...how I wish I could do something like this :) He will have to see this code as well and since there is a create table statement in it, I dont think it'll go through :)

I have a test database that I can run this on but I dont think it'll go to production.

Is it a bad idea to use a table variable to hold a two column 100 row set of data?|||Hey, if creating temp tables is the only thing your DBA is worried about, - I'd try to look for a replacement, or a better job. With memory tables (variable tables, SQL arrays, whatever) I can kill your server even faster, why bother with tempdb when you can get the brain of the machine much faster?
declare @.tbl table (col1 int null, col2 int null)
declare @.cnt int
select @.cnt = count(*) from @.tbl
while (case when (0>=@.cnt) then 1 else @.cnt end) >= 1 or /*should be AND */ @.cnt < 100 begin
insert @.tbl select (select count(*)-1 from @.tbl where col2-1 > col1-2),
(select count(*)+1 from @.tbl where col2+1 < col1+2)
select @.cnt = count(*) from @.tbl
end