Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Do not grant permissions to create or modify database objects

Unanswered Do not grant permissions to create or modify database objects

  • Monday, April 16, 2012 6:54 PM
     
     
    Sign In to Vote
    0
    Hello All,

    I have created 2 active directory groups.One group 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 created a new login for this group and 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.I also checked all the appropriate deny check boxes(Alter, Create table,Insert,update..etc) in database properties under permissions section. Please correct me if I am doing anything wrong.

    Thanks for any help.
     

All Replies

  • Monday, April 16, 2012 7:26 PM
     
      Has Code
    Sign In to Vote
    0

    Hello,

    try this to get your effective rights of your login.

    EXECUTE AS LOGIN = 'INSERT LOGIN NAME HERE' 
 
     
    CREATE TABLE ##ObjectLevel 
    ( 
        DatabaseName    NVARCHAR(128), 
        Name            NVARCHAR(128), 
        SubEntityName    NVARCHAR(128), 
        PermissionName    NVARCHAR(128) 
    ) 
 
    EXECUTE sp_msforeachdb 'USE [?]  
        INSERT INTO ##ObjectLevel 
        SELECT db_name(), t.name,  c.subentity_name, c.permission_name  
        FROM sys.objects t 
        CROSS APPLY fn_my_permissions(QUOTENAME(t.name), ''OBJECT'') c' 
         
    SELECT    NULL AS 'Database Owning Object',  
            @@SERVERNAME AS 'Securable Name',  
            a.subentity_name  COLLATE Latin1_General_100_CI_AI AS 'Subentity Name',  
            a.permission_name COLLATE Latin1_General_100_CI_AI AS 'Permission Name' 
    FROM fn_my_permissions(NULL, 'SERVER') a 
    UNION ALL 
    SELECT    NULL, 
            d.name COLLATE Latin1_General_100_CI_AI,  
            b.subentity_name COLLATE Latin1_General_100_CI_AI,  
            b.permission_name COLLATE Latin1_General_100_CI_AI 
    FROM sys.databases d 
        CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') b 
    UNION ALL 
    SELECT    o.DatabaseName COLLATE Latin1_General_100_CI_AI, 
            o.Name COLLATE Latin1_General_100_CI_AI,  
            o.SubentityName COLLATE Latin1_General_100_CI_AI,  
            o.PermissionName COLLATE Latin1_General_100_CI_AI 
    FROM ##ObjectLevel o 
     
    DROP TABLE ##ObjectLevel 
 
    REVERT
    best regards
     
  • Monday, April 16, 2012 7:35 PM
     
     
    Sign In to Vote
    0

    Thanks so much christian. But I am getting an error as

    Invalid collation 'Latin1_General_100_CI_AI'.

    Do I need to specify database name near use and server name?

     
  • Monday, April 16, 2012 7:44 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    the script above runs over all databases in the instance.

    Try this for your database.

          SELECT db_name(), t.name,  c.subentity_name, c.permission_name  
        FROM sys.objects t 
        CROSS APPLY fn_my_permissions(QUOTENAME(t.name), 'OBJECT') c

     
  • Monday, April 16, 2012 8:01 PM
     
     
    Sign In to Vote
    0

    I tried it..It is showing all the permissions associated to each table in the database. I am just trying to do is I dont want to give the permissions to the second group to create or modify any tables or database objects in the database.Let me tell you the steps I followed...

    I have an active directory group created and it has members.

    I then Created a login for this group in SSMS by giving db_datareader and db_denydatawriter as database roles for the associated database.

    And then I executed the below script to actually create a role and user with read only permissions and mapped this role to the login as mentioned in the script

    CREATE ROLE [ReallyReadOnly]

    EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'
    EXEC sp_addrolemember N'DB_DenyDataWriter', N'ReallyReadOnly'

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


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

    I dont know why this is not working. The member in the group can still create a table and insert records just like an admin. Please help. Any suggestions please

    Thanks

     
  • Monday, April 16, 2012 8:04 PM
     
     
    Sign In to Vote
    0

    If you go to the database where you set this up and run

    EXECUTE AS LOGIN = 'MyDomain\UserInThatBizGroup'
    go
    select * from sys.fn_my_permissions(NULL, NULL)
    select * from sys.fn_my_permissions(NULL, 'Database')
    go
    REVERT

    How does the result look like? A few rows? Many rows?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Monday, April 16, 2012 8:09 PM
     
     
    Sign In to Vote
    0

    Few rows..For server 25 rows and for database 61 rows..But in the message I am getting an error as

    Cannot execute as the server principal because the principal "MyDomain\UserInThatBizGroup" does not exist, this type of principal cannot be impersonated, or you do not have permission.

     
  • Monday, April 16, 2012 8:09 PM
     
     
    Sign In to Vote
    0
     

    Cannot execute as the server principal because the principal "MyDomain\UserInThatBizGroup" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    I am just bolding the error message

     
  • Monday, April 16, 2012 8:39 PM
     
     
    Sign In to Vote
    0

    Sigh. No, I don't know the name of your users. MyDomain with your actual domain and UserInThatBizGroup with the actual user you tested with.

    With EXECUTE AS statement you impersonate the user you want to test.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Monday, April 16, 2012 8:53 PM
     
     
    Sign In to Vote
    0

    Yes. I tried with my actual domain and the group. I dont know why I am getting the error. I have also tried using my account in place of group then also I am getting the same error as principal doesn't exist and cannot be impersonated.

    Thank You.

     
  • Monday, April 16, 2012 8:56 PM
     
     
    Sign In to Vote
    0

    I just copied the error message with the test domain and group.But I have tested the execute as script with my actual domain and group.

     
  • Monday, April 16, 2012 9:45 PM
     
     
    Sign In to Vote
    0

    You cannot impersonate a group.

    You should test with your own login, because I assume that you are member of Administrators in Windows, and you are member of sysadmin in SQL Server.

    You must test with a user that is intended to be a read-only user.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Monday, April 16, 2012 10:01 PM
     
     
    Sign In to Vote
    0

    Yes I am a member of sysadmin in sql server. I have tested with a user (read only).I am getting the error as could not obtain the information about windows NT group/user 'Readonlyuser'.

    I am sorry for troubling...But since morning I am kind of struggling with this process...

     
  • Monday, April 16, 2012 10:03 PM
     
     
    Sign In to Vote
    0

    So if you try to log into Windows and then SSMS with that Readonly user what happens?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Monday, April 16, 2012 10:15 PM
     
     
    Sign In to Vote
    0
    It opens up and I am able to create table and insert records which I should not,  with that read only user
     
  • Tuesday, April 17, 2012 7:38 AM
     
     
    Sign In to Vote
    0

    It opens up and I am able to create table and insert records which I should not,  with that read only user

    And if you run the SELECT statements when logged in as that user?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Tuesday, April 17, 2012 2:12 PM
     
     
    Sign In to Vote
    0

    It runs successfully...

     
  • Tuesday, April 17, 2012 9:40 PM
     
     
    Sign In to Vote
    0

    > It runs successfully...

    Anything else would be surprising. It never occurred to you that I might be interested in seeing the results?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se