Re: Deny Access to SQL Server Login

From: Kimberly L. Tripp (Kimberly_PleaseReplyOnTheNewsGroups_at_nospam.sqlskills.com)
Date: 08/27/03 

Date: Tue, 26 Aug 2003 15:01:02 -0700

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: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
    ... but even MORE generic than the XDb1 implementation, ... disadvantage that 'john', 'John' and 'JOHN' are treated as three distinct ... Since this is the default collation, I assume that your database will use ... I have SQL Server on my desktop; ...
    (comp.object)
  • Re: Users need sysadmin rights
    ... at the database level there are a number of fixed database roles to ... db_securityadmin - Manages roles and members of SQL Server 2000 database ... > order to be able to manage these accounts. ... > but I'm reluctant to grant sysadmin rights to accomplish this task. ...
    (microsoft.public.sqlserver.security)
  • Re: View Type of Protocol
    ... Thanks a lot for the feedback John. ... information about the type of authentication being used. ... some reason Kerberos is not available. ... they are not authenticated and the SQL Server login fails. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Logging in irrespective of the database access settings
    ... > specify that each login has the System Admin ticked under Server Roles ... > What's the point in having the Database Access section if the System Admin ... > the following in the context of your demodata and accounts databases: ... > SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: update index in background not running.
    ... John, ... event log that SQL Server 2000 FT Indexing success & failures are recorded. ... exec sp_defaultdb N'NT Authority\System', N'master' ...
    (microsoft.public.sqlserver.fulltext)