SQL Server Performance Forum – Threads Archive
OSQL Login
I created a new database and I want to restrict the access to specific users like dbo and/or a new user that I created.I found that when running the OSQL command: OSQL -E -d Eli -Q "SELECT * FROM table1", I can connect with no problem. How can I make that only authorized user can connect ? Thanks
Eli
If configured to perform authentication in Mixed Authentication Mode, the SQL Server will accept attempts to login on these accounts, while not disabling the account on repeated attempts. When using Windows Authentication, Windows users or groups must first be given permission to connect to an instance of SQL Server, and then they must be given permission to use one or more databases in that instance of SQL Server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
elireu, You ARE accessing SQL through an authorised account. A new database with no work done on permissions will only allow members of the db_owner role in. Both the creator of the database and sysadmins have access this way.
The -E option means the connection is made through the windows account you are logged in as. It is likely that as a DBA your windows account is a sysadmin either through group membership, even more certain is that as the database creator you are automatically a member of the db_owner role.
Thanks. Still, the behavior is strange. I created a new login and in the Server Roles I checked the System Administrator. In the Database Acceess, I checked only Test1. Then I ran a select commend on database Test2:
OSQL -S saturn -U eli -P sql123456 -d Test2 -i "D:CeloCard-ProjectDb-Scriptsselect-test-2.sql". OSQL was able to connect and complete a successful select. I expected that if the Database Acceess is set to: Test1, that Login won’t be able to access Test2. Eli
quote:Originally posted by elireu
I created a new login and in the Server Roles I checked the System Administrator.
There is your answer.
mulhall, I can see that you replied but I can’t see your text. Thanks
Eli
Mulhall’s reference is right, as you have enabled that login as a part of SYSADMIN role then it is allowed to connect or manage any of the databases on that SQL server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Sorry Eli, I emboldened the relevant part in your quote. Satya has explained my response appropriately.
Ok. thanks very much. Eli
]]>