Friday morning we discovered that we no longer have sysadmin
privileges. We were able to query the syslogins table. In the output
we can clearly see that our accounts do have the sysadmin privileges,
since there is a 1 in that column. But yet we do not have sysadmin
privs!?!?!?!??!?!? Puzzling.
We are not able to get into the SA account, since no one knows the
password. But we are in BUILTIN/Administrators, and we have many SQL
Server authenticated accounts with sysadmin privs. But yet none of
them seem to have the privilege.
Saturday I was able to restart the instance (actually, several times
now), but that does not seem to resolve the problem. I have also
rebooted the server, which does not solve the problem.
The next option would be to restore Master from a few days ago, but
since I have no privileges I cant even do that!!!
Hi I'm new to SQL Server. I have created a databased named Sample and I hae created the user with login named "Sman". SMan owns some tables and sp's. I'm able to access the tables and SP's when I was logged in as Sman in Query analyser. I have given a Sysadmin privilege to Sman then I'm not able to access the tables and sp's when i try to login with Sman.
ie, Select * From tabl1 is not working But Select * From Sman.tabl1 is working. I dont know Why is it so? Can any one help me!
is there a difference in the previleges of 'sa' login and other loginwith 'sysadmin' role (and 'db_owner' for all databases) ?can they do the exact same things ?
Hi How do i set my domain administrator account as a sysadmin account for SQL? I have an error when installing SCCM but it just because my domain administrator account (which I use to install the SCCM) does not have sysadmin SQL Server role permissions on the SQL Server instance targeted for site database installation.
We€™re running mixed mode authentication on our SQL Servers. To make the server €œsafer€? builtinadmininstrators no longer have sysadmin role on the sql server. If there is only one login with sysadmin role, and we lose track of the password, is there any way to recover it? How could we reset the password or create a new sa account with a new password? This situation has not occurred, but I€™m worried about how to recover from it should it occur. This question relates to SQL 2000 and SQL 2005.
Is is possible to hide "salary" or other sensitive data from a person who is a Sys-Admin. My belief is that there is no way. Please correct me.....
Assumptions: SQL Service account has Local Admin privelege. Sysadmin can do anything on local machine, including run scripts adding themselves to any default/instance of SQL on the machine.
Please direct me to any other source of information for this topic.
Is it possible to show the user name (such as 'phuser') who is a member of the sysadmin group (NOT my idea!) I notice if you go to current connections is SQL EM the name shows, but if I login as that user if I try, user, user_name, etc inside of QA it shows DBO
Hello everybody . I have a group - SQL support.I want to give them rights to run any job from EM but I don't want them be a part of sa group What rights should I give them ? All existing jobs owned by members of sysadmin group.
Our SQL2000 server now allows all member of the Windows 2000 local administrators group log in with SysAdmin role.
I only want couple of people with sysadmin role. What should I do to prevent that. I was told once that I should delete the BUILTINAdministrator ID and manually add each window login ID to SQL server. Am I on the right track?
I have a SQL2005 in a cluster environment, for some reason the only way that user accounts can login to either the database or SSMS is to grant them the SysAdmin role. This access is a little to high for my liking and am wondering if anyone else has come across this before.
We set up a SQL Server 2005 box. When we set it up we did it with windows authentication, so the sa account is disabled. After everything was set up we were going to do some locking down. We added a domain account and gave it sysadmin rights. Next we went in to the sysadmin role and deleted the builtin/administrators group. This deleted all users except for the sa account. The sa account is disabled and we don't have a password for it. Not sure what to do next. Is there anyway to salvage this without have to completely reinstall? I can't enable sa because it says I don't have the rights, I can't add anyone to the sysadmin role because I don't have the rights. Why did it delete my domain account in the first place?
Hi guys,When I am trying to connect with SQL server 2005 as SQL server authentication mode in my won machine as a sysadmin user then a error is ochered."Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)". Please provide me a solution.
Is there a way to deny Security Permissions to a login that has sysadmin? Unfortunately I have to leave the user as sysadmin. I trying denying alter any login and control server but that didn't work.
Hello,I would like to know is it possible to disable drop database for saor sysadmin. If saor sysadmin needs to drop the database , he/she mayhave to change status in one of the system tables (sysdatabases ?) andthen only database can be dropped . This is to avoid dropping thedatabase by mistake by sa.In books online under drop databaseSystem databases (msdb, master, model, tempdb) cannot be droppedI would like to know how this is implemented for system databases ?ThanksM A Srinivas
Platform: Win2000 SP3, SQL 7.00 - 7.00.1063I have a SQL-authenticated user with the following permissions:"Process Administrators" server role"db_owner" and "TargetServersRole" for msdb database"db_owner" for master database.The problem is that when this user opens up any job (i.e. owned by anyuser) in the SQL Server Agent, and goes to the Notifications tab, thefirst three alerts (Email, Page and Net send) are greyed out, i.e.these cannot be set.The other options are available (Write to Windows application eventlog, Automatically delete job).The only way I can achieve this is to give this user the "SystemAdministrators" server role, i.e. sa.But this of course gives absolutely full access, which I don't want.Is there a way for a user to see/change Notifications of jobs whichonly he owns?
Alright, this should be a simple question, but I don't know the answer.
I created a group in our Active Directory, and added myself and another member of my team to the group. I then went into SQL 2005 management studio and added the group as a new login, and gave the group the sysadmin role.
The idea is for us to be able to connect with Windows Security to do administrative tasks on the server versus logging in with SA.
The problem, is that we cannot connect. It does not allow us to login. Do I have to do mappings for each database too?
I'm trying to run the Bulk Insert statement but in order for me the run it, i need to have the sysadmin permission. Can someone show me how to grant sysadmin permission to my SQL Server user? This is really urgent. Thank you in advance.
The company I work for outsources all its non-development IT. So all windows servers are administered by an outside company. Lately we have purchased SQL Server 2005, along with a dedicated Windows Server 2003 server. I am the sole administrator of this SQL Server, and so have sysadmin rights. However because the outside company is responsible for all windows servers, they are very reluctant to grant me local administrator rights on the server. This has been causing problems, partly because I have to go through them for many simple requests (such as moving database files, or changing SQL Server configuration files), and partly because certain functionality doesn't seem to work for non-administrators (such as the use of Database Mail and full access to Reporting Services).
I want to challenge the decision and gain local admin rights to the server. Would anyone have further reasons why a sysadmin should also have local admin rights? Is this common practice, or are sysadmins often denied admin access to the server?
We have a third party application and wish to create a report based upon a view.
The strange thing is logged in to the server as a SQL SYSAdmin account, we cannot view the data via the view. SQL Admin accounts are setup correctly and there is nothing different on this particular server. No errors are returned just a blank view with no records.
Could this be a permissions problem or orphaned schemas in that particular database? I thought SYSAdmin could view and do just about anything and the people who use this particular database would not have the know how on denying permission to the SYSAdmin role.
The problem I'm having now is that any users that are not in the sysadmin server role can't read any table. Say if I have 2 users:
Domain1User1 ( in db_datareader and db_datawriter Database Role, no sever role assigned) Domain1User2 ( in db_datareader and db_datawriter Database Role, sysadmin Server Role)
Here is what's expected to happen if everything goes well.
A list of customers ( first name, last name, age, etc) in a gridview should show up after login.
After both users logged in, Domain1User1 received an exception message of "Object reference not set to an instance of an object".
Domain1User2, however, was able to see the list.
I checked SQL Server Logs, and found 2 items: Login succeeded for user 'Domain1User1' ... Connection TRUSTED Login succeeded for user 'Domain1User2' ... Connection TRUSTED. Based on the log file, it appears that both users had good connections to the database. Then why is it that User1, which is not in sysadmin Server Role, was not allowed to make a query?
Now if I assign sysadmin Server Role to Domain1User1 as well, the User1 will be able to see the list without seeing the exception.
We have defined a local administrator to be the SQL Server and SQL Server Agent services user, and is also the job step owner for some SSIS packages I am running.
My question is, isn't by default a local administrator ALSO granted sysadmin in SQL Server? According to this link, it seems to imply this:
However, I am having some permissions problems with the local adminstrator account (i.e. SQL Server agent account) when it runs the job. The error is that it doesn't have execute permissions on sp_dts_addlogentry.
Is there a simple command that can be executed to check if the current user has sysadmin privs? Â I just want to check to see if I have sysadmin privs and if so then execute a command, if not do nothing in .Net code. Â I just want to do this check once and set a variable in the .net code.
i am aware that only sysadmin can send attachments using sp_send_dbmail. but the problem is, i don wan my application login to have sysadmin role and wan it to be able to send email with attachments using sp_send_dbmail. i'm using a stored procedure to call sp_send_dbmail, anyway can i impersonate sysadmin inside the stored procedure to execute sp_send_dbmail?
