Connect Access to SQL Server

[Jim Doherty]

Great info. I'm doing some research into it now... Chances are I'll take you up on the remote access, as I'm still learning to navegate SQL Server Management Studio.

You mention the "unique table" the forms property, in Access. I defined that but the recordset is still not editable. Do I have to look at how the View is constructed?

Yes you do I am afraid......... and in many ways you can end up with a completely different view/query in SQL server when compared to the way it was, or might have been in Access, different beast so different requirements. Its going to sound dumb I know but also the simplest things get overlooked here.

For instance if you have a two table join and have defined the Unique table that allows the edits and find you 'still' you cannot enter records you had better check that you have your primary key set, because without that, you cannot edit anyway!! The times I have come across that simple build mistake as the cause for the unavailable edits is quite a lot actually.

To guide you through this would potentially wear my fingers away here its much easier seeing it done visually hence the remote access thing i spoke off. I am sure you'll say to yourself "of course" when you see for yourself rather than typing back and forth with all the questions that one could quite easily raise on this.

Jim :)

[RAG2007]

Great, let's do remote access if you can. I'm in NYC, and could do anytime from 8:30 to 4:30 pm. It's probably best to communicate this info via personal email, rather than the forum. My email is: rghertne@health .nyc.gov.

Robin

Yes you do I am afraid......... and in many ways you can end up with a completely different view/query in SQL server when compared to the way it was, or might have been in Access, different beast so different requirements. Its going to sound dumb I know but also the simplest things get overlooked here.

For instance if you have a two table join and have defined the Unique table that allows the edits and find you 'still' you cannot enter records you had better check that you have your primary key set, because without that, you cannot edit anyway!! The times I have come across that simple build mistake as the cause for the unavailable edits is quite a lot actually.

To guide you through this would potentially wear my fingers away here its much easier seeing it done visually hence the remote access thing i spoke off. I am sure you'll say to yourself "of course" when you see for yourself rather than typing back and forth with all the questions that one could quite easily raise on this.

Jim :)

[RAG2007]

Thanks Jim for the help. I realized I didn't get your email, I wanted to ask you a quick question about udfs, I'm getting a "is not a recognized built-in function name" error. Maybe you could email me the sql for one of your views that incorporates a udf that we looked at?

Thanks!
Robin

[Jim Doherty]

Thanks Jim for the help. I realized I didn't get your email, I wanted to ask you a quick question about udfs, I'm getting a "is not a recognized built-in function name" error. Maybe you could email me the sql for one of your views that incorporates a udf that we looked at?

Thanks!
Robin

Hi RAG,

To replicate an useage of a UDF embedded in an SQL view have a look at this a thread that I contributed to that provides for a working example that you can follow and replicate for yourself

http://www.thescripts.com/forum/thread766999.html

Regards

Jim :)

[RAG2007]

OK, so I got the domain name from my IT department, and my server is in mixed mode. I want to add my 20 users through Windows Authentication. I'm in SQL server 2005, management studio. When I go to create a new login, for "Login Name", I would enter DOMAIN\username for each person (where username is their Windows username), select their default database, and then go to that database and add them as a user and define their role? Or do I just enter the domain name DOMAIN\?

no you don't need to create each user. you just have to point which domain should the sql server get the account credentials from. you still have to grant all those users (DOMAIN\uname) with the necessary rights....

-- ck

[Jim Doherty]

OK, so I got the domain name from my IT department, and my server is in mixed mode. I want to add my 20 users through Windows Authentication. I'm in SQL server 2005, management studio. When I go to create a new login, for "Login Name", I would enter DOMAIN\username for each person (where username is their Windows username), select their default database, and then go to that database and add them as a user and define their role? Or do I just enter the domain name DOMAIN\?

When you add your user..... look at it from this perspective Your user now exists as a user on the SQL server


Your users will be listed under 'security' node 'logins' sub node. Once a user is listed there you define what databases the user can have access to.

