How to Fix the "Can't access Database because its Read Only" problem

The error comes up like this -

System.Data.SqlClient.SqlException .... because the database is read-only

( I'm putting this here so searches can find it easier)

I got this idea from a related thread, but they used a NETWORK user that is obsolete in 2007.

I found that you have to edit the actual file properties on the ASPNETDB.MDF and ASPNETDB.LDF files so that the ASPUSER has full access. i.e. on the file properties, security tab, hit the Add button and type in ASP.NET then return. Back at the security properties, click on the 'Full Control' checkbox at the top. That took care of it for me.

This was after checking the IIS control panel for the properties on the folder that contained my application and App_Data folders to be sure that the "Write" checkbox was already checked, it was

Of course this assumes that you have installed the express version of SQL from MicrosoftAre you using SQL Server server instance or user instances, I am a bit confused as you are talking about LDF files and the App-Data folder on the other hand.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

My project is a webservice that I created with Studio 2005, in the default configuration if you have a database, its put into an APP_DATA folder in your webservice folder.

Its in that folder where the ASPNETDB.MDF and LDF files are located and it is there where the permissions must be set in this case. As I am serving files from a virtual directory I setup in IIS, the permission apply to the actual file location on my computer. The same would be true, though, if the files were copied to the wwwroot/myWebService/APP_DATA folder on the actual default web site on my computer.

To directly answer your question, the MDF and LDF files are the actual database file and log file for the SQL Express database that I'm using for my application. When creating a SQL-using project in Studio 2005, the system will automagically make these for you within your project and you can then access them directly in your APP_DATA folder. So as to your question about server or user instances, I am not sure that it applies as this is a SQL Express install (the free one from microsoft); maybe that corrsponds to a user instance in your environment (?)

Perhaps someone else on this thread knows exactly what you're talking about

How to find what SQL 2000 service pack is running?

Hey everyone,
We are running SQL 2000 and I just want to know how to find what service
pack is installed and running on the system.
TIA,
Clayton
P.S.: I wrote an iTunes podcast tutorial and just want to publicize it.
You can find it at: http://www.nikoli.net/itunepod
*******************
http://support.microsoft.com/kb/321185/
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Clayton Sutton" <none@.none.com> wrote in message
news:JxOQh.216567$Jt2.120445@.fe04.news.easynews.co m...
> Hey everyone,
> We are running SQL 2000 and I just want to know how to find what service
> pack is installed and running on the system.
> --
> TIA,
>
> Clayton
>
> P.S.: I wrote an iTunes podcast tutorial and just want to publicize it.
> You can find it at: http://www.nikoli.net/itunepod
> *******************
>
>
|||Thanks Aaron, that did the trick!
TIA,
Clayton
P.S.: I wrote an iTunes podcast tutorial and just want to publicize it.
You can find it at: http://www.nikoli.net/itunepod
*******************
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHyPAdsdHHA.3960@.TK2MSFTNGP02.phx.gbl...
> http://support.microsoft.com/kb/321185/
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Clayton Sutton" <none@.none.com> wrote in message
> news:JxOQh.216567$Jt2.120445@.fe04.news.easynews.co m...
>

How to find what SQL 2000 service pack is running?

Hey everyone,
We are running SQL 2000 and I just want to know how to find what service
pack is installed and running on the system.
TIA,
Clayton
P.S.: I wrote an iTunes podcast tutorial and just want to publicize it.
You can find it at: http://www.nikoli.net/itunepod
*******************http://support.microsoft.com/kb/321185/
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Clayton Sutton" <none@.none.com> wrote in message
news:JxOQh.216567$Jt2.120445@.fe04.news.easynews.com...
> Hey everyone,
> We are running SQL 2000 and I just want to know how to find what service
> pack is installed and running on the system.
> --
> TIA,
>
> Clayton
>
> P.S.: I wrote an iTunes podcast tutorial and just want to publicize it.
> You can find it at: http://www.nikoli.net/itunepod
> *******************
>
>|||Thanks Aaron, that did the trick!
TIA,
Clayton
P.S.: I wrote an iTunes podcast tutorial and just want to publicize it.
You can find it at: http://www.nikoli.net/itunepod
*******************
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eHyPAdsdHHA.3960@.TK2MSFTNGP02.phx.gbl...
> http://support.microsoft.com/kb/321185/
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Clayton Sutton" <none@.none.com> wrote in message
> news:JxOQh.216567$Jt2.120445@.fe04.news.easynews.com...
>sql

How to find what SQL 2000 service pack is running?

Hey everyone,
We are running SQL 2000 and I just want to know how to find what service
pack is installed and running on the system.
--
TIA,
Clayton
P.S.: I wrote an iTunes podcast tutorial and just want to publicize it.
You can find it at: http://www.nikoli.net/itunepod
*******************http://support.microsoft.com/kb/321185/
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Clayton Sutton" <none@.none.com> wrote in message
news:JxOQh.216567$Jt2.120445@.fe04.news.easynews.com...
> Hey everyone,
> We are running SQL 2000 and I just want to know how to find what service
> pack is installed and running on the system.
> --
> TIA,
>
> Clayton
>
> P.S.: I wrote an iTunes podcast tutorial and just want to publicize it.
> You can find it at: http://www.nikoli.net/itunepod
> *******************
>
>|||Thanks Aaron, that did the trick!
--
TIA,
Clayton
P.S.: I wrote an iTunes podcast tutorial and just want to publicize it.
You can find it at: http://www.nikoli.net/itunepod
*******************
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHyPAdsdHHA.3960@.TK2MSFTNGP02.phx.gbl...
> http://support.microsoft.com/kb/321185/
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Clayton Sutton" <none@.none.com> wrote in message
> news:JxOQh.216567$Jt2.120445@.fe04.news.easynews.com...
>> Hey everyone,
>> We are running SQL 2000 and I just want to know how to find what service
>> pack is installed and running on the system.
>> --
>> TIA,
>>
>> Clayton
>>
>> P.S.: I wrote an iTunes podcast tutorial and just want to publicize it.
>> You can find it at: http://www.nikoli.net/itunepod
>> *******************
>>
>>
>

How to find the SQL start up acct from SQL?

Is there a system stored procedure to show the SQL Server Startup Service
Account id?
Thanks
Alex
No. You can read it from the registry though using
xp_instance_regread. If you run profiler while checking the
value in Enterprise Manager, you can see what is used to
view the account in EM - it's along the lines of:
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
-Sue
On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
Au@.discussions.microsoft.com> wrote:

>Is there a system stored procedure to show the SQL Server Startup Service
>Account id?
>Thanks
>Alex
|||Thank you very much. I did venture into the registry but did not know the
xp_regread stored procedure to read registry from within SQL Server.
I am developing a sp to kill all active users apart from the processes run
by Service account, hence the question.
Thanks again
"Sue Hoegemeier" wrote:

> No. You can read it from the registry though using
> xp_instance_regread. If you run profiler while checking the
> value in Enterprise Manager, you can see what is used to
> view the account in EM - it's along the lines of:
> DECLARE @.serviceaccount varchar(100)
> EXECUTE master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
> N'ObjectName',
> @.ServiceAccount OUTPUT,
> N'no_output'
> SELECT @.Serviceaccount
> -Sue
> On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
> Au@.discussions.microsoft.com> wrote:
>
>
|||you might also like to try this...
if object_id('tempdb..#Results')is not null drop table #Results
create table #Results(value nvarchar(4000))
insert into #Results(value)
exec xp_cmdshell 'wmic service get name,startname'
select
*
from
#Results
where
value like 'MSSQLSERVER %'
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Alex Au" wrote:

> Is there a system stored procedure to show the SQL Server Startup Service
> Account id?
> Thanks
> Alex

How to find the SQL start up acct from SQL?

Is there a system stored procedure to show the SQL Server Startup Service
Account id?
Thanks
AlexNo. You can read it from the registry though using
xp_instance_regread. If you run profiler while checking the
value in Enterprise Manager, you can see what is used to
view the account in EM - it's along the lines of:
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQ
LSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
-Sue
On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
Au@.discussions.microsoft.com> wrote:

>Is there a system stored procedure to show the SQL Server Startup Service
>Account id?
>Thanks
>Alex|||Thank you very much. I did venture into the registry but did not know the
xp_regread stored procedure to read registry from within SQL Server.
I am developing a sp to kill all active users apart from the processes run
by Service account, hence the question.
Thanks again
"Sue Hoegemeier" wrote:

> No. You can read it from the registry though using
> xp_instance_regread. If you run profiler while checking the
> value in Enterprise Manager, you can see what is used to
> view the account in EM - it's along the lines of:
> DECLARE @.serviceaccount varchar(100)
> EXECUTE master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'SYSTEM\CurrentControlSet\Services\MSSQ
LSERVER',
> N'ObjectName',
> @.ServiceAccount OUTPUT,
> N'no_output'
> SELECT @.Serviceaccount
> -Sue
> On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
> Au@.discussions.microsoft.com> wrote:
>
>|||you might also like to try this...
if object_id('tempdb..#Results')is not null drop table #Results
create table #Results(value nvarchar(4000))
insert into #Results(value)
exec xp_cmdshell 'wmic service get name,startname'
select
*
from
#Results
where
value like 'MSSQLSERVER %'
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Alex Au" wrote:

> Is there a system stored procedure to show the SQL Server Startup Service
> Account id?
> Thanks
> Alex

How to find the SQL start up acct from SQL?

Is there a system stored procedure to show the SQL Server Startup Service
Account id?
Thanks
AlexNo. You can read it from the registry though using
xp_instance_regread. If you run profiler while checking the
value in Enterprise Manager, you can see what is used to
view the account in EM - it's along the lines of:
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
-Sue
On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
Au@.discussions.microsoft.com> wrote:
>Is there a system stored procedure to show the SQL Server Startup Service
>Account id?
>Thanks
>Alex|||Thank you very much. I did venture into the registry but did not know the
xp_regread stored procedure to read registry from within SQL Server.
I am developing a sp to kill all active users apart from the processes run
by Service account, hence the question.
Thanks again
"Sue Hoegemeier" wrote:
> No. You can read it from the registry though using
> xp_instance_regread. If you run profiler while checking the
> value in Enterprise Manager, you can see what is used to
> view the account in EM - it's along the lines of:
> DECLARE @.serviceaccount varchar(100)
> EXECUTE master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
> N'ObjectName',
> @.ServiceAccount OUTPUT,
> N'no_output'
> SELECT @.Serviceaccount
> -Sue
> On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
> Au@.discussions.microsoft.com> wrote:
> >Is there a system stored procedure to show the SQL Server Startup Service
> >Account id?
> >
> >Thanks
> >Alex
>|||you might also like to try this...
if object_id('tempdb..#Results')is not null drop table #Results
create table #Results(value nvarchar(4000))
insert into #Results(value)
exec xp_cmdshell 'wmic service get name,startname'
select
*
from
#Results
where
value like 'MSSQLSERVER %'
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Alex Au" wrote:
> Is there a system stored procedure to show the SQL Server Startup Service
> Account id?
> Thanks
> Alexsql

How to find out the total number of commited transactions on SQL2k?

Dear all,

Previously I posted this issue on SSIS newsgroups and I did not obtain any response so that I'll do here.

Using this system function (::fn_log(null,null)) you can find out how many transactions have been confirmed in your .LDF. Although by means of another system function you can see further information, such as statitical.

Any help would be very appreciated.

Thanks in advance and regards,

sp_monitor among them

How to find out the reason for error 17883?

Hello, ereryone

I use MSDE in my system, and sometimes the following error occurs in MSDE. After that, I can not even access MSDE with the osql command.

Can anyone give some advice as how to deal with this kind of error or how to find the real reason? I have checked MS homepage, but I can not get the details of it.

Error: 17883, Severity: 1, State: 0 The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x003B70F8

Thanks in advance.

check this: http://support.microsoft.com/kb/319892/

How to find nth lowest value in a row

Morning all,
I have a table with 500+ fields of numeric data (a time series of values) I
import from an external system. I need to find the 5th and 6th lowest values
in a given row to perform some calculations.
I currently do this by transposing the data row ie turning the row into a
column sorting it and then using row_num to get the 5th and 6th lowest
values. This transpose is a real bottleneck on my process.
Does anyone have any alternatives to my method ? All suggestions gratefully
received.
Thanks
WOn Thu, 16 Aug 2007 17:56:35 -0700, willcas wrote:
>Morning all,
>I have a table with 500+ fields of numeric data (a time series of values) I
>import from an external system. I need to find the 5th and 6th lowest values
>in a given row to perform some calculations.
>I currently do this by transposing the data row ie turning the row into a
>column sorting it and then using row_num to get the 5th and 6th lowest
>values. This transpose is a real bottleneck on my process.
>Does anyone have any alternatives to my method ? All suggestions gratefully
>received.
Hi W,
Perhaps you could transpose the data during the import and store it in a
more relational way?
The only other alternative would be a really very nasty (and long!) CASE
epxression that I won't even begin to think about <shudder>.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"willcas" <willcas@.discussions.microsoft.com> wrote in message
news:6658592C-0F0A-45C6-BB9C-DFDDF0263FF2@.microsoft.com...
> Morning all,
> I have a table with 500+ fields of numeric data (a time series of values)
> I
> import from an external system. I need to find the 5th and 6th lowest
> values
> in a given row to perform some calculations.
> I currently do this by transposing the data row ie turning the row into a
> column sorting it and then using row_num to get the 5th and 6th lowest
> values. This transpose is a real bottleneck on my process.
> Does anyone have any alternatives to my method ? All suggestions
> gratefully
> received.
> Thanks
> W
I would treat this as a staging table and transform the data to a properly
normalized model. Then write your query against that new model.
Perhaps better still, do the transformation before the load (using
Integration Services for example).
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can't you use unpivot for the transposition?
"willcas" <willcas@.discussions.microsoft.com> wrote in message
news:6658592C-0F0A-45C6-BB9C-DFDDF0263FF2@.microsoft.com...
> Morning all,
> I have a table with 500+ fields of numeric data (a time series of values)
> I
> import from an external system. I need to find the 5th and 6th lowest
> values
> in a given row to perform some calculations.
> I currently do this by transposing the data row ie turning the row into a
> column sorting it and then using row_num to get the 5th and 6th lowest
> values. This transpose is a real bottleneck on my process.
> Does anyone have any alternatives to my method ? All suggestions
> gratefully
> received.
> Thanks
> W

how to find if index key is ASC or DESC from system tables?

There is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.

Thank you.
Regards,
Anuj GoyalWhen defining indexes, you can specify whether the data for each column is stored in ascending or descending order. If neither direction is specified, ascending is the default.

You can use INDEXKEY_PROPERTY to know the order, refer to books online for more information.

How to find how Activation has failed

I have seen the posting on determining how activation has failed and looking through the system logs is very helpful in determining why activation is not occuring, however, short of looking through the SQL Server logs is there another way to get the same information? Access to the SQL Server logs is fairly restricted. Does anyone know another way that this can be done? I have used the execute as technique described in the article on "Troubleshooting Activation Stored Procedures" and found it helpful in some cases.

Gary

There is a profiler trace event for "Broker:Activation" which should report an event when an activated task is aborted. However, if the activation stored procedure produces an output due to an error, this only gets logged to ERRORLOG and event log. Unfortunately, SQL Server 2005 does not have a better error reporting mechanism. However, you can wrap your stored procedure in TRY/CATCH block and log exceptions to a table. That will at least catch exceptions in the stored proc itself.

Hope that helps,
Rushi

|||Thanks Rushi,

I guess I can work with that. The problem is that priveledges to see the ERRORLOG and Event logs aren't typically given to developers. I know that now in 2005 you can profile without being SA which is a big help.

Thank you for your prompt reply,

Gary

|||

We certainly need better logging mechanism for activated tasks than writing to ERRORLOG. If you have suggestions please write to product feedback, which will help in planning future releases.

Thanks,
Rushi

|||Thanks Rushi,
I added feedback for this issue.

Gary

How to find group user login Name

Our system administrator set up an NT server group in order to allow
our users to login to our application via https to our sql server.
The group appears as a User in SQL Server when you look at it in
Enterprise Manager. That said, I can not see the users associated
with the group from Enterprise Manager, but know they can login to the
database.
The problem is this. When we login via the web we get access to the
database without problem, but when you look at the current_user what
you see is the login Name the user entered and NOT the name of the
group/User. That is to say, I can see a UserID which is not listed as
a User in SQL Server and can't see the name of the group, which is
listed as a user in SQL Server.
I need to know who's logging in order to direct them to the
appropriate web page via their role. Before the admin set up the
group, I was using sp_helpuser to get the role, but then again I had
the userID to do this.
The question I have now, is there any way to see what the
group/user is who logged in i.e. the goup listed as the User in
Enterprise Manager? Otherwise I have to build a table of userIDs and
their group/User name, which seems to defeat the purpose of having he
server authenticate users.

Thanks,
Tom"Tom Loach" <tcl4p@.virginia.edu> wrote in message
news:o7vttvktkbdbib39pqklga3f8s9lgdsksc@.4ax.com...
> Our system administrator set up an NT server group in order to allow
> our users to login to our application via https to our sql server.
> The group appears as a User in SQL Server when you look at it in
> Enterprise Manager. That said, I can not see the users associated
> with the group from Enterprise Manager, but know they can login to the
> database.
> The problem is this. When we login via the web we get access to the
> database without problem, but when you look at the current_user what
> you see is the login Name the user entered and NOT the name of the
> group/User. That is to say, I can see a UserID which is not listed as
> a User in SQL Server and can't see the name of the group, which is
> listed as a user in SQL Server.
> I need to know who's logging in order to direct them to the
> appropriate web page via their role. Before the admin set up the
> group, I was using sp_helpuser to get the role, but then again I had
> the userID to do this.
> The question I have now, is there any way to see what the
> group/user is who logged in i.e. the goup listed as the User in
> Enterprise Manager? Otherwise I have to build a table of userIDs and
> their group/User name, which seems to defeat the purpose of having he
> server authenticate users.
> Thanks,
> Tom

I'm not sure I understand your setup completely, but if you use CURRENT_USER
you will see the user name in the current database, not the login name. If
you use SYSTEM_USER instead, you should see the user's login - does this
give the result you expect?

If not, then perhaps you could clarify the sequence of events used to set up
the group, espeically the difference between a server login and a database
user - this is what's not entirely clear from your post above. If you can
provide the actual SQL commands used to set up server access and permissions
for the Windows group, that would be the least ambiguous description,
although of course you may well have done it through Enterprise Manager
instead.

Simon|||Simon,
thanks for your suggestion, but alas it did not work. Iam sorrry
I was not able to make the situation more clear, but perhaps another
try.
We have a group account set up, which if you look at in Enterprise
manager appears as a User i.e. a_Group. Obviously, since this is a
group account it is comprised of multiple users who need access to SQL
Server. The problem we're having is, if you login to the system and
if I run a stored procedure that returns the Current_User, what I get
is not the name of the User i.e. a_Group that appears in Enterprise
Manager, but the UserID i.e. h3tcl that was used to login to the
Windows server.
When I tried to exec sp_helpuser using Current_User I get an error
since SQL Server does not know who the user is (it sees h3tcl). If I
exec sp_helpuser and enter the user name (a_Group) found in
Enterprise manager sp_helpuser works fine and returns the appropriate
data from SQL Server.
So I'm back to the problem is how and where do I look for the group
name? If you have any other ideas I'm more than open. Thanks again
for your input.

Regards,
Tom

On Tue, 16 Dec 2003 16:44:21 +0100, "Simon Hayes" <sql@.hayes.ch>
wrote:

>"Tom Loach" <tcl4p@.virginia.edu> wrote in message
>news:o7vttvktkbdbib39pqklga3f8s9lgdsksc@.4ax.com...
>> Our system administrator set up an NT server group in order to allow
>> our users to login to our application via https to our sql server.
>> The group appears as a User in SQL Server when you look at it in
>> Enterprise Manager. That said, I can not see the users associated
>> with the group from Enterprise Manager, but know they can login to the
>> database.
>> The problem is this. When we login via the web we get access to the
>> database without problem, but when you look at the current_user what
>> you see is the login Name the user entered and NOT the name of the
>> group/User. That is to say, I can see a UserID which is not listed as
>> a User in SQL Server and can't see the name of the group, which is
>> listed as a user in SQL Server.
>> I need to know who's logging in order to direct them to the
>> appropriate web page via their role. Before the admin set up the
>> group, I was using sp_helpuser to get the role, but then again I had
>> the userID to do this.
>> The question I have now, is there any way to see what the
>> group/user is who logged in i.e. the goup listed as the User in
>> Enterprise Manager? Otherwise I have to build a table of userIDs and
>> their group/User name, which seems to defeat the purpose of having he
>> server authenticate users.
>>
>> Thanks,
>> Tom
>I'm not sure I understand your setup completely, but if you use CURRENT_USER
>you will see the user name in the current database, not the login name. If
>you use SYSTEM_USER instead, you should see the user's login - does this
>give the result you expect?
>If not, then perhaps you could clarify the sequence of events used to set up
>the group, espeically the difference between a server login and a database
>user - this is what's not entirely clear from your post above. If you can
>provide the actual SQL commands used to set up server access and permissions
>for the Windows group, that would be the least ambiguous description,
>although of course you may well have done it through Enterprise Manager
>instead.
>Simon

How to filter our system objects from the list

I'm using following statement to get the list of objects in given SQL
Server database (this is taken from VB6 code):
lstrSQL = "select name, refdate from dbo.sysobjects where " &
lstrFilter & " order by name"
I apply filter to narrow the results to particular type, for example
for procedures:
lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
For functions:
lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
N'IsTableFunction') = 1"
etc.
My question is: how can I filter out the objects created by the system
(I want only the objects created by the users to be left)?
Ideally, I'd like the solution to work in SQL Server 2005 and in the
older versions.
TIA
Dariusz Dziewialtowski.Use the xtype column to exclude the objects you don't want to show:
xtype: Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
"dariusz.dziewialtowski@.gmail.com" wrote:

> I'm using following statement to get the list of objects in given SQL
> Server database (this is taken from VB6 code):
> lstrSQL = "select name, refdate from dbo.sysobjects where " &
> lstrFilter & " order by name"
> I apply filter to narrow the results to particular type, for example
> for procedures:
> lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
> For functions:
> lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
> or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
> N'IsTableFunction') = 1"
> etc.
> My question is: how can I filter out the objects created by the system
> (I want only the objects created by the users to be left)?
> Ideally, I'd like the solution to work in SQL Server 2005 and in the
> older versions.
> TIA
> Dariusz Dziewialtowski.
>|||select *
from sysobjects
where xtype <> 'S'
"Edgardo Valdez, MCSD, MCDBA" wrote:
> Use the xtype column to exclude the objects you don't want to show:
> xtype: Object type. Can be one of these object types:
> C = CHECK constraint
> D = Default or DEFAULT constraint
> F = FOREIGN KEY constraint
> L = Log
> FN = Scalar function
> IF = Inlined table-function
> P = Stored procedure
> PK = PRIMARY KEY constraint (type is K)
> RF = Replication filter stored procedure
> S = System table
> TF = Table function
> TR = Trigger
> U = User table
> UQ = UNIQUE constraint (type is K)
> V = View
> X = Extended stored procedure
>
> "dariusz.dziewialtowski@.gmail.com" wrote:
>|||Try,
...
and objectproperty([id], 'IsMSShipped') = 0
AMB
"dariusz.dziewialtowski@.gmail.com" wrote:

> I'm using following statement to get the list of objects in given SQL
> Server database (this is taken from VB6 code):
> lstrSQL = "select name, refdate from dbo.sysobjects where " &
> lstrFilter & " order by name"
> I apply filter to narrow the results to particular type, for example
> for procedures:
> lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
> For functions:
> lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
> or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
> N'IsTableFunction') = 1"
> etc.
> My question is: how can I filter out the objects created by the system
> (I want only the objects created by the users to be left)?
> Ideally, I'd like the solution to work in SQL Server 2005 and in the
> older versions.
> TIA
> Dariusz Dziewialtowski.
>|||Use the IsMSShipped object property|||Edgardo, Alejandro, Scott
Thank you for your so quick responses!
I was thinking about using IsMSShipped in the past but I was afraid
that it wouldn't work right - for example: the whole Northwind database
is shipped by Microsoft - would the flag IsMSShipped be set to TRUE for
all objects in that database in that case?
Hmm, I have to test it actually...
Again - thanks a lot for your help!
Dariusz Dziewialtowski.|||That's a good point.
Actually, the IsMSShipped property is only set for those objects that
someone set it on for. Anyone can mark an object so that the IsMSShipped bit
is true by running sp_MS_marksystemobject.
So there is really no way to know for sure what is shipped by MS and what
isn't. You'll have to come up with another way of determining which objects
you want to see and which you don't.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<dariusz.dziewialtowski@.gmail.com> wrote in message
news:1144681248.829348.140770@.i40g2000cwc.googlegroups.com...
> Edgardo, Alejandro, Scott
> Thank you for your so quick responses!
> I was thinking about using IsMSShipped in the past but I was afraid
> that it wouldn't work right - for example: the whole Northwind database
> is shipped by Microsoft - would the flag IsMSShipped be set to TRUE for
> all objects in that database in that case?
> Hmm, I have to test it actually...
> Again - thanks a lot for your help!
>
> Dariusz Dziewialtowski.
>|||Kalen,
Thanks a lot for your explanation!
Dariusz Dziewialtowski