June 16, 2004 at 11:47 am
How Windows users rights are mapped to
SQL Server rights?
Let's say NT user 'rob12' is a member of Power Users NT group.
He logins to SQL Server. What he sees and what he doesn't see?
June 16, 2004 at 12:02 pm
It is not based on the groups they are apart of outside of SQL. You can however say assign the Power Users NT Group to have access to SQL Server and they have not access.
In SQL Server it is all based on the server roles, database roles or user defined database roles that they are assigned to.
So suppose you want the Power Users to have the ability to be able to Create Databases. Then you add the Power Users group to SQL Logins and assign the server role of "Database Creators".
June 16, 2004 at 12:15 pm
<add the Power Users group to SQL Logins and assign the server role of "Database Creators">
is the most interesting part of your message.
How do I do that?
What does it mean, "add Power Users group to SQL Logins"?
Does it mean I have to create SQL account and
somehow map Pwer Users to this SQL account?
June 16, 2004 at 12:59 pm
Ok to add the power users for the local box themselves with specific abilities do like so.
1) Open Enterprise Manager and drill thru the tree view to the Secuirty->Logins for the the server you want to setup.
2) Right click the Login node and select "New Login"
3) next to Name on the General tab is an elipse button ("..."), press and use the "List Names From" selection drop down to pick the server or domain you want to get the Power Users from. As a general rule you should on the NT box assign domain groups to local groups then assign to SQL Server so you have better control.
4) Highlight the group to add (in this case "power users") and press the "Add" button. Note here, "power users" is one of the special builtin groups, in this case when you select it will add the machine name like so "machinename\power user" but this will not work. You have to change "machinename\power users" to "builtin\power users", this is the case for all local builtin groups.
5) Press the "OK" button
6) On the bottom of the General Tab for New User set the default database (this is the database they see when they launch any app without setting to a specific database most folks set away from master but it is not absolutely needed. If you do thou , go to the Database Access tab and check the DB you default them to and set any special DB roles there) and langauge.
7) Switch to the "Server Roles" tab and check only the "Database Creators" role.
8) Press the "OK" button and you are done.
Now when a person who is in the Power Users local group signs in, and as long as they have no other permissions via server role, database role, user defined database role, or object level then they will have all of and only the abilities of the "Database Creator" server role.
From BOL (see "Adding a Member to a Predefined Role" in BOL)
"Creates and alters databases."
June 16, 2004 at 1:21 pm
Ahaa!
Thank you very much.
Now I'm getting closer.
So is it a preferrrable method of
SQL Server users authentication?
Would you recommend to create
SQL Login for IUSR_MachineName account?
I'm just trying to think if it is appropriate
to use this method for Web Application?
Will it give me more security?
June 16, 2004 at 1:39 pm
-- Thank you very much.
-- Now I''m getting closer.
-- So is it a preferrrable method of
-- SQL Server users authentication?
Actually that would be for Windows Authentication. That is just how you setup a Windows Group to have access.
It is better to have NT groups then assign access and permissions to group sql.
This way if a new person gets added to NT to a group they are ready to use SQL (for the most part). Just try to build logical groups to making admin easier.
-- Would you recommend to create
-- SQL Login for IUSR_MachineName account?
-- I''m just trying to think if it is appropriate
-- to use this method for Web Application?
-- Will it give me more security?
Actually applications tend to be harder to deal with. Many times folks create either a SQL Account for an aplication or in the case of IUSR_Machine a they setup like I said with the Windows Account.
If all folks log into a domain the same as the web and SQL server turn off anonymous login and you should have a easy time of access with apps and web giving them the access they need.
However with Web a lot of times you want to create customized interfaces based on the user. In this case even with a windows account you may have to build a table for each user to store bits of info to control the interface flow itself.
If you web server isn't on a domain and you have to use anonymous logins then you will want to use a SQL Server authenticated account. That is setup similar to the Widnows account except you specify the password in sql.
Then you have a table with user credentials and have to decide how to handle their passwords wether been a control on the page to validate against a domain or other server or a table which defines their password an other security features.
Short and sweet thou, if you can use just Widnows Authentication it is simplier to administer than SQL administered accounts and more robust.
June 16, 2004 at 2:39 pm
When you use Antares' method (via Enterprise Manager) on the General tab there is the option for making the login use SQL Server Authentication or Windows Authentication. You will want to 'check' the Windows Authentication. Then they don't need a SQL Server specific password.
However, the user's domain must be 'trusted'. That means that both the user's domain and the SQL Server domain authenticate Windows login's via the same domain controller (or via domain controllers that replicate each other - have the same logins/passwords on both).
-SQLbill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply