How to determine if a Windows Auth ID is no longer valid in SQL?

How can we proactively find out when a Windows Authentication ID in SQL
Server is no longer valid, i.e. an employee is no longer with the company so
we should delete their related Windows Authentication user IDs from our SQL
Server databases?Don't you have an employee table that lists the AD login and whether the
employee is active or not? Some companies store this data directly in AD.
You need to find out where the information is stored though, before we can
tell you how to fix your issue.
Another question...are they disabling the AD account? If you were using AD
groups, instead of individual logins, you would no longer have to worry abou
t
an individuals access. It's also easier to manage and delegate management
responsibility to the business and HR.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Stephanie" wrote:

> How can we proactively find out when a Windows Authentication ID in SQL
> Server is no longer valid, i.e. an employee is no longer with the company
so
> we should delete their related Windows Authentication user IDs from our SQ
L
> Server databases?
>|||I found the sp_ValidateLogins very helpful.
To quote the BOL - "Reports information about orphaned Microsoft? Windows
NT? users and groups that no longer exist in the Windows NT environment but
still have entries in the Microsoft SQL Server? system tables."
Randy
"Stephanie" wrote:

> How can we proactively find out when a Windows Authentication ID in SQL
> Server is no longer valid, i.e. an employee is no longer with the company
so
> we should delete their related Windows Authentication user IDs from our SQ
L
> Server databases?
>

How to determinate if I have install sp1 of SqlExpress

Hi,
How to determinate if I have install the sp1 of SqlServer 2005 Express?ad
SELECT @.@.Version?
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uC8pZYHfGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi,
> How to determinate if I have install the sp1 of SqlServer 2005 Express?
>

How to determinate if I have install sp1 of SqlExpress

Hi,
How to determinate if I have install the sp1 of SqlServer 2005 Express?ad
SELECT @.@.Version?
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uC8pZYHfGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi,
> How to determinate if I have install the sp1 of SqlServer 2005 Express?
>sql

How to detect that a sqlserver table row has foreign keys and cannot be deleted?

What is the best way to detect that a sqlserver table row has foreign keys and cannot be deleted?

Thanks,

Keith

sp_table_constraints_rowset 'yourTableName'

How to detect SQLServer 2005

How can I detect if my computer have installed SQLServer 2005 or SQLServer
2005 Express with program like c# or VB.NNET?
Hi
If you can connect to the instance you can run a query that returns the
result of the function SERVERPROPERTY
SELECT CONVERT(varchar(40), SERVERPROPERTY('edition'));
John
"ad" wrote:

> How can I detect if my computer have installed SQLServer 2005 or SQLServer
> 2005 Express with program like c# or VB.NNET?
>
>

How to detect SQLServer 2005

How can I detect if my computer have installed SQLServer 2005 or SQLServer
2005 Express with program like c# or VB.NNET?Hi
If you can connect to the instance you can run a query that returns the
result of the function SERVERPROPERTY
SELECT CONVERT(varchar(40), SERVERPROPERTY('edition'));
John
"ad" wrote:
> How can I detect if my computer have installed SQLServer 2005 or SQLServer
> 2005 Express with program like c# or VB.NNET?
>
>sql

How to detect a dead database

I have a database of SqlServer call myData, and it's physicial is
c:\myData.mdf.
Some one stop the SQLServer Service, then delete c:\myData.mdf, then
start the SQLService, and then the database myData is dead.
How can I detect if myData is in this state?Ad
The sysdatabases table has a column status. Read the BOL about it
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||If the physical file containing the database has been deleted, then the
database is truly gone.
Do you have a backup?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
Did you hire a terrorist?
Just like the case of DBF in Foxpro, if you delete the table.dbf, there
is no way to recover it. You may wanna try Easy Data Recovery Pro to
undelete the file. Did you check the recycle bin?
Man-wai Chang
Softmedia Technology Co., Ltd.
Tel: (852)3583 2780|||I did not wnat to recover the database.
I want to confirm if the database has no physical file before delete it.
How can I confirm the database has no physical file?
"Man-wai Chang" <info@.softmedia.hk>
'?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
> Did you hire a terrorist?
> Just like the case of DBF in Foxpro, if you delete the table.dbf, there is
> no way to recover it. You may wanna try Easy Data Recovery Pro to undelete
> the file. Did you check the recycle bin?
>
> --
> Man-wai Chang
> Softmedia Technology Co., Ltd.
> Tel: (852)3583 2780|||Hi,
As a first step ensure that no one have rights to SQL Server box apart from
authorised people. If you have backup you could
restore the database from Backup.
Thanks
Hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||Hi,
Execute the command from Master database:-
DROP DATABASE <DBNAME>
This command will drop the database and close all physical MDF and LDF
Files.
Thanks
hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ObiV0Q2yGHA.4116@.TK2MSFTNGP02.phx.gbl...
>I did not wnat to recover the database.
> I want to confirm if the database has no physical file before delete it.
> How can I confirm the database has no physical file?
>
> "Man-wai Chang" <info@.softmedia.hk>
> '?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>|||Thanks,
But how can I dertiminate if a database lost it's physicial file?
"Hari Prasad" <hari_prasad_k@.hotmail.com> glsD:ey1nz22yGHA.4204@.TK2MSFTNGP04.phx.g
bl...
> Hi,
> Execute the command from Master database:-
> DROP DATABASE <DBNAME>
> This command will drop the database and close all physical MDF and LDF
> Files.
> Thanks
> hari
> SQL Server MVP
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:ObiV0Q2yGHA.4116@.TK2MSFTNGP02.phx.gbl...
>|||ad wrote:
> Thanks,
> But how can I dertiminate if a database lost it's physicial file?
>
If you're on SQL 2000, query the sysdatabases table to get the data file
name, then use xp_cmdshell or the undocumented xp_fileexists sproc to
see if the file exists.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Database will move to suspect status.
Thanks
Hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:%23uh5rS6yGHA.996@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> But how can I dertiminate if a database lost it's physicial file?
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com>
> glsD:ey1nz22yGHA.4204@.TK2MSFTNGP04.phx.gbl...
>

How to detect a dead database

I have a database of SqlServer call myData, and it's physicial is
c:\myData.mdf.
Some one stop the SQLServer Service, then delete c:\myData.mdf, then
start the SQLService, and then the database myData is dead.
How can I detect if myData is in this state?Ad
The sysdatabases table has a column status. Read the BOL about it
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||If the physical file containing the database has been deleted, then the
database is truly gone.
Do you have a backup?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
Did you hire a terrorist? :)
Just like the case of DBF in Foxpro, if you delete the table.dbf, there
is no way to recover it. You may wanna try Easy Data Recovery Pro to
undelete the file. Did you check the recycle bin?
Man-wai Chang
Softmedia Technology Co., Ltd.
Tel: (852)3583 2780|||I did not wnat to recover the database.
I want to confirm if the database has no physical file before delete it.
How can I confirm the database has no physical file?
"Man-wai Chang" <info@.softmedia.hk>
'?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
>> start the SQLService, and then the database myData is dead.
> Did you hire a terrorist? :)
> Just like the case of DBF in Foxpro, if you delete the table.dbf, there is
> no way to recover it. You may wanna try Easy Data Recovery Pro to undelete
> the file. Did you check the recycle bin?
>
> --
> Man-wai Chang
> Softmedia Technology Co., Ltd.
> Tel: (852)3583 2780|||Hi,
As a first step ensure that no one have rights to SQL Server box apart from
authorised people. If you have backup you could
restore the database from Backup.
Thanks
Hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||Hi,
Execute the command from Master database:-
DROP DATABASE <DBNAME>
This command will drop the database and close all physical MDF and LDF
Files.
Thanks
hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ObiV0Q2yGHA.4116@.TK2MSFTNGP02.phx.gbl...
>I did not wnat to recover the database.
> I want to confirm if the database has no physical file before delete it.
> How can I confirm the database has no physical file?
>
> "Man-wai Chang" <info@.softmedia.hk>
> '?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
>> start the SQLService, and then the database myData is dead.
>> Did you hire a terrorist? :)
>> Just like the case of DBF in Foxpro, if you delete the table.dbf, there
>> is no way to recover it. You may wanna try Easy Data Recovery Pro to
>> undelete the file. Did you check the recycle bin?
>>
>> --
>> Man-wai Chang
>> Softmedia Technology Co., Ltd.
>> Tel: (852)3583 2780
>|||Thanks,
But how can I dertiminate if a database lost it's physicial file?
"Hari Prasad" <hari_prasad_k@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:ey1nz22yGHA.4204@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Execute the command from Master database:-
> DROP DATABASE <DBNAME>
> This command will drop the database and close all physical MDF and LDF
> Files.
> Thanks
> hari
> SQL Server MVP
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:ObiV0Q2yGHA.4116@.TK2MSFTNGP02.phx.gbl...
>>I did not wnat to recover the database.
>> I want to confirm if the database has no physical file before delete it.
>> How can I confirm the database has no physical file?
>>
>> "Man-wai Chang" <info@.softmedia.hk>
>> '?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
>> start the SQLService, and then the database myData is dead.
>> Did you hire a terrorist? :)
>> Just like the case of DBF in Foxpro, if you delete the table.dbf, there
>> is no way to recover it. You may wanna try Easy Data Recovery Pro to
>> undelete the file. Did you check the recycle bin?
>>
>> --
>> Man-wai Chang
>> Softmedia Technology Co., Ltd.
>> Tel: (852)3583 2780
>>
>|||ad wrote:
> Thanks,
> But how can I dertiminate if a database lost it's physicial file?
>
If you're on SQL 2000, query the sysdatabases table to get the data file
name, then use xp_cmdshell or the undocumented xp_fileexists sproc to
see if the file exists.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Database will move to suspect status.
Thanks
Hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:%23uh5rS6yGHA.996@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> But how can I dertiminate if a database lost it's physicial file?
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com>
> ¼¶¼g©ó¶l¥ó·s»D:ey1nz22yGHA.4204@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Execute the command from Master database:-
>> DROP DATABASE <DBNAME>
>> This command will drop the database and close all physical MDF and LDF
>> Files.
>> Thanks
>> hari
>> SQL Server MVP
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:ObiV0Q2yGHA.4116@.TK2MSFTNGP02.phx.gbl...
>>I did not wnat to recover the database.
>> I want to confirm if the database has no physical file before delete it.
>> How can I confirm the database has no physical file?
>>
>> "Man-wai Chang" <info@.softmedia.hk>
>> '?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
>> start the SQLService, and then the database myData is dead.
>> Did you hire a terrorist? :)
>> Just like the case of DBF in Foxpro, if you delete the table.dbf, there
>> is no way to recover it. You may wanna try Easy Data Recovery Pro to
>> undelete the file. Did you check the recycle bin?
>>
>> --
>> Man-wai Chang
>> Softmedia Technology Co., Ltd.
>> Tel: (852)3583 2780
>>
>>
>

How to delete a sqlserver system stored procedure in master?

