How do I setup permissions in Windows Authentication?

Question

How do I setup permissions in Windows Authentication?

crazyfig

SSC-Addicted

Points: 400
More actions
August 6, 2009 at 4:40 am

#209055

Hi everyone,
I have set up our SQL Server to Windows Authentication as too mnay ppl know the sa password. So we will move them over to use Windows Authentication. So now that I have our 2005 SQL Server set to Windows Authentication, Im a bit stuck. All the books I got with the maintaining a SQL Server 2008 MCTS I got dont go into detail on how to setup authentication for each user. So bit stuck here.
I tested, and could log in to the SQL Server with Windows Authentication in Management Studio. Problem is I seem to have full access to all the databases. How do I restrict access / permissions to the databases? I see you cant add roles to a SQL Server...only on each database.
Does this mean I will have to create roles on each database separately, the add the users to each role on each database? Seems like an inefficient way to do things?
Basically what I want is:
1. To create different roles for different rights (like ready only for data, no updates allowed etc)
2. Assign user accounts to the roles
Am I on the right track? Will I have to create the same roles for each database, then assign each user to the roles I created for each database?
Cheers

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

normal practice here would be to create SW domain groups in AD and add these groups to SQL, then add required permissions within SQL to those groups.

then you add invidual windows accounts to the relevant SW groups that have the SQL permissions required.

If you can see all dbs with your windows ID then perhaps ypu are in the local admins accouint on the server, so ensure others who do not require local admin are not in that group.

---------------------------------------------------------------------

crazyfig SSC-Addicted Points: 400 More actions · Answer 2

Ok, so basically most of the "security" is done via Active Directory...adding users to the specific groups. Then in SQL Server, I just add users to the groups? So I will have to create / maintain groups & add users in 2 places...one in Active Directory and one in SQL Server? Is that right?

george sibbald SSC Guru Points: 104210 More actions · Answer 3

no, you only have to add individual members to the groups in AD. You then define the AD group to sql as a login and People then get their access to SQL via their membership of the group. This means that in SQL you only need to apply permissions to the groups rather than to individual users, saving admin effort.

---------------------------------------------------------------------