Is there a way to prevent any user other than sa access to SQL server 2005

Question

Post reply

Is there a way to prevent any user other than sa access to SQL server 2005

osama.g.y.a

SSC Rookie

Points: 34
More actions
January 2, 2010 at 11:53 pm

#217933

I have a question and I hope you will help
Is there a way to prevent any user other than sa access to SQL server 2005
Because, as is well known to all when you download the SQL there are two options
Windows Authentication mode
SQL Server and Windows Authentication mode

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

why would you only allow sa to connect ?

Is it for sql server (software) maintenance ?

You could start sqlserver using the maintenance parameter. (check books onlin for startup parameters and the consequences)

Keep in mind, it is a BAD practise to use sa for applications.

It is a best practise to host sqlserver using a windows service account.

Please rephraze your question.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

sean redmond SSCertifiable Points: 5824 More actions · Answer 2

You can do it simply by dropping/deactivating all logins and all of their associated users. Why you would want to do it, however, I'm not sure.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

Or by activating a login trigger (needs sp2) which only alows 'sa' or members of sysadmins sqlgroup.

Could be usefull if you intend to acivate it for a maintenance window, but think of the consequences !!

Have a look at http://www.sqlservercentral.com/articles/Administration/64974/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Nice Marmot SSCrazy Points: 2278 More actions · Answer 4

Just out of curiosity, if your SQL Server is in a Domain, could you not do this by creating a login to deny connect permission to Domain Users? That would include the DBAs too, though . . . 😉

sean redmond SSCertifiable Points: 5824 More actions · Answer 5

I could, I suppose. The problem is that, aside from malevolent external intervention, the only people who have such write-access are the DBAs and the Sysadmins and none admit to doing it.

External malevolent intervention falls into the sphere of the sysadmins and I'll leave that to them. The lesson has taught me that we need a much more detailed logging system. At this point in time, the trace method suggested looks very appealing but I have to examine it in more detail.