locked
Mapping AD Groups to Certain SQL 2014 DBs RRS feed

  • Question

  • Hi, I am new to SQL server administration and I wonder if I am posting in the right place?

    I am using Windows server 2012 Standard R2 and SQL Server 2014 Enterprise. I have created three AD Groups and added the groups to the SQL server:

    Group A. Group B, and Group C. I have mapped each group to their database which I created on the same SQL server. Now I have Group A mapped to Database A, Group B mapped to Database B, and Group C mapped to Database C.

    Now all the users in all the groups can see each other databases, I need to give full permission to Group A for ONLY database A and NOT to allow them access Database B and C, also I need to do the same thing for the other two groups, this means each group can only access their database and not allowed to access other databases.

    What is the best way to that please?

    Thank you in advance!


    Sunday, May 10, 2015 12:24 PM

Answers

  • In the link you shared, they have changed the ownership of the DB to the desired login, in my case I cannot do that as I cannot change the DB owner to a Group.

    use Database A

    Go

    sp_changedbowner [Domain\Group A]

    Go

    Msg 15353, Level 16, State 1, Line 3
    An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys.

    Any idea ?

    ok..thats what you wanted ... users should not see other databases...

    yes, you cannot set group as database owner...i do not think there is any other way...

    you can still do - deny view any database to users but they will not see it in the object explorer but can connect to it , fine

    else leave it like that and they can see the database but cannot access them



    Hope it Helps!!

    • Marked as answer by Munaf Salim Sunday, May 10, 2015 2:03 PM
    Sunday, May 10, 2015 1:35 PM
  • Ok thank you
    • Marked as answer by Munaf Salim Sunday, May 10, 2015 2:01 PM
    Sunday, May 10, 2015 2:01 PM

All replies

  •  yes, you can do that - but what do you mean by full permission... there are database level fixed roles that can help you with this...

    db_datareader role --gives permission to read the data only

    db_datawriter role --gives permission to write(insert/update/delete) the data only

    db_ddladmin role --gives permission to modify/create/drop objects

    db_owner - you own the database and you can do anything within that database .

    you will also need additional permissions like executing stored procedures etc..

    if you want to take care of all these in shots along with other things inside the database- the group needs to own database -- so, db_owner permissions is what you need.



    Hope it Helps!!

    Sunday, May 10, 2015 12:35 PM
  • Thank you.

    I have already gave each group db_owner permission on their respective database. my pain in that I need to prevent Group A from accessing databases B & C and the same thing for the other groups (each group can only view and access their database)

    how can I do that?

    Sunday, May 10, 2015 12:45 PM
  • Thank you.

    I have already gave each group db_owner permission on their respective database. my pain in that I need to prevent Group A from accessing databases B & C and the same thing for the other groups.

    how can I do that?


     ok. in that case group b or c should not be able to access other database- since they do have access.  i hope they are not sysadmin on the sql server.

    are there any users  who exist inn all three databases- they can access the three databases.

    or - you mean they can see thee databases being listed in the object explorer... if so, they can just see but cannnot actually access   but if you still want them not to see try these http://blogs.msdn.com/b/euanga/archive/2006/05/04/585513.aspx 

    http://mitchelsellers.com/blogs/2008/10/15/limit-sql-server-database-list.aspx


    Hope it Helps!!

    Sunday, May 10, 2015 1:02 PM
  • Ok, sounds interesting, I'll try that and post the results... 
    Sunday, May 10, 2015 1:12 PM
  • In the link you shared, they have changed the ownership of the DB to the desired login, in my case I cannot do that as I cannot change the DB owner to a Group.

    use Database A

    Go

    sp_changedbowner [Domain\Group A]

    Go

    Msg 15353, Level 16, State 1, Line 3
    An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys.

    Any idea ?

    Sunday, May 10, 2015 1:23 PM
  • In the link you shared, they have changed the ownership of the DB to the desired login, in my case I cannot do that as I cannot change the DB owner to a Group.

    use Database A

    Go

    sp_changedbowner [Domain\Group A]

    Go

    Msg 15353, Level 16, State 1, Line 3
    An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys.

    Any idea ?

    ok..thats what you wanted ... users should not see other databases...

    yes, you cannot set group as database owner...i do not think there is any other way...

    you can still do - deny view any database to users but they will not see it in the object explorer but can connect to it , fine

    else leave it like that and they can see the database but cannot access them



    Hope it Helps!!

    • Marked as answer by Munaf Salim Sunday, May 10, 2015 2:03 PM
    Sunday, May 10, 2015 1:35 PM
  • Ok thank you
    • Marked as answer by Munaf Salim Sunday, May 10, 2015 2:01 PM
    Sunday, May 10, 2015 2:01 PM