Hi,
I need to replace a sqlserver system stored procedure
(sp_depends) because of a bug in the master database. But
it doesn't allow me to delete it. What steps do I need
to follow to delete a system stored procedure?
Thx"Tony" <indengr@.yahoo.com> wrote in message
news:087901c38391$a55abe30$a101280a@.phx.gbl...
> I need to replace a sqlserver system stored procedure
> (sp_depends) because of a bug in the master database. But
> it doesn't allow me to delete it. What steps do I need
> to follow to delete a system stored procedure?
Which bug, can you document that for us? Providing you are a sysadmin you
should be able to use ALTER PROC to modify a system stored procedure. As I'm
sure you're aware, making revisions like this place you in unsupported
territory,
Steve|||here is the bug:
http://support.microsoft.com/default.aspx?scid=kb;en-
us;180490
I'm getting this error in the sqlserver log. So I had to
replace the sp_depends procedure. I can't rename or
delete the procedure. I administer the database.
>--Original Message--
>"Tony" <indengr@.yahoo.com> wrote in message
>news:087901c38391$a55abe30$a101280a@.phx.gbl...
>> I need to replace a sqlserver system stored procedure
>> (sp_depends) because of a bug in the master database.
But
>> it doesn't allow me to delete it. What steps do I need
>> to follow to delete a system stored procedure?
>Which bug, can you document that for us? Providing you
are a sysadmin you
>should be able to use ALTER PROC to modify a system
stored procedure. As I'm
>sure you're aware, making revisions like this place you
in unsupported
>territory,
>Steve
>
>.
>|||The article you refer recommends doing just that -- creating a NEW procedure
called sp_depends2 and using that instead of sp_depends. It does not say to
delete sp_depends.
The Enterprise Manager has an extra layer of security that does not allow
you to drop any objects marked as system objects.
What error are you getting in the log?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"tony" <indengr@.yahoo.com> wrote in message
news:099f01c383a5$7a5c7cf0$a301280a@.phx.gbl...
> here is the bug:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;180490
> I'm getting this error in the sqlserver log. So I had to
> replace the sp_depends procedure. I can't rename or
> delete the procedure. I administer the database.
>
>
> >--Original Message--
> >"Tony" <indengr@.yahoo.com> wrote in message
> >news:087901c38391$a55abe30$a101280a@.phx.gbl...
> >> I need to replace a sqlserver system stored procedure
> >> (sp_depends) because of a bug in the master database.
> But
> >> it doesn't allow me to delete it. What steps do I need
> >> to follow to delete a system stored procedure?
> >
> >Which bug, can you document that for us? Providing you
> are a sysadmin you
> >should be able to use ALTER PROC to modify a system
> stored procedure. As I'm
> >sure you're aware, making revisions like this place you
> in unsupported
> >territory,
> >
> >Steve
> >
> >
> >
> >.
> >|||I don't think whatever you are trying to do is worth the effort.
Both sp_depends and sp_depends2 rely on the sysdepends table, which is for
all practical purposes not dependable (particularly for stored procedures).
Thee is no supported utility to verify whether sysdepends table is good and
there is no supported utility to correct whatever inconsistencies or add
missing information.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"tony" <indengr@.yahoo.com> wrote in message
news:099f01c383a5$7a5c7cf0$a301280a@.phx.gbl...
> here is the bug:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;180490
> I'm getting this error in the sqlserver log. So I had to
> replace the sp_depends procedure. I can't rename or
> delete the procedure. I administer the database.
>
>
> >--Original Message--
> >"Tony" <indengr@.yahoo.com> wrote in message
> >news:087901c38391$a55abe30$a101280a@.phx.gbl...
> >> I need to replace a sqlserver system stored procedure
> >> (sp_depends) because of a bug in the master database.
> But
> >> it doesn't allow me to delete it. What steps do I need
> >> to follow to delete a system stored procedure?
> >
> >Which bug, can you document that for us? Providing you
> are a sysadmin you
> >should be able to use ALTER PROC to modify a system
> stored procedure. As I'm
> >sure you're aware, making revisions like this place you
> in unsupported
> >territory,
> >
> >Steve
> >
> >
> >
> >.
> >|||You should be write your own stored procedure
>--Original Message--
>Hi,
> I need to replace a sqlserver system stored procedure
>(sp_depends) because of a bug in the master database.
But
>it doesn't allow me to delete it. What steps do I need
>to follow to delete a system stored procedure?
>Thx
>.
>