December 19, 2009 at 12:04 am
Hello,
I have hard time understanding SQL Server Login, User, Role, Schema… etc.
What I want:
Restrict access to my database to a specified login only. Say my database name is 'AC' and my login is 'Adel'; Adel only can access the database AC, and all other logins can NOT access it (including sa, administrators, guest, built in logins… etc. all must NOT be able to access it) even through Management Studio or whatever. No login or user should access the database 'AC' except the login 'Adel'. This must not affect other logins' access to other databases on the same server.
Details:
I'm deploying a .Net Windows Forms Application in which I use SQL Server 2005 Express Edition. My client already has a Windows Server 2003 server with some databases. I have nothing to do with these databases; it's not my business; other applications and websites are using them, so I should not affect them.
What I am going to do when deploying to my client is:
1- Make a new login named 'Adel' and password-protect it.
2- Create my database 'AC'.
3- Assign it to Adel only (how?)
Can you help me?
Thank you in advance.
Adel,
December 21, 2009 at 10:06 am
Hi Adel,
I don't think it can be done. The sa user will always have access to all databases. I've a similar situation and my solution was to create a named instance. On the machine where the instance is running, I'm member of local administrators. As such, I also run the named instance service. I've removed all other user logins from SQL Server. Any other user that attempts to run the service is denied (the service won't start) and nobody can login to the server except me.
Carlos.
Carlos
December 21, 2009 at 10:14 am
As mentioned you will not be able to stop the administrator from accessing the database.
This is a good thing otherwise in the possible case of a disaster you may find yourself being locked out of the database completly as sometimes the only way to connect is via the DAC
December 21, 2009 at 12:48 pm
If you need restricted access, think about a new install of SQL; maybe as a named instance on the existing machine.
Set the SA password to be something obscure - see goodpassword.com ...
Windows authentication is better than SQL.
Hope this helps!
December 22, 2009 at 4:17 am
Thanks for all of you.
I fully understood your points.
Thank you.
Adel,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply