MS SQL Server security - SQL 2000 Security Question

Author

SQL 2000 Security Question

Blake Mengotto

2005-05-28, 3:23 am

Simple question from someone who knows nothing about SQL.

SQL is set to use Windows Authentication only.

I deny access to Built-In\Administrators

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?

TIA

--
Regards,
Blake

Mark J. McGinty

2005-05-28, 7:23 am

"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\Administrato
rs 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

Blake Mengotto

2005-05-28, 11:23 am

Yes. So SQL security is similar to NTFS. If you deny access to a group that an individual is a member of, then add that individual to a database, with full access, he/she will not be able to gain access. Makes sense, and follows my original line of thought.

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. Thanks for the answer!

Regards,
Blake
"Mark J. McGinty" < mmcginty@spamfromyou
.com> wrote in message news:eAVGaf3YFHA.228@TK2MSFTNGP12.phx.gbl...

"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\Administrato
rs 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

Sponsored Links