Post reply

Database Security

  • November 13, 2007 at 4:07 am

    #92780

    i wants to implement the Database security so that the developers could have only the rights to read and write the data in the database.

    they could not make changes to scripts or tables.

    How can i do that ?

    Thanks

  • November 13, 2007 at 4:17 am

    #750558

    You really should look at Books Online, topics: Fixed Database Roles and Fixed Server Roles. This will give you a listing of all available permissions that are already set up in SQL Server.

    But to answer your question....How do your developers log in? With SQL Server auth or via Windows Auth?

    I would recommend, if you can, getting all the developers placed in a Windows User group and mapping that to SQL Server. Then removing any SQL Server accounts the individuals might use. After that, grant the Windows Group membership in two database roles. db_dataReader and db_datawriter. This will allow read & write permissions without granting them the ability to change the schema.

    Caveat!!! The two above roles will NOT grant them execute permissions on any stored procedures. If they need to be able to execute SPs, then those permissions need to be granted separately or you need to create a custom database role with execute permissions on all the necessary SPs. If you do the later, don't forget to add the Windows Group to the role membership for this custom role.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • November 14, 2007 at 1:48 pm

    #751255

    I know this is posted in the SQL Server 7 and 2000 section, but we see posts in the wrong areas all the time. Is the SQL Server you are working with SQL Server 2000 or 2005?

    K. Brian Kelley
    @kbriankelley

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

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