computer management in sql server 2000
-
hi guys this might be a dumb question to ask but i tried to google it and no answer was found...
what is the computer management used for and supposed to look like in sql server? i mean, when i create a new sql server login (windows auth) is does not show under the users folder, is this correct? what about when i create a new sql server login (sql server authentication)? ..... right now all my users come out there under users , but i think they were added wrongly manually from my previous DBA... are the users that come out there supposed to be the domain\users who have sql server logins under security in EM?
-
Adding a user to a SQL Server database is a two step process. First, you must create the login. This is an instance level login. All valid logins for a SQL Server instance can be found in EM under Security\Logins. The second step is granting the login access to a database. Once you do this, SQL Server will add that login name into the Users folder at the database level. These rules hold true for both SQL Server and Windows Authentication logins.
-
thank you! i understand now.. also for each login i need to give them access to every table in sq server, correct? select, update,delete....., how come right now all my user's have access to the sql server database, if i have not done so yet?
-
Once your users have access to a database, they will need object level permissions. This can be done by granting the permissions to the user or by creating a Role with all of the permissions and adding the user to the role. I prefer using roles over granting object level permissions directly to the user.
-
ok this is my last question... is there a difference between a server administrator role or a login under the administrator folder under computer management?
-
I'm not clear on what you mean by administrator folder in computer management? Are you referring to EM? Anyway, there is a difference between roles and logins no matter where they are. Think of a role like a windows group. You can assign permissions to the role and then add multiple users to that role and the users will inherit the permissions. The Server Administrator role is the most powerful role in SQL Server. Any user that belongs to this role can do virtually anything.
-
sorry i should had been more specific. in sql server 2000 when you go to computer management, under local users and groups there are two folders one that says users who has all the users that have access to the databases in sql server (hopefully what you meant above) and then one folder called groups who has administrators, backup operators..... if a user belongs to the administrators group, how is that different if a user has the server administrator role?
Thank again..
-
OK, now I'm with you. The Computer Management tool is a Windows component used for managing Windows level access and is completely separate from SQL Server access/security. The local users folder contains local (non-domain) Windows accounts. The groups folder contains Windows groups. Creating new local Windows users or adding Domain/local users into Windows Groups does not mean that you are giving them permissions to SQL Server. SQL Server security is handled through Enterprise Manager. You can add a Windows group as a SQL Server login and then any new addition to that Windows group would have SQL Server access, but for the most part, you should consider Windows security and SQL Server security two different monsters.
-
thank you!!! is there a reason why a user who has a sql server login with windows authentication has to be a part of the administrators group? i am having that error with all my users that are trying to conect to my sql server database.but when i add them to this group then the sql error message 18456 goes away.
-
By default, the Windows Administrators group has DBO access to SQL Server via the SQL Server System Administrators server role. This means that by adding a Windows user to the Windows Administrators group, you are giving them DBO rights to the database. This is not a good practice as the SQL Server Server Administrators server role is very powerful. Not to mention that by adding them as a Windows Administrator, they can now logon to the Windows Server itself with full admin permissions to the server OS and filesystem.
I recommend creating a SQL Server role inside the database that your users need access to. Then object level permissions to that role. Once you have the role setup, add the users Windows account as a SQL Server login via Enterprise Manager under Security\Logins. Then grant access to the database and add the Login to the database level Role. Then remove the user from the Windows Administrators group. If this is a production system, I recommend that you simulate this on a test server first so that you gain a comfort level of both Windows and SQL Server security and how they interact.
-
thank you so much. that is exactly how it is set up right now. so it is the right way for computer management to look how it is now: local users and groups, under users i have the names of all the uses that have access to sql server databases. and under the administrators group under groups only the ones that are supposed to have administrator rights have to be there. so i have to find out then why the access is not working as long as is not computer management then i could start checking other things....
-
I’m not trying to sound redundant here, but I get the impression that there is still some confusion here. There is an important distinction between the Computer Management tool and Enterprise Manager. All Windows machines have the Computer Management tool which allows you to manage/create Windows local users and groups. These local users and groups can then be granted permissions within Windows. They can login to Windows from Terminal Services or the console and use the server just as you would a workstation. This tool should be used to manage Windows level permissions.
Enterprise Manager should be used to manage SQL Server level permissions. You can add a user to SQL Server without giving that user Windows access. Let’s review a couple of examples.
First, let’s say you need to add a user to the Northwind database. Open Enterprise Manager and go to the Security>>Logins folder. Create a new Login, telling SQL Server if this is a Windows login or SQL Server login. On the Database Access tab, grant the new user permission to the Northwind database. Once complete, navigate to the Northwind Users folder and you should see your new user. Notice that you never had to enter the Computer Management tool to make this happen, even if the new user was a Windows user. At this point, you have a user that has the ability to login to the Northwind database, but if they walk up to the server console and attempt to login, they cannot because they have not been given Windows access.
Now lets say you want to create a new Windows user for someone who needs to use the server, but does not need SQL Server access. You would open up Computer Management on the server and go to Local Users and Groups. Here you would either create a new local user account, or add the person’s Domain account into the Users group. This person now has access to Windows on the server, but not SQL Server. They can login to the server and use the Windows applications, but if they attempt to access SQL Server, they will fail.
Now, here’s an advanced concept. Windows local users and groups can be granted SQL Server permissions. Let’s say that you have added the Windows Domain accounts for User1 and User2 into the Users group in Computer Management. This will give them both User level access to the Server and to Windows, but not SQL Server. If you needed to add User1 and User2 to SQL Server but you did not want to have to add them one at a time (maybe you have 100 users to add), you could go to Enterprise Manager>>Security>>Logins and create a new login and instead of adding a Windows user for the login, you can add a Windows group.
My point is, the Computer Management tool is for managing Windows permissions. Enterprise Manager is used for SQL Server permissions. While the two can be use in conjunction with each other, the administrator should have a solid foundation if both Windows and SQL Server security before attempting to use them together. The practice of creating a Windows user and adding them to the Local Administrators group just to give them access to SQL Server would be considered a ‘Worst Practice’. I hope that I am not making this more confusing for you.
-
i really apreciate your full explanation and examples.. thank you sooo much.. now i am clear in the difference. I have been reading about this for a while and never fully understood it till now.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply