difference between SYSTEM_USER and USER

Excuse me, what is the difference between user and system_user and which
function should be used in audit trail table ?
Thanks.Hi

From BOL:
"If the current user is logged in to Microsoft SQL ServerT using Windows
Authentication, SYSTEM_USER returns the Windows 2000 or Windows NT 4.0 login
identification name, for example, DOMAIN\user_login_name. However, if the
current user is logged in to SQL Server using SQL Server Authentication,
SYSTEM_USER returns the SQL Server login identification name, for example,
sa for a user logged in as sa."

USER only works with SQL Security.
--
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Zlatko Mati" <zlatko.matic1@.sb.t-com.hr> wrote in message
news:d1i9uh$j37$1@.ls219.htnet.hr...
> Excuse me, what is the difference between user and system_user and which
> function should be used in audit trail table ?
> Thanks.|||I have noticed that system_user returns "sa" while user returns "dbo". What
is the difference and which to use in audit trail table?
Thanks.

"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> je napisao u poruci interesnoj
grupi:423cc1f1_3@.news.bluewin.ch...
> Hi
> From BOL:
> "If the current user is logged in to Microsoft SQL ServerT using Windows
> Authentication, SYSTEM_USER returns the Windows 2000 or Windows NT 4.0
> login
> identification name, for example, DOMAIN\user_login_name. However, if the
> current user is logged in to SQL Server using SQL Server Authentication,
> SYSTEM_USER returns the SQL Server login identification name, for example,
> sa for a user logged in as sa."
> USER only works with SQL Security.
> --
> ----------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Zlatko Mati" <zlatko.matic1@.sb.t-com.hr> wrote in message
> news:d1i9uh$j37$1@.ls219.htnet.hr...
>> Excuse me, what is the difference between user and system_user and which
>> function should be used in audit trail table ?
>> Thanks.
>>
>>
>>|||Zlatko Mati (zlatko.matic1@.sb.t-com.hr) writes:
> Excuse me, what is the difference between user and system_user and which
> function should be used in audit trail table ?

Depends on your business requirements.

SYSTEM_USER returns the server login, USER returns the database user. Often
a login maps to a database user with the same name, but at least for SQL
logins this does not have to be the case. (And I think it can happen with
Window logins as well, if you move a database from one server to
another.)

As a general answer, I would recommend SYSTEM_USER, unless there are some
special requirements that calls for something else.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Difference between SQL2k Enterprise & Developer editions?

What are the Differences between those two SQL versions
i mean except that with in the developer edition only 1 user can login

on what version is it better to develop ?
does the developer ver. include something that the entprise does not ? (or vice versa)
(say in theory i can choose on which version to work ;-))

thanx, max.Directly from Microsoft's site:

Developer edition is designed to allow developers to build any type of application on top of SQL Server. It includes all the functionality of Enterprise Edition but with a special development and test end-user license agreement (EULA) that prohibits production deployment. It is the ideal choice for Independent Software Vendors (ISVs), consultants, system integrators, solution providers, and corporate developers developing and testing applications because it is cost effective, runs on a variety of platforms, and can be upgraded for production use to SQL Server 2000 Enterprise Edition.

It is the only edition of SQL Server 2000 that gives the licensee the right to download and install SQL Server 2000 Windows CE Edition (SQL Server CE). The Developer Edition licensee also has the right to redistribute SQL Server CE-based applications to an unlimited number of devices at no additional cost beyond the purchase price of SQL Server 2000 Developer Edition.|||SQL Server 2000 has the following editions:

Personal Edition
Standard Edition
Enterprise Edition
Developer Edition
Desktop Engine
SQL Server CE
Evaluation Edition

Personal Edition can work on the Windows 98, Windows NT Server 4.0 with Service Pack 5 or later, Windows NT Workstation 4.0 with Service Pack 5 or later and on the all editions of Windows 2000. This edition is related to SQL Server 7.0 Desktop Edition.

This edition has some restrictions:

maximum 2 CPU
no Distributed Partitioned Views
no Log Shipping
no Parallel DBCC
no Parallel index creation
no Failover clustering
no publishing for transaction replication
maximum 2Gb RAM

Standard Edition can work on the Windows NT Server 4.0 with Service Pack 5, Windows NT Server 4.0 Enterprise Edition and on the Windows 2000 Server/Advanced Server/DataCenter.

This edition has the following restrictions:

maximum 4 CPU (up to 8 CPU on the Windows NT Enterprise Edition)
no Distributed Partitioned Views
no Log Shipping
no Parallel index creation
no Failover clustering
maximum 2Gb RAM

Enterprise Edition can work on the Windows NT Server 4.0 with Service Pack 5, Windows NT Server 4.0 Enterprise Edition and on the Windows 2000 Server/Advanced Server/DataCenter.

This edition can use:

up to 32 CPU on the Windows 2000 DataCenter up to 8 CPU on the Windows 2000 Advanced Server and on the Windows NT Server 4.0 Enterprise Edition up to 4 CPU on the Windows NT Server 4.0 and on the Windows 2000 Server
up to 64Gb RAM on the Windows 2000 DataCenter up to 8 Gb RAM on the Windows 2000 Advanced Server up to 4 Gb RAM on the Windows 2000 Server up to 3 Gb RAM on the Windows NT Server 4.0 Enterprise Edition up to 2 Gb RAM on the Windows NT Server 4.0
Distributed Partitioned Views
Log Shipping
Parallel index creation
Failover clustering
The Developer Edition can be used by developers to create and debug stored procedures and triggers. This edition comes with its own compact disc and can be upgraded to SQL Server 2000 Enterprise Edition.

The Desktop Engine has no graphical user interface and is related to the MSDE, not to the SQL Server 7.0 Desktop Edition. The size of Desktop Engine databases cannot exceed 2 GB. The Desktop Engine can use maximum 2 CPU.

The SQL Server CE edition can work only on the Microsoft Windows CE, so it has all restrictions of this operation system (can use only 1 CPU, no Parallel index creation, no Full-Text Search and so on).

The Evaluation Edition can be used only for the test purposes to learn more about the new features and enhancements and should be uninstalled after a 120-day evaluation period.

difference between Owned schemas and Roles members

Hi,
I want to create a login (for account ASPNET from the Active Directory) in
sql server express 2005 for a specific database.
When addidng a new user to a specific database, i see:
Owned schemas, where i take db_datareader and db_datawriter
Roles memmbers: also db_datareader and db_datawriter
What's the difference between both and are they both required fpr account
ASPNET?
Tbanks
BartThis is answered
"Bart" <b@.sdq.dc> schreef in bericht
news:%23buSVSjbHHA.1400@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I want to create a login (for account ASPNET from the Active Directory) in
> sql server express 2005 for a specific database.
> When addidng a new user to a specific database, i see:
> Owned schemas, where i take db_datareader and db_datawriter
> Roles memmbers: also db_datareader and db_datawriter
> What's the difference between both and are they both required fpr account
> ASPNET?
> Tbanks
> Bart
>

difference between dbo user and another user

hi !

what is difference between dbo user and another user?

Look in the BOL for mre information:

"Database Owner (dbo)
The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically."

HTH, Jens Suessmeyer.

|||In generic terms DBO user has all privileges against the database andSQL server and normal users privileges can be controlled by setting proper roles against it.
Read thru books online for DBO and security for more information.
http://www.sqlsecurity.com is a good website to catchup security information.

Difference between database owner and user who has db-owner?

What is difference between database owner and user in the users folder who
has db-owner?
Database owner has full access under the database. Also user who has db-
owner has full access under the database.
When both create a table, table owner is dbo.
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.com> When both create a table, table owner is dbo.
No. A user who isn't dbo but is db_owner will now have dbo as owner of the object. This is indeed
the major (and possibly only) difference between being dbo and having the db_owner role.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:48a04eefb96840aea85cee6c4fd2f178@.SQLMonster.com...
> What is difference between database owner and user in the users folder who
> has db-owner?
> Database owner has full access under the database. Also user who has db-
> owner has full access under the database.
> When both create a table, table owner is dbo.
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com

Difference between database owner and user who has db-owner?

What is difference between database owner and user in the users folder who
has db-owner?
Database owner has full access under the database. Also user who has db-
owner has full access under the database.
When both create a table, table owner is dbo.
Thanks in advance,
Do.
Message posted via http://www.droptable.com> When both create a table, table owner is dbo.
No. A user who isn't dbo but is db_owner will now have dbo as owner of the o
bject. This is indeed
the major (and possibly only) difference between being dbo and having the db
_owner role.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:48a04eefb96840aea85cee6c4fd2f178@.SQ
droptable.com...
> What is difference between database owner and user in the users folder who
> has db-owner?
> Database owner has full access under the database. Also user who has db-
> owner has full access under the database.
> When both create a table, table owner is dbo.
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.comsql

Difference between database owner and user who has db-owner?

What is difference between database owner and user in the users folder who
has db-owner?
Database owner has full access under the database. Also user who has db-
owner has full access under the database.
When both create a table, table owner is dbo.
Thanks in advance,
Do.
Message posted via http://www.sqlmonster.com
> When both create a table, table owner is dbo.
No. A user who isn't dbo but is db_owner will now have dbo as owner of the object. This is indeed
the major (and possibly only) difference between being dbo and having the db_owner role.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:48a04eefb96840aea85cee6c4fd2f178@.SQLMonster.c om...
> What is difference between database owner and user in the users folder who
> has db-owner?
> Database owner has full access under the database. Also user who has db-
> owner has full access under the database.
> When both create a table, table owner is dbo.
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com

Did anyone get this error on a backup job?!

Additional information --> Job Owner: SA

In my DB Backup JOB my first step is to map a network path like this
NET USE Z:\\SERVER2\DADOS /user somebody 234567

I got this Error --> Executed as user: MYSERVER\SYSTEM. System error 1312 has occurred. A specified logon session does not exist. It may already have been terminated. Process Exit Code 2. The step failed.

In step 2 i copy all my *.bak files from yesterday to the drive mapped on dir BACKUP in SERVER2.

COPY "C:\BACKUP\*.BAK" "Z:\BACKUP\"

I got this Error --> Executed as user: MYSERVER\SYSTEM. ...annot find the path specified. C:\BACKUP\db_200511030200.BAK The system cannot find the path specified. C:\BACKUP\CFG_db_200511030225.BAK The system cannot find the path specified. C:\BACKUP\STRUCTAL_db_200511030225.BAK The system cannot find the path specified. C:\BACKUP\STRUCTEN_db_200511030225.BAK The system cannot find the path specified.

step 3 i delete all files in MYSERVER C:\BACKUP\*.BAK

This step is OK

step 4 i execute my database maintenance plan

the .bak file are generated and put in "C:\BACKUPS" in MYSERVER

This step is OK

Thanks for all help.First you need to resolve the 1312 error. It may be caused by some sort of delay that occurred between a network connection request initiated by NET USE and the actuaql attempt to map the drive. While on the topic, why do you need to map the drive to begin with?

Second, you need to make sure that the step is executed successfully before moving on. You need to either check for errorlevel or take the step out of the batch and put it by itself into a separate step within the job, and then allow continuation OnSuccess.

Diagnose SQL Procedures.

Hi there,
Is there any way I can create one user defined stored procedure that will
let me know all the stored procedure and functions in current database have
compiled successfully, there is no compilation error in the stored
procedures ?
Basically, I have a database and there are around 90 stored procedure and I
want to check all the stored procedures compiled successfully or still they
need to compiled so I need any query or stored procedure through which I can
diagnose this...
Thanks in advance
What exactly do you mean by compile? Sp's only get compiled the first time
they are run. They get parsed and checked when you create them. There really
isn't anything such as an unsuccessful compilation.
Andrew J. Kelly SQL MVP
"Rogers" <naissani@.hotmail.com> wrote in message
news:%23VA1H30MGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> Is there any way I can create one user defined stored procedure that will
> let me know all the stored procedure and functions in current database
> have compiled successfully, there is no compilation error in the stored
> procedures ?
> Basically, I have a database and there are around 90 stored procedure and
> I want to check all the stored procedures compiled successfully or still
> they need to compiled so I need any query or stored procedure through
> which I can diagnose this...
> Thanks in advance
>
|||Andrew J. Kelly wrote:
> What exactly do you mean by compile? Sp's only get compiled the
> first time they are run. They get parsed and checked when you create
> them. There really isn't anything such as an unsuccessful compilation.
Maybe he wants to know how to verify existing procedures against a
schema that has changed. If that's the case, there's some information
here that might help:
http://groups.google.com/group/micro...eb685e696368c1
David Gugick - SQL Server MVP
Quest Software

Developer edition installation error on XP Professional

When trying to specify the Service settings Domain User account during SQL
Server 2000 developer edition installation on an XP Pro (SP2) machine I get
the following error message: "The logon account cannot be validated for the
SQL Server service. Verify that the username and password entered are
correct. The logon attempt failed." I have tried a variety of accounts
(including administrator) and always get the same error message. The "Local
System account" option seems to proceed without error, but that doesn't meet
my application requirements.
Any help would be greatly appreciated, thanks.
- Daniel
Hi
XP needs to verify the account credentials with domain controller that thje
XP machine is in. It can't contact the DC, so it passes the error to SQL
Server installation.
Check your connectivity to your DC's.
Regards
Mike
"Daniel Scheidt" wrote:

> When trying to specify the Service settings Domain User account during SQL
> Server 2000 developer edition installation on an XP Pro (SP2) machine I get
> the following error message: "The logon account cannot be validated for the
> SQL Server service. Verify that the username and password entered are
> correct. The logon attempt failed." I have tried a variety of accounts
> (including administrator) and always get the same error message. The "Local
> System account" option seems to proceed without error, but that doesn't meet
> my application requirements.
> Any help would be greatly appreciated, thanks.
> - Daniel
|||Thanks Mike, my machine is a stand-alone box not connected to any DC. I'm
using the machine name in the "Domain:" field. This approach has worked fine
for me on Windows 2003 Server boxes, but I'm stuck on this XP install
- Daniel
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> XP needs to verify the account credentials with domain controller that thje
> XP machine is in. It can't contact the DC, so it passes the error to SQL
> Server installation.
> Check your connectivity to your DC's.
> Regards
> Mike
> "Daniel Scheidt" wrote:

Developer edition installation error on XP Professional

When trying to specify the Service settings Domain User account during SQL
Server 2000 developer edition installation on an XP Pro (SP2) machine I get
the following error message: "The logon account cannot be validated for the
SQL Server service. Verify that the username and password entered are
correct. The logon attempt failed." I have tried a variety of accounts
(including administrator) and always get the same error message. The "Local
System account" option seems to proceed without error, but that doesn't meet
my application requirements.
Any help would be greatly appreciated, thanks.
- DanielHi
XP needs to verify the account credentials with domain controller that thje
XP machine is in. It can't contact the DC, so it passes the error to SQL
Server installation.
Check your connectivity to your DC's.
Regards
Mike
"Daniel Scheidt" wrote:
> When trying to specify the Service settings Domain User account during SQL
> Server 2000 developer edition installation on an XP Pro (SP2) machine I get
> the following error message: "The logon account cannot be validated for the
> SQL Server service. Verify that the username and password entered are
> correct. The logon attempt failed." I have tried a variety of accounts
> (including administrator) and always get the same error message. The "Local
> System account" option seems to proceed without error, but that doesn't meet
> my application requirements.
> Any help would be greatly appreciated, thanks.
> - Daniel|||Thanks Mike, my machine is a stand-alone box not connected to any DC. I'm
using the machine name in the "Domain:" field. This approach has worked fine
for me on Windows 2003 Server boxes, but I'm stuck on this XP install
- Daniel
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> XP needs to verify the account credentials with domain controller that thje
> XP machine is in. It can't contact the DC, so it passes the error to SQL
> Server installation.
> Check your connectivity to your DC's.
> Regards
> Mike
> "Daniel Scheidt" wrote:
> > When trying to specify the Service settings Domain User account during SQL
> > Server 2000 developer edition installation on an XP Pro (SP2) machine I get
> > the following error message: "The logon account cannot be validated for the
> > SQL Server service. Verify that the username and password entered are
> > correct. The logon attempt failed." I have tried a variety of accounts
> > (including administrator) and always get the same error message. The "Local
> > System account" option seems to proceed without error, but that doesn't meet
> > my application requirements.
> >
> > Any help would be greatly appreciated, thanks.
> >
> > - Daniel

Developer edition installation error on XP Professional

When trying to specify the Service settings Domain User account during SQL
Server 2000 developer edition installation on an XP Pro (SP2) machine I get
the following error message: "The logon account cannot be validated for the
SQL Server service. Verify that the username and password entered are
correct. The logon attempt failed." I have tried a variety of accounts
(including administrator) and always get the same error message. The "Local
System account" option seems to proceed without error, but that doesn't meet
my application requirements.
Any help would be greatly appreciated, thanks.
- DanielHi
XP needs to verify the account credentials with domain controller that thje
XP machine is in. It can't contact the DC, so it passes the error to SQL
Server installation.
Check your connectivity to your DC's.
Regards
Mike
"Daniel Scheidt" wrote:

> When trying to specify the Service settings Domain User account during SQL
> Server 2000 developer edition installation on an XP Pro (SP2) machine I ge
t
> the following error message: "The logon account cannot be validated for th
e
> SQL Server service. Verify that the username and password entered are
> correct. The logon attempt failed." I have tried a variety of accounts
> (including administrator) and always get the same error message. The "Loca
l
> System account" option seems to proceed without error, but that doesn't me
et
> my application requirements.
> Any help would be greatly appreciated, thanks.
> - Daniel|||Thanks Mike, my machine is a stand-alone box not connected to any DC. I'm
using the machine name in the "Domain:" field. This approach has worked fine
for me on Windows 2003 Server boxes, but I'm stuck on this XP install
- Daniel
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> XP needs to verify the account credentials with domain controller that th
je
> XP machine is in. It can't contact the DC, so it passes the error to SQL
> Server installation.
> Check your connectivity to your DC's.
> Regards
> Mike
> "Daniel Scheidt" wrote:
>

Develop multiusers applications with SQL Server Express

Can I develop applications with SQL Server Express that I have more than one user in several computers working with the same database on a server, or I can only develop applications with only one user with a local database with SQL Server Express?

SQL Server Express will support multiple users. To connect from other computers you must first enable "Local and remote connections" in the Surface Area configuration tool .

Karl

|||

You should also read the Blog entry at http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx. You may need to enable SQL Browser and create Excpetions in your Firewall to allow people to connect to your SQL Express server. It all depends on the configuration of your computer and how you want to connect.

Regards,
Mike Wachal
SQL Express

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.
Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/default...&Product=sql2k
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>
|||Some information here as well
http://www.aspfaq.com/2446
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/default.aspx?scid=kb;en-us;834846&Product=sql2k
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>|||Some information here as well
http://www.aspfaq.com/2446
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/defaul...6&Product=sql2k
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>|||Some information here as well
http://www.aspfaq.com/2446
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

determining what are "system" objects in sp_help or system tables

Hi,

I have a few things on my databases which seem to be neither true system
objects or user objects - notably a table called 'dtproperties' (created
by Enterprise manager as I understand, relating to relationship graphing
or something) and some stored procs begining with "dt_" (some kind of
source control stuff, possible visual studio related). These show up when
I use

"exec sp_help 'databaseName'"

but not in Ent. Mgr. or in Query Analyzer's object browser, and also not
in a third party tool I use called AdeptSQL. I am wondering how those
tools know to differentiate between these types of quasi-system objects,
and my real user data. (This is for the purpose of a customized schema
generator I am writing). I'd prefer to determine this info with system
stored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into the
system tables if needed.

Thanks,
DaveDave C. (spam@.spam.spam) writes:
> I have a few things on my databases which seem to be neither true system
> objects or user objects - notably a table called 'dtproperties' (created
> by Enterprise manager as I understand, relating to relationship graphing
> or something) and some stored procs begining with "dt_" (some kind of
> source control stuff, possible visual studio related). These show up when
> I use
> "exec sp_help 'databaseName'"
> but not in Ent. Mgr. or in Query Analyzer's object browser, and also not
> in a third party tool I use called AdeptSQL. I am wondering how those
> tools know to differentiate between these types of quasi-system objects,
> and my real user data. (This is for the purpose of a customized schema
> generator I am writing). I'd prefer to determine this info with system
> stored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into the
> system tables if needed.

select objectproperty(object_id('tbl'), 'IsMSShipped') reveals these
funny tables.

An answer by the way, that probably can be retrieved by spying on the
various tools with the SQL Profiler.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Determining User Role via DMO

