Post reply

Active Directory Accounts need to see only one database role.

  • April 24, 2015 at 5:29 am

    #317641

    I have more than 3000 Active Directory Users, I have created Role Level Security on one table by using Suser_name(), Now all the active directory users need to connect sql server and access the database role object. How can I achieve that without Using .net?

    I am able to add all active directory users to sql server in one go, but I am also trying to achieve same time to map with database role as well?

    Is it possible ?

  • April 24, 2015 at 6:35 am

    #1793239

    ok, so you have ~3000 users in active directory;

    did you add a login for a windows group like myDomain\RegisteredUsers? or did you do user by agonizing user ~3000 times?

    i try to use groups as much as possible; for example, even though i'm the dba, my domain username mydomain\lowell doesn't exist on any server;

    only the group myDomain\SQLDBAs exists. makes it super easy when they hire an assistant for me, or even my replacement.

    if you offer up some details on what you have in place, we can offer some advice;

    you probably just need to add a windows group as a user and to a role with the appropriate permissions.

    then it's something like five lines of code to add 3000 users, instead of 3000 repetitively redundant executions times for 5 lines of code.

    USE SandBox;

    CREATE USER [myDomain\RegisteredUsers] FOR [myDomain\RegisteredUsers] --contains 3000 users, changes daily.

    CREATE ROLE [ReallyReadOnly]

    EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'

    --can the users EXECUTE procedures? comment out if false

    GRANT EXECUTE TO [ReallyReadOnly]

    --finally add our user to the role:

    EXEC sp_addrolemember N'ReallyReadOnly', N'myDomain\RegisteredUsers'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 24, 2015 at 7:14 am

    #1793248

    Nearly I am trying to achieve like this:

    http://www.mssqltips.com/sqlservertip/1657/get-active-directory-users-and-groups-with-sql-server-integration-services/

    But without using Script/.Net can we do some other way.

    Many Thanks in advance

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

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