Re: Deny Access to SQL Server Login

From: Russell Fields (rlfields_at_sprynet.com)
Date: 08/27/03 

Date: Wed, 27 Aug 2003 10:58:46 -0400

John,

Have you looked at the sp_denylogin stored procedure?

It is not in itself a neat and clean sweeping answer, but is a tool. I
would think that you could create a Windows group that contains all the
logins you want to occasionally deny rights to. Then you could do something
like:

sp_denylogin [MyDomain\GroupToBeRestricted]

-- do my other stuff that needed privacy

sp_grantlogin [MyDomain\GroupToBeRestricted]

I have to confess that I have never done this. :-) When I want privacy in a
database for some work, I usually want it to be in either single_user or
restricted_user mode: E.g.

ALTER DATABASE mydatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Russell Fields
"John Aspenleiter" <jaspenle@strong.com> wrote in message
news:0e2a01c36ca4$4a941180$a301280a@phx.gbl...
> 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: Permission to open database
    ... John I got it to work with the code below I got from an example I found. ... Dim rst As DAO.Recordset ... "Please contact the Database Adminstrator for assistance.", ... "WelcomeForm" ...
    (microsoft.public.access.gettingstarted)
  • Re: Exporting Info To Word File
    ... degree of automation) to get individual records from an existing databse when ... need from my database. ... "John Nurick" wrote: ... >>> document from the template, and place the information in the bookmarks ...
    (microsoft.public.access.externaldata)
  • Re: Communication Log
    ... "John Marshall, MVP" wrote: ... Visio MVP ... May I send you copy of the database that I made? ... a communication log in the database so that it will store the Date, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Export Project notes to Access or Excel
    ... John: I have the same problem as the OP. ... Unfortunately SQL Reporting Services does not have a text field that will ... The macro couldn't get past the 'Dim cn ... Actually you CAN use the code you found in the Project database ...
    (microsoft.public.project)
  • Re: Need some help with MOVENEXT/MOVEPREVIOUS
    ... John wrote: ... Your database has only one record with '123' in MYFIELD ... That's why opening the database then opening a recordset query works. ... Doing the select and the routine, the table is filled with the requested record. ...
    (microsoft.public.vb.database.dao)