So create your users first, then grant them access to the databases you intend them to have access to. (THAT WAY ROUND) as opposed to going to a specific database and adding a user that way. This is relevant when assigning permissions for 'GRANT ACCESS' maybe for multiple databases.

Double clicking on each user name brings up the dialogue where you will see then that it is a simple question of ticking which databases they can have access to and under what role they will be assigned. Each user can if required have more than one role but be careful if you do this not to usurp or undermine the persmission of one role by assigning to another having permissions at a higher or lower level.


Create your relevant roles, define permissions for the role, then add your users to the role. So.... if you have a role of READWRITER the role having specific permissions to 'do' or 'not do' something with the objects or data then your users inherit those permissions for the role because they are in that role.

You can go adding a 'windows group' as a user but all users in the windows group have the permissions assigned to the windows group. OK for a generic entry where permissions are not vitallly critical because you control them in the app maybe........bu t to be more precise on this and also to have advantage of functions such as system_user (same as currentuser in Access where you might audit who does what with the data based on their login credentials.

You might want to just therefore simply tighten up by adding your users to each of your defined roles and control your permissions through roles not each user...much easier than assigning permissions to EACH individual user (you,d be there till next new year ticking boxes otherwise).

Trust this makes sense to you. I'm sure you'll play with it until you get the hang of how it works etc etc


Jim :)

[ck9663]

I'd go with Jim with this one with respect to defining roles. It's one way to efficiently manage your sql server. Before you do everything, think of the roles of your users (not the user themselves). Try putting it in a worksheet so that you can properly match the user with their respective role. Then you can add the user to their respective role properly.

Good luck.

-- CK

[RAG2007]

That's sound advice. So I'll add my users one at a time and ascribe them roles. Roles seem to be fixed, though, correct? I cannot add a new one. Doing a little research, I read that by playing around with schemas one could control which db objects users have access to, which I could see coming in handy at some point. But defining the permissions of what a user can and cannot do (eg edit) is done through roles, correct?

My problem now is, though, it seems I don't have permission to create users or assign roles! Argh, foiled by government bureaucracy. I guess I'll have to talk to IT and give them a list of users, and have them do the legwork.

Thanks guys for the help, slowly but surely I'll make it.

[ck9663]

I think you can CREATE ROLE

-- CK

[RAG2007]

Met with my DBA. They wouldn't give me permissions to add users or create roles, but suggested that in the front end I control that using windows system functions to determine who the user is. Now I'm searching for the vba code that will enable me to do that, but haven't found anything yet. Anyone know how to access that?

[Jim Doherty]

Met with my DBA. They wouldn't give me permissions to add users or create roles, but suggested that in the front end I control that using windows system functions to determine who the user is. Now I'm searching for the vba code that will enable me to do that, but haven't found anything yet. Anyone know how to access that?

Sounds like someone cannot be bothered with the overhead of administering a server properly to me... I don't personally do needless workarounds so maybe some else can help.

Regards

Jim :)

[ricka49]

this may be of some help.

http://www.mssqltips.c om/tip.asp?tip=143 7

[ck9663]

Met with my DBA. They wouldn't give me permissions to add users or create roles, but suggested that in the front end I control that using windows system functions to determine who the user is. Now I'm searching for the vba code that will enable me to do that, but haven't found anything yet. Anyone know how to access that?

Your DBA is saying, you login using the username as setup on your apps or on your ODBC. On your front-end, you have to grab the username of the user. Login on your sql-server on a generic (could be your) user/pass. You now have to check if that username that you grab is authorized to use your system, maybe against a table with all authorized users and their password. If they are, you allow them to use it. If not, you prompt an error.

You don't have to request their username, maybe just their password. If you go to Command Line and type SET <Enter>, you'll see an environmental variable called USERNAME and USERDOMAIN. You can just use that. How to get those values? Well that would depend on your apps.

Happy coding.

-- CK