Rights and permissions

[YoelBenYossef]

Hi there,
We had an occurence that I can't explain on our system last week, and I was wondering if anyone could help me find the cause.

One of our users tried to create and a System DSN connection to an ODBC database to which they have access. The database itself uses sql and server logins, and they are defined on the db. The user however couldn't create a link in the odbc until we made him a local admin on the server.

Any idea's what I'm missing? Or do I make all my users local admins?

Thanks
Eric

 

[onpnt]

NO, NO and NO do not make the sysadmin's and take them out NOW

Check the security on the database they need to get to. Make sure thy have the correct settings.

What do they need to do on the DB?

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[SQLBill]

First narrow down the problem.

Is it with creating an ODBC connection? Or is it with logging into SQL Server? That is two different things.

Where I work, only admins can create ODBC connections and I believe it's that way a lot of places. So, I'm guessing that is the real problem. Instead of making your users admins, have the sysadmin set up the ODBC connections for whomever needs it.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

SQLBill asks a very importnat question. Where was the error that they were getting? And more importantly what was the error that they were getting? What options did they select when setting up the DSN?

Having admin rights to the server shouldn't allow anyone to create a DNS on there workstation where they couldn't without local server rights, unless there NT login doesn't have access to the database before hand.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Absolutely agree with all of the above.

You have to be local Admin on your personal PC to create an ODBC. Or, I think, a Power User might be able to do it. Regardless, there is NEVER any reason why a user should create an ODBC on the Server itself. The ODBC is created on the client machine so the client can connect on the server. Any ODBCs on the Server actually cause severe security issues.

Another note, having too many ODBCs on a box can actually kill your network performance. You might want to do some googling to verify which ones are the worst, but I think it's System ODBCs followed by File and then by User (I could have them reversed).

As said above, remove SysAdmin permissions from these people. Also, remove their access from the Server OS COMPLETELY so they can't hack their way into giving themselves high-SQL Server permissions. The only time a user / group needs permission to anything on the server OS is if they need to access to a disk share. They should not be able to connect (directly or remotely) to the Server.

Sorry we're all freaking out here, but we all work in environments where people get fired for doing things like that. And that just plain ain't good. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"