SQL Access Rights

Printable View

August 3rd, 2004, 08:55 PM
cheyenne1212

SQL Access Rights

I have a SQL server 2000 SP3a running on server 2003 Enterprise Edition and I have one problem.

The only way the users can get the SQL server service manager on their workstations to connect to the SQL server is if they are part of a admin account (domain/Administrators etc)

How can I let a regular user connect to the SQl server without adding the users to a admin account.

I have tried adding their logons to the SQL server itself but still it won't let me have them connect.

Now the software we're gonna be using will not require as I believe the software will interact with the database itself. MSDE which is what I was playing with at the moment and having the user access rights problems with.

THanks for your help on this, I've never handled any SQL server things before.
August 3rd, 2004, 09:12 PM
faith_in_death

check the permisions in the firewall (if you have one)....and the acces rigths for remote conection in the user's profile
August 3rd, 2004, 09:24 PM
CXGJarrod

Just to make sure we are talking about the same thing: (As I am coming from a 2000 and not 2003 background)

"SQL SERVICE MANAGER

Also known as the SQL traffic light program, this feature lets you start and stop all SQL-related services. The only new option in this program is the ability to start and stop the SQL DTC service."

You are going to have to be an admin to start or stop the service. Does the application need to use the SQL Service manager to access the database? The few applications that ran with an SQL database that I have dealt with (mainly Goldmine) did not need that to be running. (Or need admin privlidges to query and do its job with the database)
August 4th, 2004, 01:26 PM
SirDice

SQL server service manager is a tool designed for administrators. Your users do not need it.

Normal applications that use an SQL server usually connect to the server using an ODBC connection. You can create a system DSN to allow any user on that workstation to use that ODBC connection or you can create a user DSN to only allow that perticular user.

As for restricting what users can or cannot do that's done within SQL-Server by setting the appropriate privileges (select, insert, update etc.) for that useraccount. This is usually done by a DBA (DataBase Administrator).
August 4th, 2004, 01:49 PM
cheyenne1212

Yeah I don't know what I was thinking nevermind.

was just one of those days. lol
August 4th, 2004, 09:32 PM
cheyenne1212

sorry bout the double post, but I have one more question.

It all works fine now, but one mistake I made, was to create the SQL database on the C: partition which only has 400MB space out of 3.9 gigs.

I have a second D: partition on there with 36.1gigs of free space.

Both of these partitions are 1 SCSI / RAID array in raid 1 config.

Any idea on how I coud move the database to the larger partition?
August 9th, 2004, 09:59 AM
SirDice

Make an SQL backup and restore to the new location?
August 10th, 2004, 04:58 PM
mohaughn

SQL 2000 has a copy database wizard...

http://support.microsoft.com/default...b;en-us;274463

You can also do it manually.. Even though this article is written specifically about an MIIS database you could do this action to any database running on SQL2000...

http://support.microsoft.com/default...b;en-us;818565

Substitute MicrosoftIdentityIntegrationServer with your database name.

I think moving it manually is a better solution as the copy database wizard is going to create a new database and copy all of the data to that new database, which may take a bit longer.