I am trying to find a way to check if a user belongs to a program
specific role. Previously we had been using:
// Not Exact but I think it can be followed
hr = m_pDmoSrvr->GetTrueLogin(&login);
LPSQLDMOUSER pUser = 0;
hr = m_pDmoDb->GetUserByName(login, &pUser);
hr = pUser->IsMember(exclRole, &b);
But this does not seem to work if the user is a member of an NT Group
that has been added to the exclRole Role. I may not know what Group was
used to assign the Role.
Is there a way to determine whether the connecting user belongs to a
Role? I tried getting a LPSQLDMOLOGIN and using that but it did not
seem to work.
I'd be grateful for any pointers.
OtisIt looks like your checking to see of a login name, or windows username is a
member of a database role. Logins are not members of database roles; databas
e
users are. You want to test to see if the user that their login maps to is a
member of a database role, not if their login is a member of the role.
"OBQuiet" wrote:

> I am trying to find a way to check if a user belongs to a program
> specific role. Previously we had been using:
> // Not Exact but I think it can be followed
> hr = m_pDmoSrvr->GetTrueLogin(&login);
> LPSQLDMOUSER pUser = 0;
> hr = m_pDmoDb->GetUserByName(login, &pUser);
> hr = pUser->IsMember(exclRole, &b);
> But this does not seem to work if the user is a member of an NT Group
> that has been added to the exclRole Role. I may not know what Group was
> used to assign the Role.
> Is there a way to determine whether the connecting user belongs to a
> Role? I tried getting a LPSQLDMOLOGIN and using that but it did not
> seem to work.
> I'd be grateful for any pointers.
> Otis
>|||"examnotes"
<MarkWilliams@.discussions.microsoft.com> wrote in
news:1F9B3D42-70A4-4474-87A6-7579B2AC8E0D@.microsoft.com:

> It looks like your checking to see of a login name, or windows
> username is a member of a database role. Logins are not members of
> database roles; database users are. You want to test to see if the
> user that their login maps to is a member of a database role, not if
> their login is a member of the role.
>
I believe you are correct but I have not been able to trace through the
options of the SQL-DMO objects to figure out how to manage it.
It seemed like the code I posted was getting the user object. But, at that
level, it seems to only check if the object itself is a member. It does not
check that the object(user) is a member of a group that are all members of
a role. That is what I have to figure out how to do.
Otis

Determining the OS Version that SQL is running on

I am writing a client application that offers an UI that allows an administrator to remotely add/delete/update user accounts accross many different SQL Servers running on XP and up.

When the operating system is W2K3 or higher I want to take advantage of the "check_expiration, check_policy, must_change' arguments to create login and exclude those features when the host OS does not support them.

Is there an easy way to determine if those arguments are supported?

Thanks

Mark

The OS version information is returned with @.@.VERSION. You will have to parse it a bit to find the Version, Edition, and Build -but it's all there.|||

or you can try this .. in sql server 2005 you have some limitation with XP_Cmdshell ... it may not be enabled by default...

EXEC master..xp_cmdshell 'netsh diag SHOW os /p'

for more details refer this link : http://www.sqlmag.com/Article/ArticleID/46062/sql_server_46062.html

Madhu

|||

Also exec xp_msver will return the OS version (I think it is line 15 in the result set)

hth,

-Steven Gott

S/DET

SQL Server

|||

exec xp_msver is exactly what I am looking for

Thanks!

Determining permissions through Stored Procedures

Is it possible in SQL 2005 to determine what rights a user has to a given DB
(down to the table level) using a Stored Procedure, or Function? I would lik
e
to know if a user has "Insert" rights to a table before I give them an "Add
New" button on my form.
Thanks
DaveDave,
Using sp_helprotect is the traditional method, but it does not return
information about securables introduced in SQL Server 2005. You can use
sys.database_permissions and fn_builtin_permissions instead.
Here is something I use for quick checks that might help you get started.
select u.name, p.permission_name, p.class_desc, object_name(p.major_id)
ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u on m.role_principal_id = u.principal_id
join sys.database_principals u2 on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:CB769D2E-1E60-440D-A474-C44B36C6860E@.microsoft.com...
> Is it possible in SQL 2005 to determine what rights a user has to a given
> DB
> (down to the table level) using a Stored Procedure, or Function? I would
> like
> to know if a user has "Insert" rights to a table before I give them an
> "Add
> New" button on my form.
> Thanks
> Dave
>|||Dave (Dave@.discussions.microsoft.com) writes:
> Is it possible in SQL 2005 to determine what rights a user has to a
> given DB (down to the table level) using a Stored Procedure, or
> Function? I would like to know if a user has "Insert" rights to a table
> before I give them an "Add New" button on my form.
Check out the function Has_perms_by_name in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx