How to make a copy of SA

[bigfoot]

I want to create a user that has everything that sa has. To run dts packages with. I created a w2k user and thtn in sql, but I can not sign him into a task.

When I create a user in SQL only, it works ok, but when I create a user in the windows system, and then sql, (pick him from the win list), I can not use this user to log into an connection when I am doing DTS.

Any help would be great.

 

[NoCoolHandle]

All you need to do is create a Login Account and add them to the sysadmin roll.

To do this in EM

Left click Security
Right Click Logins
select "New login"
Define the login (sql or Nt)
On the server roles tab select "system administrator"

HTH

Rob

 

[bigfoot]

Yup, did that. If the user is created in sql server, then all is ok. If I create the user as part of the domain, then use him in sql, he is not able to log into the sql databases, even if I check off administrators in EM.

 

[bigfoot]

Any user I first crste in the NT domain does not work in SQL Server. If I just create then AS a sql server login, then they work fine.

What's the deal?

 

[ClaireHCM]

When you access thru Query analyzer,what kinda error you are getting for those Domain account?

 

[SQLSister]

I don't know if this will help, but we have occasionally had a problem with the fact that creating a user in Windows NT doesn't propogate the users immediately to all the servers. Sometimes if you wait a day or so they are then available. THe process you use to create the users may also not be giving them any active directory rights on the server in question. No rights to the server, no way to access the database on the server.

 

[bigfoot]

OK. This server is a stand alone server at our hosting site. I r-click on mycomputer, go to manage and create the user there. Give him administrator priv.'s.

Then I go into EA on the same machine and choose new user, and I click on the little button with the ... on it and choose my user from the domain's list. I give him admin to the database, and check off the checkboxes next to ALL of the databases.

I click on Query analyzer, and type in his name and password, and I get a login failed for user myuser.

I can log into the box, so the name and password ARE correct. It's just SQL Server that will not accept him.

If I create the SAME user but only in SQL Server, it works great.

Anyone, please?

 

[NoCoolHandle]

Ok.. It seems like you are talking about 2 different types of logins..
If you create a login on the computer and it is a Local Administrator (of the computer/domain) then you don't need to log in to the SQL instance as anything other than you NT person and you should by default (unless the hosters have removed the BUILTIN\Administrators group from your server) get full sa privaliges in SQL - there should be NO reason to provide access to any db.. (heck you shouldn't even need to create a SQL login.)

When you talk about EA, I am guessing you mean EM (enterprise manager). IS taht correct?

Are you creating a SQL login or a Domain Login?

Domain/Omputer Logins don't need a password.. (This makes them a sql login) - unless you are running mixed mode, you won't be able to login as the user is "not associated with at trusted connection" or something like that. You should select "windows authentication" after you have logged into the pc (Opp System) using the name you just added to the system.

Where you need to add your user to the sysadmin role is in SQL Server not the domain (computer). Follow the first post from me as to how to do this.

or Using Query Analyzer.. Login as an admin (if you have that ablitiy) and
use the foloowing syntax to add a WIndows user with admin privilages..

exec sp_grantlogin [PCorDomainName\LoginName]
-- adds the pc login to sql as an NT login (square brackets are needed)
exec sp_addsrvrolemember 'PCorDomainName\LoginName','sysadmin'

now they are god..

 

[ClaireHCM]

Ok...I see what's wrong.
Your steps should be
(1)first Create user in your computer (right click on mycomputer and manage to add user)
(2)Go to Enterprise manager --- security ---R-click on Logins to add new logins
(4)Select the name from scrolled list ,and choose the user you just added into your computer ----PS.Including domain name or computer name ,domain\user or computer\username
(5)Now....log off your computer ,login as the user you just added
(6)connect to isqlw/QA , select windows authentication.

Any prob. let me know!

 

[bigfoot]

ClaireHsu: Did that.

NoCoolHandle: I did not put in a password in SQL Enterprize manager (EM) (sorry about EA)


What I need the user for is for the connections in my DTS scripts. If I use windows authen, in them, and I am logged off, then they will not run. The machine will be logged off most of the time.

What name and pwd do I use for the connections on the DTS scripts, and also what name and password do I use for the SQL Servedr Agent.

That's what this is all about.

 

[ClaireHCM]

Your sql server agent on both machine should be either running under same domain account (cant be local account)or domain accounts who can communicate with each other.

There can be only one user logged in one machine(I am saying computer,not sql server)

 

[bigfoot]

The web server is a stand alone server on it's OWN domain.

I get to it thru Remote Desktop Connection, so it is not signed in all the time, and it is not on site.

It does not connect with any other domain, just runs the web. It also runs DTS packages to unpack the nightly load to the customer files, and to package the orders files.

FTP does the rest. But I want to have a domain (it's) account run the dts scripts so it can be sure to have permissions to all the file folders to IO the files from/to the FTP server.

 

[NoCoolHandle]

Ok so what you need is a sql login not an NT Login..

This could be done using the following script

exec sp_addlogin 'test','pwd','master'
exec sp_addsrvrolemember 'test,'sysadmin'

the server needs to be running in Mixed mode for the solution to work..

Reghack = hklm/software/microsoft/mssqlserver/mssqlserve/loginmode=0
then restart the sql service..
or use EM..

That should then get you what you need

One thing I am slightly confused with is how are you running your dts sctipts.. Are they running as a schedued job, or are you executing them from a command prompt..




Rob

 

[bigfoot]

They will be running from a scheduled job.

And while I have you.... The job that refreshes the files depends on a MS Access Zipped file being in the folder to unzip and transfer to SQL Tables.

What's the best way to run this job? Do I schedule the job for 2am and hope that my file makes it there by then, or do I run the job from here after I FTP the job up. If so then how? ASP script connected to a web page? Web service? Connect my SQL Server to the web server and run the job.

Been thinking about this, and it's a tough question.

Hey, the file just FTP's down from the web server using the ASQL gent's default login. I was not logged in to the server!!! Sweet. Thanks.

 

[NoCoolHandle]

Asp would be a problme as you would more than likley be running as the iuser_pcname account or asp_net account

then you would need to get permissions..

However I think you could use the dtsrun to take a sql login..

Maybe the answer here is to have a job setup that can be executed via an alert..

when you set up the job, you can specify a job owner.. If it is sa/dbo, it should run the dts package wiht no problems as it will be running with all os permissions as well.

The trick would be to then setup an alert to trap for a custom error.. something like sqlserror 72011 (custom errors need to be over 50000).

You could then create a custom error that you could fire from a trigger or a proc.

sp_addmessage(72011,17,'run sql dts package',default,'true')

Would create a message that will be sent to the event log. THis would the ensure that the event notification would get back to SQL Server and the alerter service..

At that point the jobs alert would fire the trigger..

You could even add a script that looks to see if your access file exists before you start using xp_cmdshell and dir.. It could fill a table and you could then query the table. before you start or if it isn't there, the same job would try again 1 hr later..