Good day,
Does any one know a fast way to script the sa account and all its permissions for all the various databases it is a member of? I would like to create a duplicate user account quickly and am trying to by pass the process of manually doing it.
Thanks
During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
View 6 Replies View RelatedI have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this?
yours sincerely
Craig Hoy
I have several DTS jobs that runs well as a job with my nt login account for the SQL agent service startup account, but if I use the System account
they fail with this error.
" Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider"
The data has change access to the System account under the NT security.
Thank you in advanced.
Jorge
Hi all, i hope you can help me.
Basically a dts package has been setup that pulls in data from another companies server, this data requires to be on-demand i.e individual users can pull in updates of the data when they require it.
I am using xp_cmdshell and dtsrun to pull in the data. This obviouly works fine for me as i am a member of sysadmin.
Books online quotes " SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"
So i went to the SQL Server Agent Properties 'Job System' tab and unchecked 'Non-sysadmin job step proxy account' and entered a proxy account.
The proxy account has been setup as a Windows user with local administrator privilages and even a member of the sysadmin server role - just in case.
Now when i log onto the db with my test account - a non-sysadmin - and attempt to run the stored proc to import the data i recieved the message 'EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo' '
hmm... so basically i have either misunderstood BoL or there is something not quite right in my setup.
I have search the net for a few days now and yet i can find no solution.
Can anyone help?
Hi there,BOL notes that in order for replication agents to run properly, theSQLServerAgent must run as a domain account which has privledges to loginto the other machines involved in replication (under "SecurityConsiderations" and elsewhere). This makes sense; however, I waswondering if there were any repercussions to using duplicate localaccounts to establish replication where a domain was not available.Anotherwords, create a local windows account "johndoe" on both machines(with the same password), grant that account access to SQL Server onboth machines, and then have SQL Server Agent run as "johndoe" on bothmachines. I do not feel this is an ideal solution but I havecircumstances under which I may not have a domain available; mypreliminary tests seem to work.Also, are there any similar considerations regarding the MSSQLSERVERservice, or can I always leave that as local system?Dave
View 1 Replies View RelatedI have a situation that I have discovered in our QA database that I need to resolve. When I looked at the Activity Monitor for our server, I discovered that a process is running under a domain user account for one of our .Net applications. The problem is that that domain user account has not been created as a SQL login account on the server. I am trying to figure out how someone can log in to the database server with a domain user account that has not been added to SQL Server as a login account.
Does anyone have any insight on this? I don't like the idea of someone being able to create domain account that can access the database without me granting them specific access.
- Larry
Hello everyone
I need some advice regarding security
My security officier wants me to disable the sa account on all my SQL Servers.
NT Security for the sysadmin role is already setup for all my servers
for the group "Domain DBAs"
Could someone give the pros and cons.
this Person wants the ability tho activate the sa account at will. (he comes from the AS400 Mind frame)
Second question are there any good books or courses that talk about securing SQL Server 7.0, 2000, etc.
Thanks
I have taken over a NT 4 and SQL Server 7 system that
has a NT account called SA. No one seems to know what it
is for. I thought that the SA account was only a SQL login.
has anyone seen this?
Thanks
Scenario: My client has 4 sql boxes with applications connecting to them via various methods (ado, odbc, etc.). Some of the applications have the SA login and pwd hard coded. Too many users have the SA pwd so they want to change the pwd without affecting the applications. Well, they haven't heard of Source Safe until I got here and the projects for the applications are nowhere to be found. They don't want to hear about rewriting the applications.
Suggestions: I'm wondering if I can create an account with the same priviledges of SA and modify the SA account. I'm not sure what's possible at this point because they have taken away most of my options.
If anyone has any suggestions, I would appreciate it.
Thanks for your help.
This is my first time to install the SQL 2005 on my VPC for testing purpose, I don't recall that I did configure the sa account during the instalation.
Is their is any way after the installation to configure the sa account with a password? Please advice
Thanks
How can I find out what account sql server is running under?
View 2 Replies View RelatedI installed SQL Server 2005 and Visual Studio 2005 and have discovered that the ASPNET machine account was not added as a user when going into "Computer Management". I obviously need this to run ASP.net apps.
I tried going to the Administrators group to add user ASPNET, but the system can't find this user.
How can I install the ASPNET account?
makimix
Hi,
I need to secure an sqlserver database such that it can only be accessed from an application and to prevent anyone with full admin rights on their local machine and an sqlserver licence from getting in to the database.
I am struggling with controlling access to the database from the sa account. If I attach to the database from a second instance of sqlserver which is different than that where the database was created then I am able to gain full access no problems, which is of course The Problem.
From what I can work out.
1. sa is dbo (and this cannot be changed)
2. dbo has the role of db_owner (and this cannot be changed)
3. the permissions for the db_owner role cannot be changed.
4. the password for sa is set at the level of sqlserver and not per database
.....so any sa can access any database.
I don't believe this so have to be missing something significant, any light on the subject would be gratefully received.
KeithT
What is the best way of accessing a sql server on a live server? shall I use Integrated windows or use a special user account? If I use a user account, what are the needed priviledges to give it?
thank you
I seem to remember that when using VS2003 to create a website which connected to a MSDE database, I needed to explicitly grant access to the database for ASPNET machine account using the following SQL commands from within a .sql script:
EXEC sp_grantlogin '<machine>ASPNET'EXEC sp_grantdbaccess '<machine>ASPNET'
With VS2005, it sppears that upon creating an .MDF database in the App_Data folder this is no longer necessary.
I'd be interested to know why this is so. Does VS2005 automatically do this when the database is created?
If anybody could shed some light on this I'd be interested.
Thanks,Wayne.
I have a DTS pkg containing VBScript scheduled as a job. The script creates an ADODB connection and opens it passing the servername, username, and password as parameters. We are using NT Authentication, and SQLAgent is using a domain account (SQLService acct).
The job runs fine if I specify a SQL username with SA authority in the ADO connection string. But if I try to use an NT acct it gets a login failure for the i.d. I also noticed that none of the NT login/users show up in the Job Owner drop down list of the job properties window.
My question is should I be able to run the job using just an NT account with SA authority or do I need both NT and SQL i.d.s to run jobs? What am I doing wrong?
As default, the BUILTINAdministrators account is
created during the installation of SQL Server 7.0.
The default access is to have the account in
the System Administrators server role which gives
them dbo access to each database.
Since I don't want my network administrators to
have sa privleges within SQL but still want them
to be able to access the databases, I've removed
them from the System Administrators server role.
The SQL Server Login Properties window still shows
the account having access into each of the databases
as dbo, however they are unable to view or access
any objects within the databases.
Shouldn't the account still have permissions, just not
as sa? Can someone please explain this to me? I've checked
BOL and several of the reference books I have, but don't
find any detailed information on this account.
Thank you
Toni
The DBO account on a user database has managed to lose its SUID. This causes the account to appear in the sysusers table but not in the users collection in Ent Man.
Does anyone know a way to recover that does not involve restore from backup?????
I want to remove the SQL Services account from the Local Adminstrators group of the server, so that it cannot be used to login, (using trusted sa).
I have removed from administrators in user manager, granted the account the rights to login as a service, edited the permissions on HKLMSoftwareMicrosoftMSSQLServer, HKLMSoftwareMicrosoftWindowsNTCurrentVersionPerlib , HKLMSystemCurrentControlSetServicesMSSQLServer for the account.
SQL now runs with the service account stopping and starting SQL services, but any Scheduled Tasks on the server do not run.
Any ideas what I can do to get the tasks to run, without adding the service account as an administrator on the server?
I registered a server (SERVER2) belonging to the same domain as my computer through Enterprise Manager using the sa account of SERVER2. Both my server (SERVER1) and the remote server, SERVER2 have SQL Services running under a common domain admin account named,sqlservice. When I try to stop the Sql Server Agent service on Server2 from my Enterprise manager, I get the following error :
An Error 5 - (Access is denied) occured while performing this service operation on the SQLServer Agent Service.
The error log has recorded the following error :
[000] Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: A required privilege is not held by the client)
Could someone please help me with this?
Thanks in advance,
Praveena
My knowledge is limited in SQL server just enough to get it running for SMS so I am sure that I am at fault for this!!
A couple weeks ago I decided to change my SA account password for security reasons but SMS stopped working so I changed it back to its original a few days ago. SMS is working fine but now when I try to use SQL Executive to add accounts or do anything for that matter I always get an error saying that I had a SQL Server login failure. No matter what I do in SQL Executive I get this error. I've tried to login, configure, or access my database through executive but always come up with the same error. I changed the password in the logins folder under my database could I have locked myself out somehow? Its very strange because other apps that I am using require the SA account and password and they work fine, so I am really confused.
Thanks for any help someone might have on this matter,
Jeb
Well, I inherited a SQL Server ....sob story.........installer won`t call back.....sob story.....buying books like mad......sob story. Geez, this was a *great* job when I interviewed.....
Regardless, I do not know the password for the sa account on a SQL 6.5 server running on NT4SP3. We are using standard securtiy. Do I have any options?
Hopefully, the technical discussion doesn`t reference rubber garden watering implements. :{)
Thanks,
steve
This message is in reference to a previously posted SLQ Server 7 question and answer regarding the BUILTINAdministrators account (dated 11/19/2001)
Original Question: I'm using Mix-Mode Authentication. Can I remove the Builtin/Administrator loggin ID
without cause any harm to my current Login ID user(Windows NT user)? Are will
removing Builtin/Administrator what harm will this cause to my server?
Answer to above: The first thing I do after installing SQL is remove the Builtin/Administrators account for
the SQL Server. Depending on how you set SQL up, this should not affect any NT
users, unless you granted them access to the server through the local Administrators
group. This is not the prefered way of granting access, however. This will give any
administrator on the machine access to all of the data, which you may not want,
depending on the confidentiality of the data.
Situation: (SQL Server using Mixed Mode Authentication).
If the Builtin/Administrators is disabled or deleted, and the server rebooted, SQL Server will be initiated but, the SQL Server Agent won't be.
Signing onto SQL Server as SA will not be able to restart SQL Agent nor will an NT user with administrator capability have any better luck
Question: What specifically in NT and SQL Server does one need to change to get around this situation.
The SQL Server documentation is not very helpful regarding the use of this login.
While attending a SQL 2000 Administrator course, two of my colleagues were told to always delete this account but were not given any reason or explained the consequence of this action.
Hi,
I am trying to get the sql agent account to log on with a domain account, but the error I am getting is:
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
I want to know what is the guest account and what its use for ?
View 1 Replies View RelatedHello world
View 1 Replies View RelatedI have a problem that i can't log on sql server as the account is lock out. I don't know why it is locked out as this is the first time i log on. Anybody has this experience? Thank you very much!
View 4 Replies View RelatedWe have Websense installed on one of our servers.
The database is MSSQL 2005, and it's configured using the local system account to run all of the services.
The sa account keeps locking repeatedly, even though I have manually unlocked it using SSMS. And it locks right away. I cannot log into the database using the sa account, but can see it through a Domain Admin account.
Has anyone come across a problem when changing database access on a sql auth. user only. Where you are prompted to confirm password and you are not changing the password but there database access.
View 9 Replies View RelatedMy 'sa' account is locked out and the BUILTINAdministrators account does not have the sysadmin role. I removed the sysadmin role from BUILTINAdministrators for security reasons and before I could create a new account with the sysadmin role I fat fingered the sa password and locked it out. How can I get myself out of this mess.
Any help would be welcomed.
Hello , I am using sql svr 2005 ,I cannot deploy my db because local account does not have read rights , so how can I change it or how can I create a sql server account which has every rights (read,write,execute) to the database ??
F16 LĂ?GHTĂ?NĂ?NNNG
How can I find account that the SQL Server service is using ?
Plz help.