| Author |
Topic |
|
rohans
Posting Yak Master
194 Posts |
 Posted - 2003-12-15 : 14:18:27
|
| I want to know if there is a strored procedure that tells the account that is starting the sql services? All help appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-15 : 14:20:42
|
| No there isn't. But I'm sure that you could get to it if you used xp_cmdshell with DOS commands although I don't know what DOS command that you would need.Anyway, why not just have a peak at the service to get this information?Tara |
 |
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2003-12-15 : 14:23:40
|
| Ok, I have about 5 different SQL servers on different physical servers. I want to stay at may workstation and know the account that starts each server. Any ideas.All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-15 : 14:28:23
|
| Right click on your server in EM and go to properties, then go to Security tab.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2003-12-15 : 14:32:31
|
| When I try security tab I don't see the account starting the service. Any reason for this.All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-15 : 14:35:29
|
| Because it is using the local system account. This is not recommended. You should be using a domain account that has local admin privileges. I suggest making this change on all of your boxes.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2003-12-15 : 14:42:03
|
| I am about to implement this change for the databases but had to do some testing first as I can't bring down all the production databaes, that may leave me without a job. But I am in the process. I looked at one that uses a domain account and still saw nothing. What does that mean as I am clueless now.All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-15 : 14:49:17
|
| The information that you are requesting is stored in the registry. From your machine, you can connect to remote registries if you use regedit. Just go to file, connect network registry. SQL Server comes with an extended stored procedure called xp_regread that allows you to read the registry of the SQL Server. This is not documented in SQL Server Books Online. For examples, just google xp_regread.HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER or if you are using a named instance HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$<instance name>ObjectName is where the account is stored.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2003-12-15 : 15:34:04
|
| Suppose it says the key does not exist. But from regedit I see the key. What do I do?All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-15 : 15:37:57
|
| Run this instead of what you have:EXEC xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER', @value_name='ObjectName'goObjectName is where the logon account is stored.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2003-12-15 : 15:54:00
|
| I tried that already and got this error. but I used yours just to prove to myself, and is the same error.Msg 22001, Level 1, State 22001RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'(0 row(s) affected)All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-15 : 16:07:32
|
| It must be because you are using the Local System Account. I don't have ANY servers that do this, so I am unable to test that theory out for you.So go to the physical box and open up the service and see which account it is using. Or maybe you've got terminal services configured so that you can get to the server remotely.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2003-12-15 : 16:19:02
|
| I tried at the local box and the smae error. I am using a domain account at this time, but it is also system admin. I don't know why.All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-15 : 16:22:21
|
| Don't try to run the command on the local box because it's the same thing as running it from your box but connected to the server in QA. Go to Start, Settings, Control Panel, Administrative tools, then double click on services. Double click on the MSSQLSERVER service. Now go to Log On tab. Is the local system account selected?Tara |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-12-15 : 16:26:58
|
| Is the server registered in EM with an SA account? If it is not, then sometimes it doesn't show the Service account (Depending on permissions).-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2003-12-15 : 20:30:01
|
| It is registered in EM with a SA account and I tried it from my workstation. Tduggan , I am not sure what you are asking me to do as I know what account is starting my services. Maybe I should use the startup service account to register the DB in EM , then I may see it. What you think?All help appreciated. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-12-16 : 00:55:39
|
| well, you could always just login to the console and check the services cpanel. but that would be too easy.try this instead.go to www.sysinternals.com and download their excellent pstools package. Extract the pstools.zip to a directory and then run the psservice command against the server you want to query. here are is the help output from psservice:Usage: psservice [\\Computer [-u Username [-p Password]]] <cmd> <optns>Cmd is one of the following: query Queries the status of a service config Queries the configuration start Starts a service stop Stops a service restart Stops and then restarts a service pause Pauses a service cont Continues a paused service depend Enumerates the services that depend on the one specified find Searches for an instance of a service on the networkUse the username and password to log into the remote computer in cases where your account does not have permissions to perform the action you specify.you would probably want to run a command like the following:psservice \\computer -u username -p password config MSSQLSERVERYou output will look like this:SERVICE_NAME: MSSQLSERVER(null) TYPE : 10 WIN32_OWN_PROCESS START_TYPE : 2 AUTO_START ERROR_CONTROL : 1 NORMAL BINARY_PATH_NAME : C:\PROGRA~1\MICROS~2\MSSQL\binn\sqlservr.exe LOAD_ORDER_GROUP : TAG : 0 DISPLAY_NAME : MSSQLSERVER DEPENDENCIES : SERVICE_START_NAME: CORP\sqlserviceIn this case we are running SQL Server from an account called sqlservice found in the CORP domain. You could wrap all this up in some kind of sproc that executed these commands via xp_cmdshell, or better just script the code from a batch file or something at the OS level. -ec |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-16 : 12:14:17
|
| The point is to check what it is using regardless of what you think it is using. Since you aren't getting the correct results elsewhere, it wouldn't hurt to double check.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2003-12-17 : 10:10:44
|
| Haven't got the chance to test the post from eyechart.What is the recommended account for the sqlagent service? shgould it be local system or use a domain account? Really trying to have the best security possible.All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-17 : 12:05:59
|
| Neither service should use the local system account. Both should use the same account, one that is a domain account and has local admin privileges on the database server.Tara |
|
|
|
Next Page
|
SQL service account