January 15, 2008 at 12:58 pm
When you unclicked SysAdmin a couple of days, did you take it off the MAS SQL Server account or did you accidently take it off the SA account?
See if you can re-add SA to SysAdmin.
January 15, 2008 at 2:52 pm
I removed the system administrator check from the account "admin". I did not touch sa until just now by checking the box for system admin. I am still having this SQL-DMO error however.
By the way, I had to recheck the system administrator box for the "admin" today because MAS500 could not apply additional licenses to itself. Once I put the check back in, they were able to complete the action.
Now our aSa consultants want to log on directly to our SQL server to run scripts to fix a data type issue. Backups are already done! 🙂
January 15, 2008 at 3:08 pm
Just thought I would add this as I was just looking at the MAS500 databases through Ent. Manager and may have found something to help solve this error:
Microsoft SQL-DMO error.
"Error 21776: [SQL-DMO] The name "dbo" was not found in the Users collection. If the name is a qualified name, use [] to separate various parts of the name, and try agan."
At the MAS500 database level, there is a level under it called "Users" in here, there is a user that was created by the consulants called "dbo". When I drill down at this level, it shows it is using "sa" for the login name!
No other place do I see the user "dbo" not even in the Security section of the Ent. Manager.
Maybe this is somehow linked to this "sa" error when just opening the account via "Security".
January 16, 2008 at 3:57 am
At the MAS500 database level, there is a level under it called "Users" in here, there is a user that was created by the consulants called "dbo". When I drill down at this level, it shows it is using "sa" for the login name!
I'm not quite sure what you mean by "when I drill down". Could you explain it a little better?
I advise creating a new user account in the MAS database, assign it to the db_Owner role and give them that password to use for access. When you said they had an Admin account, I thought you meant that literally-a SQL Login called "Admin". Now if they really do have a user called "Admin" and you didn't add it to the db_Owner role for the MAS database when you removed SysAdmin access, that might account for your troubles yesterday.
January 17, 2008 at 10:18 am
I'm actually in the middle of a project where we are having to remove SYSADMIN rights from anything other than the DBA's. This is in order to meet audit requirements.
If your accountant wants a good analegy ask him how he would feel about giving Admin access to the network and all of his private files to everyone in the company.
If you have payroll information on your server then you have an even better point. Let him know that you have just given access to payroll to not only the consultant but most of the users. If you don't have payroll data on your servers then ost companys have some type of high confidentiality financial data on their SQL Servers, use that instead.
I will say this though. Try to get your problem fixed now. The longer it goes on the harder it will be to fix later.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 18, 2008 at 12:53 pm
Brandie Tarvin (1/16/2008)
I'm not quite sure what you mean by "when I drill down". Could you explain it a little better?
I advise creating a new user account in the MAS database, assign it to the db_Owner role and give them that password to use for access. When you said they had an Admin account, I thought you meant that literally-a SQL Login called "Admin". Now if they really do have a user called "Admin" and you didn't add it to the db_Owner role for the MAS database when you removed SysAdmin access, that might account for your troubles yesterday.
Sorry Brandie,
Drill down = clicking on "Properties".
Yes, they do have a SQL Server user account called "admin". Your first assumption was correct. It had db_owner on this account already so I didn't need to add it. I had to re-check the Server Role of System Administrators on it anyway though. 🙁
Do you think I need to look at the SQL Server user account called "dbo" since the error code specifically references this user? Let me know if so and what I need to look for.
Kenneth, unfortunately, our company really does not understand privacy or security issues and they all treat me like a roadblock here. Also we do not have an official SQL DBA so I'm trying to do what I can using the expert advice I am getting from this forum.
I really appreciate this forum and am happy that I am not the only one dealing with these kinds of issues.
January 21, 2008 at 4:08 am
Open up BOL and look at "dbo". This will explain the dbo user account to you.
Maybe the problem is, when you removed SysAdmin access from the "admin" account, you forgot to change the owner of the database with sp_changdbowner to the "admin" account?
That might account for your problem with getting the "admin" account to do anything after you removed SysAdmin access. It might have been trying to create or alter dbo owned objects and didn't have permissions because it wasn't officially assigned to dbo anymore (which is different from the db_owner role, BTW).
January 21, 2008 at 11:07 am
Michael,
Your quote from Machiavelli is absolutely on target. Machiavelli gets a pretty bad rap and most people use the term "Machiavellian" to mean sneaky and underhanded or cold and heartless; however, I have found that most of those people haven't bothered to read or to learn much of anything about Machiavelli.
If one follows the advice of Machiavelli, one will be aware of the up-side risk as well as the down-side risk of the decisions one makes . . . which is a good thing, IMHO. So, in this particular situation, a review of the upside risk and the down-side risk shold lead to the conclusion that, even when one is the DBA), when someone does something really stupid using the sa access (and eventually someone will), then it is all going to land on the DBA whether he has the title or has simply accepted the mantle . . . unless he can demonstrate via memos, emails, or other eveidence that he has done his best to prevent the disaster. Creating a special log in and deleting it (or dimishing its access privileges) after the contractors/consultants/ vendor representatives leave is an excellent piece of evidence to use as a shield when the "stuff" rolls down hill.
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply