April 21, 2005 at 12:12 pm
Hello,
I don't have much experience with SQL server, so excuse me if my questions seem simplistic to most of you.
When I first started working here, I could fire up the enterprise manager console and monitor the database jobs to ensure they all ran okay etc.... Suddenly now I can't see the jobs anymore.
I have another user who uses tables in SQL Server, suddenly he can't connect anymore. When he fires up enterprise manager he can see the server, yet it says either access is denied or the server isn't running.
I'm baffled by these things and have no idea where to start.
April 21, 2005 at 1:11 pm
Someone, maybe even you, changed permissions for your login. Do you have anyone else who administers, or who has access to that server? If the default setup hasn't changed, anyone with administrative access to that server can gain administrative access to SQL Server.
It could be any number of things that actually happened, but it sounds like you were removed from the sysadmins group.
No offense intended, but if you are new to SQL Server you shouldn't have that level of access, certainly not in a production environment...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 21, 2005 at 11:26 pm
Agreed with dcpeterson. Someone, who has system administrator or security administrator permission revoke sa permission from you. I think that from all scenarios that could be, this explanation is the most realistic. Of course till then when there wouldn't be any new details about your problem
April 22, 2005 at 12:39 pm
I suppose both of you are right, I shouldn't have those kind of rights. The unfortunate thing is although my primary responsibilities are that of an Oracle DBA, I adopted SQL Server as well and am the only one in the organization at this location that is considered responsible for all these databases.
I'm looking for as many helpful hints as I can to get up to speed with the SQL Server side of things. In many ways it's easier to maintain than Oracle, but the security part of it is confusing to me.
April 22, 2005 at 5:20 pm
Well, at least you have (hopefully) have an understanding that your actions can have huge consequenses, and will exercise appropriate caution.
Unfortunately, this is a familiar situation for SQL Server. Since it is easier to maintain than Oracle and DB2 many organizations have made the mistake of assuming that they don't need a qualified DBA...
In terms of general hints to point you in the right direction, here are some tips off the top of my head:
SQL Server has two authentication modes. Integrated, which is a single signon sort of a model where your Windows Domain account is granted rights. Then the SQL Server mode where the logins and passwords are managed within SQL Server itself.
SQL Server has both system rights and database rights. Most users don't need any system rights except the right to logon which, unlike Oracle, is granted implicitly by the creation of a login (there is no explicit Create Session permission in SQL.)
A login may be granted access to one or more databases. This creates a data base level user. The users are generally tied to a login, but they don't have to be. Creating a database user but not associating it with a login is similar to creating a user in Oracle but not granting it Create Session. The user exists, but nobody would be able to actually make use of it...
There is a special login called sa which is a SQL account and cannot be deleted. If you know the sa password you can always login and gain system admin rights although this practice is generally not encouraged.
When you register a server in Enterprise Manager you must specify an authentication method (Windows, or SQL Server) If you specify Windows, then you don't need to supply a password. You can check to see how the server is registered by right clicking on the server and selecting Edit Server Registration Properties.
To view jobs, you must be the job owner or be in the system administrators group, or be logged in as sa.
My guess on the most likely scenario is that you were using Windows authentication to gain access and your Windows account was also in the Local Administrators group (Windows) which placed you in the BuiltinAdmins group in SQL Server, which is in the System Admins group by default. Someone either removed your Windows account from the Local Administrators group (in Windows) or removed the Builtin Admins group from the System Admins group (in SQL Server).
Hopefully this gives a some clues...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 22, 2005 at 6:18 pm
Could it be that you stop sql server without knowing it?
Is the Icon color on your server connection in the Enterprise Manager Green or Red? If it is red, someone may have stop the sql server. You could right click on that server connection and start the server back up. Also make sure you start SQL Server Agent as well.
mom
April 22, 2005 at 7:45 pm
I think SQL Server here is setup giving the windows domain rights. I bought the resource guide so will be doing some reading. There's a section in there that translates functionality between SQL Server and Oracle that I think is going to be very helpful. I have been working with the TSQL for quite some time now, it's the administration part in SQL server that I don't have too much experience with.
Thanks for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply