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 RDL to manager via .NET

Hi
is it possible to add a RDL-Report via a Window-Application to the report manager?
Thanks in advance!
Regards
AlexConsier using an RSS file with RS.exe, if you're okay with doing this via command-line
--
Message posted via http://www.sqlmonster.com

Adding procedure to SQL Server Express

I am adding (trying to add) a procedure (written by a friend of mine and someone who is experienced). I am adding it via the SQL Server Mgt. Studio Express. When I run it, it says that the command completed successfully, but it doesn't appear in the Procedure folder.

When I run my ASP 2.0 form that calls it, it says that it can't find the procedure.

Can anyone help me determine what to look for or what I might be doing wrong?

Thank you.

How exactly are you adding the object to the database inside the Management tools. Has you friend supplied a SQL script file and you are running it inside a new query window. If so you will need to make sure that the query you are running is inside the correct database. Generally you should have a use statement at the top of the database

Query for example

use northwind
go

and then have the Stored procedure script following it.

|||

He forgot the USE statement. Previously it was adding into the system area. My knowlede of procedures is limited, but I should have thought of that myself.

Thank you.

Adding numerous login via script

Hi,

Can anyone point me in the direction of a script that incorporates sp_addlogin, which allows for adding 200 sql authentication logins from an excel spreadsheet, or a temporary sql table with the id info, containing the username, password, and def db?

I'm trying to avoid adding each new login one by one.

EXEC sp_addlogin 'username', 'password', 'default database'Thanks, BPH

A simple approach is to create an expression in your Excel spreadsheet that builds the Exec sp_addlogin line. Copy the formula down to all the rows, then copy/paste the value into Query Analyzer.

If you have loaded the columns into a database table, you can execute a Select statement that builds the lines. Again, copy and paste the results and execute them. Assume you have built a table call logintemp with username, pwd, and dbase columns:

Select 'Exec sp_addlogin ''' + username + ''', ''' + pwd + ''', ''' + dbase + ''''
From logintemp

You need to double the quote marks to yield a quote in the output string.

|||Thanks. I'll give that a shot.

Adding number of decimal places during table design

Hello,

Nice easy one (hopefully) from a newbie on SQL 2000.

I have a table HolidayTakenBooked which is populated from a stored procedure via the following statement;

TRUNCATE TABLE HolidayTakenBooked
INSERT INTO HolidayTakenBooked
SELECT * FROM #TMP_HolidayTakenBooked ORDER BY ABR_Clock_No

I am finding that for certain values in the HolidayTakenBooked table decimals are not being transferred correctly. ie. 0.5 in the TMP table appears as 1 in the HolidayTakenBooked table.

I'm pretty sure that this is down to the data definition of the table see sample field below;
[HOL_DaysTaken1] [decimal](18, 0) NULL ,

So the simple question here is how do I define decimal places when I define a new table. When designing a new table in Enterprise Manager I select decimal and the server does not allow me to change the value of 9 it defaults to.

What simple thing I am not doing ?

Cheers
NealPut values in Scale properties,you will find it just below precision if you are creating tables in EM design Table option...|||Many thanks.
Neal

Adding logins via SSEUtil compared to SQL Server Management Studio

Hello all,

I am currently in the process of setting up an SQL Server Express installation that comes packaged with an application I have written. My problem is that I want to use SQL Server user management (not just windows users) which work fine if I set them up manually. I started writing a script that I have SSEUtil execute once the application is fully installed (a step in my installation script) which sets up the users and passwords etc. The script is similar to the following:

USE [DBName]
GO

EXEC sp_DropUser 'user1'
EXEC sp_DropUser 'user2'
EXEC sp_DropUser 'user3'
EXEC sp_DropUser 'user4'
GO

USE [master]
GO

EXEC sp_DropLogin 'user1'
EXEC sp_DropLogin 'user2'
EXEC sp_DropLogin 'user3'
EXEC sp_DropLogin 'user4'
GO

CREATE LOGIN user1 WITH Password = 'user1', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user2 WITH Password = 'user2', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user3 WITH Password = 'user3', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user4 WITH Password = 'user4', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [DBName]
GO

EXEC sp_AddUser 'user1'
EXEC sp_AddUser 'user2'
EXEC sp_AddUser 'user3'
EXEC sp_AddUser 'user4'
GO

ALTER USER user1 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user2 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user3 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user4 WITH DEFAULT_SCHEMA = MySchema
GO

REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 FROM MyRole
GO

EXEC sp_DropRole 'MyRole'
EXEC sp_AddRole 'MyRole'
GO

GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 TO MyRole
GO

EXEC sp_AddRoleMember 'MyRole','user1'
EXEC sp_AddRoleMember 'MyRole','user2'
EXEC sp_AddRoleMember 'MyRole','user3'
EXEC sp_AddRoleMember 'MyRole','user4'
GO

Now if I run this script from within SQL Server Management Studio it executes perfectly. The logins add, the role is added, each user is added to the database logins and assigned to the role, the schema is set correctly on each user.

Then when I try to run the exact same script from the SSEUtil application (SSEUTIL -s PCNAME\Instance -run USERS.SQL), it processes everything, except the Logins.

This is frustrating as it means to install for a client I would need to either get them to open the management console and run the script from there, or I have to go to site just to setup users.

Am I on the right track? Or is there another way to automate the adding of Logins?

Thanks in advance,

DSXC

Just an update.

I did a check within my database and found that the sys.syslogins has the users (when I do a select from the view) but they just don't work. The only difference I can see between my scripted login I created and the SA user is the flag for sysadmin, but thats understandable as these users are not to be sysadmins.

Is there another table that actually enables the login?

DSXC

|||

I noticed a lot of people using the SQLCMD.EXE instead of the SSEUtil.EXE I was using so I thought I'd give it a try.

Lo and behold... it works!

Talk about crazy... oh well. Just so everyone knows, use the SQLCMD.EXE over SSEUtil.exe... gah!

EDIT: I didn't mention the command line to run it...

SQLCMD.EXE -i MYSCRIPT.SQL

Hope that helps.

DSXC

|||

Hi DSXC,

Sorry to have missed this thread earlier, I can shed some light on what you're seeing.

SSEUtil.exe is an unsupported tool primarily used for troubleshooting User Instance problems, it is not meant to be used as a general solution for runing scripts, nor is it licensed to be deployed with your application. Additionally, the default mechanism of SSEUtil works against the User Instance, not the parent instance, so my guess is it was not working the way you think it was.

SQLCmd is the general scripting utility that is installed with all copies of SQL 2005 and is exactly the tool you should be using for what you wish to accomplish, but you've already discovered that.

Mike

|||

Hi Mike,

Thanks for your response. It makes a bit more sense now.

I found the SSEUtil app with a search on attaching the database to my SQL Server so I guessed it would have worked running scripts against it also. I just noticed that the SSEUtil doesn't attach my database correctly either so I've now transferred over to using another SQL script.

Again thanks for your response.

DSXC

Adding Formula in CRYSTAL REPORT

Hi,

I am using a Crystal reports in order to get the information from the database (ORACLE 9).
The source of data is the script which I added via add command using ORACLE OLE Provider.

I would like to say that now I am trying to implement CR formulas in order to give the
following message :
"NO USERS LOGIN"

I wanted that when there is no user connected to the database Crystal Report gives this message.

In order to that I created the following forumla from the formula Tab, but it didn't give any
message on crystal reoport, when no user login to the database:

Formula 1

if count ({command.USER}) < 0
then "No User Login to Database"

or

Formula 1

if count ({command.USER}) < 1
then "No User Login to Database"

I tried to drag this formula on various section of reports (Detail, Report header), but it
did'nt work.

But when I used greater than sign instead of using less than size, this formula works and
gives the message.

For Example:

Formula 2

if count ({command.USER}) > 0
then "User Login to Database"

Could someone please inform what changes I made in the formula 1 in order to display the message
"No User Login to Database", when there is no user login to database.

I also like to say that the routine which I am using in the Crystal Report gives me information
of several Users who are connecting to the database.

Thanks

DavidI think that's normal, because crystal create a connection to your database to verify the number of user connections. So I think that you must put the following formula in your report:

if count ({command.USER}) < 2
then "No User Login to Database"

To verify what I say just print the field {command.USER} to see his value.|||Hi ariqa,
Use the following formula

if isnull ({command.USER})
then "No User Login to Database"

Madhivanan|||Hi Machuet & Madhi,

Thanks for your cooperation.
Yours solution helped me.

Thanks

Regards

David

Adding current date into attachment filename in reporting service

Hi there,

I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.

My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.

My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.

Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005

Regards,

Hello...

I have the same question. My subscription generates a file that gets stored on our Network file server. A new file is created every day and the users want the generate date included in the name of the file that is created and saved. I see that this can be done in the e-mail subject line if your subscriptions email the users. How do you do it in the file name?

Thanks.

|||

You can't do change the name on an export, but you can take a look at a couple posts on here about using a data-driven subscription to handle this.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=168131&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1311897&SiteId=1

Hope this helps.

Jarret

Adding current date into attachment filename in reporting service

Hi there,

I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.

My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.

My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.

Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005

Regards,

Hello...

I have the same question. My subscription generates a file that gets stored on our Network file server. A new file is created every day and the users want the generate date included in the name of the file that is created and saved. I see that this can be done in the e-mail subject line if your subscriptions email the users. How do you do it in the file name?

Thanks.

|||

You can't do change the name on an export, but you can take a look at a couple posts on here about using a data-driven subscription to handle this.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=168131&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1311897&SiteId=1

Hope this helps.

Jarret

Adding assembly file to AS database via AMO

I am trying to add an assembly reference to an AS database using AMO objects. The class Microsoft.AnalysisServices.Assembly class does not expose any file property or source property to assign the assembly file path.

With SQL Management Studio, I can right click on the Assembly folder and add a new assembly. I want to do the same thing in code. Can someone provide me with an example? Thanks!!

The AmoAdventureWorks sample that is shipped with SQL Server has the following C# routine which does what you want.

static void CreateStoredProcedures(Database db)
{
// Create the CLR assembly
ClrAssembly clrAssembly = db.Assemblies.Add("StoredProcedures");
clrAssembly.ImpersonationInfo = new ImpersonationInfo(
ImpersonationMode.ImpersonateServiceAccount);
clrAssembly.PermissionSet = PermissionSet.Unrestricted;

// Load the assembly files
clrAssembly.LoadFiles(Environment.CurrentDirectory
+ @."\StoredProcedures.dll", false);

clrAssembly.Update();
}

|||Thanks. MSDN AMO objects tutorial only mentioned Assembly class and not ClrAssembly. Thanks again.