Post reply

Restricting Users from creating Tables and Database

  • January 22, 2013 at 11:31 am

    #273218

    What do I have to do to configure SQL Server to not allow any users besides my Login to be able to create tables and database.

    Thanks

  • January 22, 2013 at 12:32 pm

    #1579798

    rynmthw3 (1/22/2013)

    What do I have to do to configure SQL Server to not allow any users besides my Login to be able to create tables and database.

    Thanks

    It's the principal of minimal permissions.

    in the database itself, when you map a user to their login, do not grant those users to the roles of db_owner or db_ddladmin.

    create a new. specific role that has the permissions you really want them to have...maybe db_datareader and db_datawriter, maybe not even the writer permissions. maybe add execute so they can run any procs you created.

    add your users only to that new role you defined the expected permission s on.

    on the server itself, make sure their logins are not sysadmin.

    if they are sysadmins, and you cannot take away their sysadmin permissions, then there is nothing you can do.

    * caveat: ...well, you could add a ddl trigger, but they would be able to disable that and do what they wanted...

    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!

  • January 22, 2013 at 3:23 pm

    #1579846

    The user that I am taking this permission away from do have the sysadmin server role so I am going to revoke that....Problem solved

    Thank you

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

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