Re: SQL 2000 Security Question

From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 05/28/05 

Date: Sat, 28 May 2005 04:25:56 -0700

  "Blake Mengotto" <mengotto@nospam.hotmail.com> wrote in message news:eRx$s41YFHA.3280@TK2MSFTNGP09.phx.gbl...
  Simple question from someone who knows nothing about SQL.

  SQL is set to use Windows Authentication only.

  I deny access to Built-In\Administrators
Builtin\Administrators is by default a member of the System Administrators fixed server role. It is not possible to set access denied to anything for Sys Admin role members -- but before you even think about removing that group from that role, you'll need to add yourself or whoever will be responsiblr for this SQL server, individually, to the Sys Admin role, otherwise you'll find yourself on the outside looking in.
  I add an account that is a local admin on the SQL box, and give it DB_Owner to various DB's that it should own, and be able to do whatever in.

  Will the DENY on Built-in\Admins keep this local admin id from accessing SQL?
Assuming this NT group is no longer a member of Sys Admin, that depends upon how you deny access. Explicit access-denied privileges for a given object always supercede access-allowed privileges to the same object. But typically access is "denied" to an object merely by removing all access-allowed privileges.

Taking your question at face value, if you had a user named, let's say, jsmith, and you make jsmith dbo of the pubs database, but then you define access-denied for jsmith to pubs, jsmith will not be able to connect to pubs.

Make sense?

-Mark

  TIA

  --
  Regards,
  Blake

Relevant Pages

  • Re: SQL 2000 Security Question
    ... Before I denied built in admins, I created an ID called SQLDBA that had the SA role, because I knew I could lock myself out of SQL. ... Taking your question at face value, if you had a user named, let's say, jsmith, and you make jsmith dbo of the pubs database, but then you define access-denied for jsmith to pubs, jsmith will not be able to connect to pubs. ...
    (microsoft.public.sqlserver.security)
  • Re: No value given for one or more required parameters
    ... No this is not pubs from SQL 2000. ... There is no required filelds. ... >> Operation must use an updateable query. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: sp_fulltext_table with SQL Server Agent - permissions problem?
    ... SQL Server Scheduled Job 'hm_build_ft' ... I have tried to create SQL FTS on the pubs database but the problem is the ... >> USE hm2 ...
    (microsoft.public.sqlserver.fulltext)
  • Re: SQL connection problem
    ... EXEC sp_defaultdb 'John', 'pubs' ... Detach the database and delete the LOG file. ... > I have sql 2000. ...
    (microsoft.public.sqlserver.programming)
  • Re: need help
    ... for both my db and the pubs db which the code works ... I did change my sql to thi: ... dbo.Task.comments FROM dbo.Team INNER JOIN dbo.Task ON ... Is the SQL Server you are hitting a local ...
    (microsoft.public.dotnet.languages.csharp)