ASP Access to SQL SERVER change

[Roland Hall]

: Thanks to all who contributed. After a week of struggle I am finally where
I
: needed to be 2 weeks ago. At least I finally have a smile on my face.
:
: It is finally working in VB and ASP. Here is what I did. I created SQL
login
: accounts and re-booted system. They did not work. the sa account did not
: even work. I then changed Authentication back to Windows Only(I did not
: change any account information). Re-booted again. Changed back to SQL
Server
: and Windows Authentication and re-booted again.
: All accounts are now working as expected.
:
: Hopefully, my final question.
: If I want to set up a group of users who can look at one Database and 8 of
: the 16 available tables in that database what permissions should I set?

Please don't top post. This should also be in a new thread and in the db
group. It would give you more opportunity for others to see you have a
second question.

You may need to rephrase because user's should only have access to what your
application allows them to. If you're referring to developers, you're not
restricted to using one account to access a database. You can set
individual permissions on a table for different users, if that's your goal.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

 

[Bob Barrows [MVP]]

Thanks to all who contributed. After a week of struggle I am finally
where I needed to be 2 weeks ago. At least I finally have a smile on
my face.
Great!

It is finally working in VB and ASP. Here is what I did. I created
SQL login accounts and re-booted system. They did not work. the sa
account did not even work. I then changed Authentication back to
Windows Only(I did not change any account information). Re-booted
again. Changed back to SQL Server and Windows Authentication and
re-booted again.
All accounts are now working as expected.

So you did not have to run the ALTER LOGIN command as shown in the Help
topic I pointed you to? Interesting ...

Hopefully, my final question.
If I want to set up a group of users who can look at one Database and
8 of the 16 available tables in that database what permissions should
I set?

You are now getting out of the realm of ASP and into the realm of SQL Server
Security. This is a huge topic and you should start in Books Online. I
have not started working with SQL2005 yet so any advice I could give you may
be outdated. However, in a nutshell:
Add the sql logins to the database without assigning them to a global role
(datareader, datawriter, etc.) in that database.
Edit the properties of the specific tables to which you wish to grant the
users access and choose the option/button/page/tab (whatever it is in 2005
Management Studio) to edit Permissions for the object.

You will get more focussed help in the appropriate sqlserver group ... I
think there is a microsoft.public.sqlserver.security group, but you should
check to be sure.

HTH,
Bob Barrows

 

[Bob and Sharon Hiller]

Bob,
I could not figure how or where to issue the ALTER LOGIN command. I
switched the Authentication mode a few times and then read in the help file
that you had to have a strong password. I was not sure what that was either.
Another Help search and I changed my login password to a strong one. A soon
as I did that, I got a message box stating that I needed to stop and restart
the SQL Server service before the change would take effect. Instead of
stopping/restarting service I just re-booted. The moral of the story: If you
don't enter a strong password, it does not work. If you do enter a strong
password, re-boot and all is well.

Bob Hiller