Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Security / July 2007

Tip: Looking for answers? Try searching our database.

Preventing windows users accessing a database

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Steve - 25 Jul 2007 00:18 GMT
Hi All

I want to prevent windows users from accessing my database on SQL server
express 2005

I don't want users to be able to login with SSME on Windows authentication,
only by SQl Server Authentication and only on the sa and another specific
login with the password I have set

For the life of me I can't seem to find how to do this

Can anybody advise me

Regards
Steve
Erland Sommarskog - 25 Jul 2007 09:57 GMT
> I want to prevent windows users from accessing my database on SQL server
> express 2005
[quoted text clipped - 4 lines]
>
> For the life of me I can't seem to find how to do this

First of all, if you granted access to any Windows login or groups,
remove these. Second, also revoke access to BUILTIN\Administrators,
which gives permission to all Windows logins that have admin rights
on the machine.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Steve - 26 Jul 2007 01:18 GMT
Erland

Thanks worked a treat

Regards
steve

>> I want to prevent windows users from accessing my database on SQL server
>> express 2005
[quoted text clipped - 9 lines]
> which gives permission to all Windows logins that have admin rights
> on the machine.
Uri Dimant - 25 Jul 2007 10:11 GMT
Steve
I think the following has been introduced in SP2 and I did not test on
Express Edition ,sorry

/*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
– Issue a rollback*/
for logon
as begin

if exists (select * from sys.server_principals

where type_desc ='Windows_Login'

and name=original_login() )

begin

ROLLBACK;
end
end
go

For more details please

> Hi All
>
[quoted text clipped - 11 lines]
> Regards
> Steve
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.