adding windows user via sp_cmdshell

assuming SQL server nt service is started under domain user with right to cr
eate windows user in domain, is there a way to execute sp_ in QA that allow
mw to create domain user, set password and add user to group in domain? if s
o can anyone provide this s
tatment.
Tom,Hi,
Yes. See the OS commands NET USER and NET GROUP in OS Help. You can use this
command from Query Anayzer using XP_CMDSHELL.
Sample
Master..XP_cmdshell 'net user Fin_user password /DOMAIN /ADD'
go
Master..XP_cmdshell 'net group Finance /DOMAIN /ADD'
go
For more details of command execute the below from command prompt
net user ?
net group ?
Thanks
Hari
MCDBA
"TOM P." <TOMP@.discussions.microsoft.com> wrote in message
news:E8D38151-CF4C-4EF9-A713-617E25BA2AEE@.microsoft.com...
> assuming SQL server nt service is started under domain user with right to
create windows user in domain, is there a way to execute sp_ in QA that
allow mw to create domain user, set password and add user to group in
domain? if so can anyone provide this statment.
> Tom,|||Hello Hari,
I have tried it, but it did not work for me, I got:
The request will be processedat DC ...
System error 5 has occurred
Access denied.
I got this regardless if I'm using SA account to open Query Analizer or wind
ows auth... where am member of domain admin. any idea...
"Hari Prasad" wrote:

> Hi,
> Yes. See the OS commands NET USER and NET GROUP in OS Help. You can use th
is
> command from Query Anayzer using XP_CMDSHELL.
> Sample
>
> Master..XP_cmdshell 'net user Fin_user password /DOMAIN /ADD'
> go
> Master..XP_cmdshell 'net group Finance /DOMAIN /ADD'
> go
>
> For more details of command execute the below from command prompt
> net user ?
> net group ?
> Thanks
> Hari
> MCDBA
>
> "TOM P." <TOMP@.discussions.microsoft.com> wrote in message
> news:E8D38151-CF4C-4EF9-A713-617E25BA2AEE@.microsoft.com...
> create windows user in domain, is there a way to execute sp_ in QA that
> allow mw to create domain user, set password and add user to group in
> domain? if so can anyone provide this statment.
>
>|||Hi Tom
As Hari said it is possible, but difficult. The problem
here is its taking the userid of SQL Server instance that
the runs the xp_cmdshell and attempting to create users.
If that userid doesn't have the Server (not SQL)
permission to do its going to crash and burn.

>--Original Message--
>Hello Hari,
>I have tried it, but it did not work for me, I got:
>The request will be processedat DC ...
>System error 5 has occurred
>Access denied.
>I got this regardless if I'm using SA account to open
Query Analizer or windows auth... where am member of
domain admin. any idea...
>"Hari Prasad" wrote:
>
Help. You can use this[vbcol=seagreen]
password /DOMAIN /ADD'[vbcol=seagreen]
command prompt[vbcol=seagreen]
message[vbcol=seagreen]
617E25BA2AEE@.microsoft.com...[vbcol=seagreen]
domain user with right to[vbcol=seagreen]
execute sp_ in QA that[vbcol=seagreen]
user to group in[vbcol=seagreen]
>.
>|||Hi,
I agree with you peter. To do this you might need to start the MSSQL server
service using
a Domain Administrator account. I will not suggest you this.
I will not recommend you to create users / Groups from Query Analyzer.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2dc001c470c1$74fea120$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Tom
> As Hari said it is possible, but difficult. The problem
> here is its taking the userid of SQL Server instance that
> the runs the xp_cmdshell and attempting to create users.
> If that userid doesn't have the Server (not SQL)
> permission to do its going to crash and burn.
>
> Query Analizer or windows auth... where am member of
> domain admin. any idea...
> Help. You can use this
> password /DOMAIN /ADD'
> command prompt
> message
> 617E25BA2AEE@.microsoft.com...
> domain user with right to
> execute sp_ in QA that
> user to group in|||Agreed.

>--Original Message--
>Hi,
>I agree with you peter. To do this you might need to
start the MSSQL server
>service using
>a Domain Administrator account. I will not suggest you
this.
>I will not recommend you to create users / Groups from
Query Analyzer.
>Thanks
>Hari
>MCDBA
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2dc001c470c1$74fea120$a301280a@.phx.gbl...
that[vbcol=seagreen]
>
>.
>sql

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
--
- - - - - - - - -
Thanks
Yogish
"durai" wrote:
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
- - - - - - - - -
Thanks
Yogish
"durai" wrote:

> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?
Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
- - - - - - - - -
Thanks
Yogish
"durai" wrote:

> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>
|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Adding line sequence numbers

hi all

I am stuck with something that seems easy but im obviously clueless as how to execute the idea.

I have a custom table that houses invoices on the details level. So for example i have:

InvcNo
00000001
00000001
00000001
00000002
00000002
00000003

and so forth

What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:

SeqNo
1
2
3
1
2
1

I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.

ThanksWill this help?
http://www.sqlservercentral.com/columnists/mcoles/sequentialordering.asp|||Thanks...but I dont have 2005 (

Is it possible to pull the example that is given in 2005 section in a prior version of SQL?|||Can you uniquely identify each record for a specific InvcNo?|||Yes, I have an InvcKey column set up|||create table #t1 (InvcKey int, InvcNo int)
insert into #t1 select
1,1 union all select
2,1 union all select
3,1 union all select
4,2 union all select
5,2 union all select
6,3

select count(*) 'SeqNo', a.InvcKey, a.InvcNo from #t1 a, #t1 b
where a.InvcNo=b.InvcNo
and a.InvcKey>=b.InvcKey
group by a.InvcKey, a.InvcNo

drop table #t1|||Ok, Ive got it...I should have played around some before asking further

SELECT COUNT(*) AS [SeqNo], c.InvcNo, c.InvcKey
FROM table c, table d
WHERE d.InvcKey <= c.InvcKey AND d.InvcNo = cInvcNo
GROUP BY c.InvcNo, c.InvcKey
ORDER BY c.InvcKey

Thanks for the help!

tibor|||You should probably use a JOIN.
Using

FROM table1, table2, ... , tableN

Is not advised.|||Being a self join, would I really need JOIN?|||Being a self join, would I really need JOIN?It is semantics. The affect it the same but the style you used is considered old fashioned in many quarters. Whether or not it is a self join is irrelevent.

Rather excitingly - you will have created a Theta Join when you have finished :)|||Rather excitingly - you will have created a Theta Join when you have finished :)

neat! normally you'd have to pay $360k to get to that level (http://en.wikipedia.org/wiki/Operating_Thetan)!

Adding Execute permissions on Stored Procedures

How can i add Execute permissions on the Stored Procedures under the
created user permission iusing SQLDMO ?hi,
arfanameer@.hotmail.com wrote:
> How can i add Execute permissions on the Stored Procedures under the
> created user permission iusing SQLDMO ?

Dim oProcedure As SQLDMO.StoredProcedure
Dim oServer As SQLDMO.SQLServer
' connect to the server, etc...

'get a pointer to the procedure..
Set oProcedure = oServer.Databases("Pubs").StoredProcedures("byroyalty")
'grant EXEC permission to an existing user
oProcedure.Grant SQLDMOPriv_Execute, "user_name"

Set oProcedure = Nothing
Set oServer = Nothing

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply

Adding execute permission to stored procedures

Hi

I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2.

For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure.

In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user.

Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this.

If anyone can point me in the right direction on this...

I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005?

Thanking you in advance

A db owner can execute, alternatively you could add server roles to the login such as sysadmin may be overkill though

Function SetLoginsDbOwner()Dim strcmdAsString

strcmd = "exec sp_addrolemember N'db_owner', N'MyLoginName_IWantSPExecutePerm'"

Dim dcAsNew OleDbConnection(connstringL)Dim cmdAsNew OleDbCommand(strcmd, dc)Try

dc.Open()

cmd.ExecuteNonQuery()

Catch exAs OleDbException

Label3.Text = ex.ToString()

Finally

dc.Close()

EndTryEndFunction