February 2, 2002 at 10:43 am
Hello,
We have SQL Server 2000 setup on a NT4 environment. There have only been 1 or 2 different accounts accessing this DB until now. The SQL database has been install as mixed mode. What we are finding is when we add a new user that is connecting through Win authentication, the user has to be granted access to each and every object in the database, otherwise no go... Is there an easy way around this by globally setting permission? All objects are owned by dbo.
Thanks for any advice,
Dan
February 2, 2002 at 10:49 am
You probably want to take a look at database roles. If it's simply the ability to read and write to tables, you have two built in roles for you: db_datareader and db_datawriter. If you need a custom set of permissions, create a new database role. Assign the permissions to the role. Then, when you add a user, put him in the role. BTW, this works for NT groups as well. So if said users already exist in a group in the domain, you can give that group the role. Then, when you need to add someone new, you simply place them in the NT group and they are automatically granted login rights to the SQL Server and have the rights given to the database role.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 2, 2002 at 11:02 am
Thanks for the quick reply.
I will give your suggestion a try, and if I have trouble I will post here again.
Thanks again,
Dan
February 4, 2002 at 7:40 am
It sounds maybe like you have granted permissions to the public role. By default, everyone that has access to the database is defaulted to the public role. Hence all users can see everything. Sounds like you need to create some other roles and deny some things from the public role.
Tom Goltl
February 4, 2002 at 7:50 am
If he's having to set grant permissions every time he's added a user, unfortunately that means no roles at all have been used. You make a good point, though, in that it's generally better to use roles other than public.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 4, 2002 at 9:53 am
Another vote for not using the Public role. also, I add the security scripting to my object scripts so they are always there.
Steve Jones
February 5, 2002 at 1:41 pm
Also I would look at creating NT groups that you can place these folks in where the groups have already been assigned proper access inside SQL. Avoid adding peoples accounts directly to SQL. As an example you will find SQL already created a Domain/Admins account which allows all NT Admins access to SQL by default. If you create groups in NT then when you add a person to NT you add them to the NT group and they automatically have SQL access the same as anyone else in the group.
February 5, 2002 at 2:06 pm
Yes, this is the BUILTIN\Administrators group and it maps to the local Administrator group on the server. There are times when it's a good idea to remove this group... like when the data is critical and sensitive.
But in general, what we try to do where I work is use NT groups, grant the NT group access to the SQL Server, then assign DB roles, where the DB roles have the permissions set. This is much preferable to looking at a list of hundreds of logins for SQL Server.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 5, 2002 at 2:35 pm
Hello All,
Thanks for all of the good suggestions. Those last few sound like the way to go.
Will give it a try and let you know if there are problems.
Thanks again,
Dan
February 12, 2002 at 7:59 am
Hello again,
According to Antares686 he mentions that you can add NT Groups to SQL. Can you explain how this is done? Is it different than adding a user to SQL?
Thanks again,
Dan
February 12, 2002 at 9:37 am
No, you just choose a group instead of a person when adding an NT Auth login.
Steve Jones
February 12, 2002 at 10:44 am
Actually when you do new login on the General tab you will see Name, if it is a local group on the NT box itself then jsut type the group name. If it is on a domain then under authentication you will see a drop box next to the word Domain, click the drop and choose your domain, it will put in the name field DOMAIN\ then just add your group name to the end.
February 12, 2002 at 10:52 am
Perfect... Will give it try.
Thanks,
Dan
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply