Post reply

Do not grant any permissions to create or modify database objects

  • April 16, 2012 at 2:59 pm

    #267453

    Hello All,

    I have created 2 active directory groups.One groups has full permissions with sysadmin role and the other group should have read only permission and they should not have any permissions to create or modify the database objects.So for the second group I ran this script.

    CREATE ROLE [ReallyReadOnly]

    EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'

    --explicitly DENY access to writing

    EXEC sp_addrolemember N'DB_DenyDataWriter', N'ReallyReadOnly'

    --now since we know the AD domain group as a LOGIN exists, added a USER, tied to that login, to our database

    --make a user in the db for the matching login

    CREATE USER [MyDomain\ThatBizGroup] FOR LOGIN [MyDomain\ThatBizGroup]

    --finally, add user to the role created

    EXEC sp_addrolemember N'ReallyReadOnly', N'MyDomain\ThatBizGroup'

    I followed this. But they still can create a table and insert the records.Please correct me if I am doing anything wrong.

    Thanks for any help.

  • April 16, 2012 at 3:46 pm

    #1474445

    your role looks fine, so it's got to be that either the individual or the group is in some other roles, like sysadmin roles, that is giving more permissions.

    run variations of these tow commands withthe groups and an individual in he group that you know exceeds the desired permission

    so you can track it down.

    --or @acctname = 'MyDomain\ActualLogin

    EXEC master..xp_logininfo @acctname = 'MyDomain\ThatBizGroup',@option = 'all' -- Show all paths a user gets his auth from

    go

    EXEC master..xp_logininfo @acctname = 'MyDomain\ThatBizGroup ',@option = 'members' -- show group members

    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 16, 2012 at 3:55 pm

    #1474449

    Thank for your reply. I executed with the group and I got the permission path as null and when I am trying to execute with the individual user account in the group it says could not obtain the information window NT group/user .

  • April 17, 2012 at 8:40 am

    #1474769

    Can anyone help me with this...I searched in google but I couldn't find any solution..Please help..

    Thanks

  • April 19, 2012 at 7:56 am

    #1476021

    srik.kotte (4/17/2012)

    Can anyone help me with this...I searched in google but I couldn't find any solution..Please help..

    Thanks

    You can use the xp_logininfo procedure to returns information about Windows users and Windows group membership. For example, check if members of BUILTIN\Administrators are granted SYSADMIN role. Once an account has been granted or inherited SYSADMIN role via some means, I don't think that rights can be denied to them, you have to make whatever changes are needed to get them out of that role.

    This link is to a script I wrote to determine what SQL Server logins, and Windows users/groups have been granted or have inherited SYSADMIN privillage.

    http://www.sqlservercentral.com/articles/Security/76919/

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

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