Showing posts with label situation. Show all posts
Showing posts with label situation. Show all posts

Sunday, February 19, 2012

Domain account without a SQL login account

I have a situation that I have discovered in our QA database that I need to resolve. When I looked at the Activity Monitor for our server, I discovered that a process is running under a domain user account for one of our .Net applications. The problem is that that domain user account has not been created as a SQL login account on the server. I am trying to figure out how someone can log in to the database server with a domain user account that has not been added to SQL Server as a login account.

Does anyone have any insight on this? I don't like the idea of someone being able to create domain account that can access the database without me granting them specific access.

- Larry

It could be working under the guest account, it could be included in a domain group account that has database access, it could be under an ALIAS (a deprecated feature -and still dangerous).|||

Most likely the Windows user has permission to connect via a Windows group membership. If the user belongs to one or more of such group and it has CONNECT SERVER permission, the Windows user will have access to connect to SQL Server.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Apparently, the account in question was a member of the WIndows local admin group. It makes me rather nervous that an ID could be created that basically has unlimited access to my databases and, as the DBA, I can't do a whole lot about this. Is there a good way that I can mitigate this risk?

|||

Here is a link to a different forum question on how to remove local administrators from the sysadmin group. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1559584&SiteID=1

Be aware that this helps to keep honest people honest and prevent misuse by mitake, but it is only a speed-bump in case the machine administrator is truly malicious and determined to get to your data. As the machine administrator has full control over the machine and files stored there as well as any process running on it. I would strongly recommend a establishing a security policy so you can monitor and audit activity on both SQL Server as well as on the machine itself.

-Raul Garcia

SDE/T

SQL Server Engine

Tuesday, February 14, 2012

Does views use table indexes.

Hi.
I have a situation where I have a table containg data, but several columns
can be used to designate that the data is "hidden". E.g. if we delete a
record, it isn't actually deleted but a datetime field called deleted_date
is changed from null to the current date-time.
Therefore I made a view like
create view vw_LiveData as select * from Data where deleted_date is null
The original table had a full-text indexed column, but using that search
criteria on the view didn't work
select * from vw_LiveData where contains(fullTextField, 'blahblahblah')
The line produces an error saying that the view doesn't have a fulltext
index.
What I speculate now is all the other indexes defined on the table, do they
have any importance when searching through data in the view?
Suppose the Data table has a foreign key to a user called User_ID that is
indexed on the table
Clearly the index is used when I type
Select * from Data where User_ID=@.User_ID
Is it also used when I type
select * from vw_LiveData where User_ID=@.User_ID
or is it necessary to created index views if I want both easy to read sql
code AND acceptable performance?
Thanks in advance,
Peter StrimanPeter Striman wrote:
> Hi.
> I have a situation where I have a table containg data, but several
> columns can be used to designate that the data is "hidden". E.g. if
> we delete a record, it isn't actually deleted but a datetime field
> called deleted_date is changed from null to the current date-time.
> Therefore I made a view like
> create view vw_LiveData as select * from Data where deleted_date is
> null
> The original table had a full-text indexed column, but using that
> search criteria on the view didn't work
> select * from vw_LiveData where contains(fullTextField,
> 'blahblahblah')
> The line produces an error saying that the view doesn't have a
> fulltext index.
> What I speculate now is all the other indexes defined on the table,
> do they have any importance when searching through data in the view?
> Suppose the Data table has a foreign key to a user called User_ID
> that is indexed on the table
> Clearly the index is used when I type
> Select * from Data where User_ID=@.User_ID
> Is it also used when I type
> select * from vw_LiveData where User_ID=@.User_ID
> or is it necessary to created index views if I want both easy to read
> sql code AND acceptable performance?
No. The index is used. It may be different with full text indexes -
check with BOL. Btw, IMHO it's better to name all columns in the view
explicitely. That way your view does not change if the underlying table
changes.
Kind regards
robert|||Thanks for the reply, just what I wanted to hear :)
It is different with full-text indexes - and that was what gave me the
concern. But it's no issue in my case since I would only use the
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23F0oyoLnFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Peter Striman wrote:
> No. The index is used. It may be different with full text indexes -
> check with BOL. Btw, IMHO it's better to name all columns in the view
> explicitely. That way your view does not change if the underlying table
> changes.
> Kind regards
> robert
>|||
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:<#F0oyoLnFHA.2484@.TK2MSFTNGP15.phx.gbl>...
<snip>
Btw, IMHO it's better to name all columns in the view

> explicitely. That way your view does not change if the underlying table

> changes.

>

> Kind regards

>

> robert

>
I STRONGLY agree. A view using a SELECT * will do very strange things if
fields are added to the table.
Daniel Wilson <Mailto://d.wilson@.embtrak.com>
Senior Software Solutions Developer
Embtrak <http://www.Embtrak.com> Development Team
DVBrown Company
(864)292-5888