Home Dashboard Directory Help
Search

Can we create SQL Security Groups at SQL Server Instance level? by raghan


Status: 

Closed
 as Won't Fix Help for as Won't Fix


1
0
Sign in
to vote
Type: Suggestion
ID: 733699
Opened: 3/27/2012 12:39:18 PM
Access Restriction: Public
0
Workaround(s)
view

Description

If a SQL Server instance contains 100 SQL Server Logins (not the windows\domain logins).

I want to grant read access to 50 SQL logins and read\write to other 50 logins.

If we have capability to create SQL Groups at SQL instance level, I can add 50 logins to that SQL Group and grant permissions at group level.

In SQL Server 2012 version we can create SQL Server roles. This might help little bit in these scenarios but still want to know if we can get the facility to create the SQL Groups at Instance level.

Please let me know if we have any options available like this in SQL Server 2008 R2 and upcoming versions.
Details
Sign in to post a comment.
Posted by Microsoft on 3/28/2012 at 3:09 PM
Hi Raghan,

In SQL Server 2012 we have added server roles to group one or more server principals together.

As Ben suggested, prior to SQL Server 2012 this can be achieved by creating login from windows groups.

I am closing this DCR for now. If you feel it does not address your use-case, please re-open it.

Thanks,
Don Pinto
SQL Server Engine
Posted by Ben Seaman on 3/28/2012 at 4:36 AM
Just to clarify - I mean that you need to create groups in Active Directory representing your groups of users and then use CREATE LOGIN in SQL to create logins based on those groups.
Posted by Ben Seaman on 3/28/2012 at 4:35 AM
If I understand you correctly, you can already do what you are proposing i.e.

CREATE LOGIN [DOMAIN\MyGroup] FROM WINDOWS

this works just fine and has done since at least SQL 2000 - and probably before this.

Ben
Sign in to post a workaround.