SQL Server Performance Forum – Threads Archive
Stop Access from Windows Authentication
Hi I have some doubt in SQL Server Security model Please suggest me on this. My data should not be exposed even to the End User through Query Analyzer or Enterprise Manger. For that I have created my database in a separate instance of sql server, But the end user with DBA Permissions is able to connect to database through Windows Authentication To connect to the database every one has to provide password. Please help me out on this Thanks & RegardsAnji Reddy
It sounds like you have granted SELECT permission to the end user so the program you are writing works. And now you want to make sure they can’t do ad-hoc SELECT statements. If you want this, you can do one of two things – create a VIEW through which users access the data, and grant permission only on that, or create stored procedures which return recordsets and grant permission only on that. Remember with your VIEWs you can horizontally partition the data as well as vertically partition it i.e. you can choose to select just a subset of columns (for vertical partitioning) but also have a WHERE mydate between X and Y and CusNum = 7 etc etc to horizontally partition it. Does this help? Dave. [email protected]
www.matiogi.com
If you use Windows authentication there is no facility of asking password before connecting to the SQL Server. In order to facilitate this you must use mixed mode authentication, but for the better security windows authentication is suggested. As referred by Dave ensure to maintain the privileges on each table and if the users have DBA privileges then make sure to use a third party tool for auditing purpose. In future kindly post the threads in relevant forums only. 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.
]]>