Preventing windows users accessing a database

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
SteveSteve (ga630sf@.newsgroups.nospam) writes:
> 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
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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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
"Steve" <ga630sf@.newsgroups.nospam> wrote in message
news:edw2AkkzHHA.4004@.TK2MSFTNGP05.phx.gbl...
> 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
Thanks worked a treat
Regards
steve
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99786FED46931Yazorman@.127.0.0.1...
> Steve (ga630sf@.newsgroups.nospam) writes:
> 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.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx