SQl Server temp tables (SQL2005, Access 2003)

Topic

New Reply

I have an Access project that front-ends to a SQL Server database. I have multiple users. Some information I want to store in a temp table so each user sees something different. I can create the temp table (with # as the first character of the name). But I want the temp table to be used in a view so that each user gets a different set of filtered data. I could probably have done this in the days when you could create views right in an Access Project. But now I am stuck. It seems that views in SQL Server do not allow temp tables. They do allow functions but they don’t allow temp tables either. They don’t allow stored procedures which does allow temp tables. Can anyone give me a clue if it is possible to use data in a temp table in a view – somehow?

Thanks.

Reply | Quote

Replies

Graeme

A suggestion – depending on the amount of data you have for each user, you could create a permanent SQL Server table to store all the user data with a column UserID. You could then have a view on this table, and a sproc to call it with a parameter of UserID, so that each user would only see their own data.

HTH

Nick

Reply | Quote

Thanks Nick. I have tried using the SQL user and this works fine until I export the database to another server then it has lots of issues. So I am managing the user logon myself. So I wanted to store the user name in the temp table so only that user would see it but it would be used to filter all data. How can I store the user in a permanent table and have the sproc know which one to pick? As you might guess, I am far from being an expert in matters SQL Server! I appreciate your help.

Graeme.

Reply | Quote

Graeme

Without knowing more details of your setup, it’s difficult to say exactly which would be the best way forward. If the users are getting into SQL Server via your userID, then you could create the table to store the data (including the column UserID), create a view on that table, and then create a sproc with a parameter of UserID to select from the view.

Nick

Reply | Quote

Thanks Nick. OK. Here is a thumbnail sketch. I have found SQL Server security great until I ship the database elsewhere (where they don’t have SQL Server DBA expertise on hand) and then it gets all very messy. So I decided I would manage the security myself. They need to create the users in SQL Server themselves and they can do this. I allow the front end to log on using windows authentication and I manage the log on process. I have a list of users in a user table that I maintain. This also contains their password (encrypted). When they log on I check their user ID exists and the encrypted password they enter matches the encrypted one stored against that ID. I have another table that the system manager maintains that determines what each user can see. So I need to record the ID of the person who logged on and use it in views on the data so they only see what they need to see. That’s why I went for temp tables – the front end will only see the user who logged on to this instance in the table (even though others may also be logged on and their front end will only see them in the table). But then I need to use this temp table in vies to filter data. That is where I hit the wall. Is that any more helpful?

Thanks for your perseverance.

Graeme.

Reply | Quote

First, create a function the calls the user name

CREATE FUNCTION fnGetUserName()
RETURNS varchar(16) AS
BEGIN
RETURN SUBSTRING(SYSTEM_USER, CHARINDEX (” , SYSTEM_USER)+1, 50)
END

Then in the UserID Criteria put = dbo.fnGetUserName()

I use this on several reports to only allow that user to see their information and not someone elses. I have a table that stores the user name and which information they are allowed to see.

Reply | Quote

Thanks Winston. I did something like this but when I shipped the database to the client (including users for whom I had set up permissions since they had no real DBA assistance), it all fell apart and I could not get it to recognise the SQL users. That is why I have resorted to managing logins by myself. I think it might have something to do with the internal user ID that SQL creates but I dn’t really know.

Thanks for your help.

Graeme.

Reply | Quote

I’m a bit confused – it sounds like you are trying to use both SQL Server and your own security system. Are your remote users logging into the network (or the server) using Windows Authentication? If so, there are some fairly powerful tools you can use to create views based on the user name, and then connect the front-end dynamically to those views so they only see their data. I think that might be just as expeditious as trying to do things on the fly. On the other hand if you have a large number of tables involved, that get’s complex. Another strategy you might consider is to use Pass-Through queries to send the request to the server for just their data. In that case you could simply use Access User Security to force a login, and then use the CurrentUser function to construct the pass-through query on the fly. If this doesn’t make any sense, I’ll back up and fill in some of the details.

Reply | Quote

Wendell,

Sorry I have been a bit tardy – been interstate. I think you have hit the nail right on the head. I have given this some thought and have decided that the best strategy is to make SQL Server security work! I think my problem started when I created users in my DB and then shipped them with the DB. Everything worked fine at my end but it all did not work very well at the other end (I expect internal ID may have been the problem). So I will remove all users from the DB before I ship it and have them create them at the other ends and see how that works. Hopefully this will be more successful. Thanks for your thoughts.

Cheers.

Graeme.

Reply | Quote

Well, I’ve just had a week of hard lessons on SQL Server 2005 and it’s users and logins. We had a situation where the network guy upgraded a client’s server, changing the domain name, and the name of the server running SQL Server, then simply attached to the existing SQL Server 2000 files, and though everything should be OK. Unfortuantely, the old logins still existed, and no new ones had been added. Now the database claims it doesn’t have a valid owner, and all other sorts of weird things are going on. And it does appear that the internal ID numbers are a big piece of the problem. Let us know how you get on, and my apologies for the delay, as I’ve been travelling as well.

Reply | Quote

Thanks for this Wendell. No apologies required – I also have been a bit too busy to spend a lot of time on the problem. I am glad to see that it is not just me who is having a problem here. But so far I have not found a solution that I think will work satisfactorily and I am coming back to the point of view that I should go with SQL Server authentication. But prior to shipping the database, I expect that I will have to delete the users. That probably won’t be an option for you though. I believe that there is a built in function to realign internal IDs. It didn’t work for me (and unfortunately I can’t remember what it was) but it might help you if you can find it.

Thanks.

Graeme.

Reply | Quote

Thanks – I’ll research the internal ID realignment issue.

Reply | Quote