Re: Deny Access to SQL Server Login

From: John Aspenleiter (jaspenle_at_strong.com)
Date: 08/27/03 

Date: Wed, 27 Aug 2003 07:05:37 -0700

Thanks for the reply Kimberly. It would be nice to be
able to single out logins that you need locked out. To
me, it seems like something they forgot to include...

John

>-----Original Message-----
>If you want to block ALL users from getting NEW
connections then you could
>"pause" SQL Server. If you only want to prevent some
users from ALL
>databases then this is not an option... Not a bad design
change request
>(send an email to sqlwish@microsoft.com).
>
>BUT - if you're really trying to block all users from a
specific database
>because you're trying to do maintenance on a specific
database - then how
>about setting that database to "restricted_user" and it
will only allow DBO?
>Maybe that will work? The syntax is
> ALTER DATABASE dbname
> SET RESTRICTED_USER
>
>hth,
>kt
>
>--
>Please reply only on the newsgroups! Include dml/ddl,
when possible.Thanks
>Kimberly L. Tripp
>President, SYSolutions, Inc. www.SQLSkills.com
>Principal Mentor, Solid Quality Learning
www.SolidQualityLearning.com
>
>
>"John Aspenleiter" <jaspenle@strong.com> wrote in message
>news:056201c36c15$c3a10a90$a101280a@phx.gbl...
>> My question has to do with SQL Server logins in SQL
Server
>> 2000. I know that nt accounts with sql server you have
>> the ability to deny access to certain nt accounts. How
>> would you deny access to SQL Server accounts?
>>
>> sp_addlogin john, john, master
>>
>> EXEC sp_denylogin 'john'
>>
>> Server: Msg 15407, Level 11, State 1, Procedure
>> sp_denylogin, Line 31
>> 'john' is not a valid Windows NT name. Give the complete
>> name: <domain\username>.
>>
>> I need the ability to lockout logins for maintenance
>> purposes. I don't know of a clean way to block logins
>> from accessing sql server...
>>
>> Thanks!
>>
>> John
>
>
>.
>

Relevant Pages

  • Re: Logging in irrespective of database access
    ... Try connecting using Query Analyzer with one of the problem logins and run ... the following in the context of your demodata and accounts databases: ... This will return the security context used to access the database. ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Try connecting using Query Analyzer with one of the problem logins and run ... the following in the context of your demodata and accounts databases: ... This will return the security context used to access the database. ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem with SQL Server 2005 Express Edition
    ... Use of included script samples are subject to the terms specified at ... One cause for that error is trying to use a SQL Server login on a database server that is configured to only accept Windows logins. ...
    (microsoft.public.sqlserver.security)
  • Re: Help Ive been an idiot
    ... You need to make sure that you readd any logins that were mapped to database ... Jasper Smith (SQL Server MVP) ... > connectionString, CommandType commandType, String commandText, ...
    (microsoft.public.sqlserver.setup)
  • Re: Master database must never be the default database for any login
    ... let's say that I want TEMPDB as my default database for all logins ... Is it possible I configure SQL Server to set a default database other ... MASTER to any logins created automatically? ...
    (microsoft.public.sqlserver.security)