Home Forums SQL Server 2008 SQL Server 2008 - General Giving permission for each table within a database RE: Giving permission for each table within a database

  • March 25, 2013 at 5:24 am

    #1600237

    winmansoft (3/24/2013)

    Hi Lowell

    Thank you for replying. Your method of giving permissions to tables works fine.But if in server roles if i set sysadmin(I did it in SSMS) for that user then those permission given for table not works.All roles created gets neglected. I want permission for most of the database as admin and for some table in about 3 database i want to restrict the access for that user. So i gave sysadmin. So why does it will not work as i expected?How to solve it?

    you can't.

    once you give someone sysadmin, no other permissions matter; they can do anything to any object in any database. Also roles are cumulative, so if i'm in a role that is everything + anther containing three items, i still have everything. Deny permissions don't affect a sysadmin.

    so if you give someone sysadmin, and then want to take something away, you have to go back to the idea of least permissions:

    take away sysadmin, and create teh role(s) for each database which carry the appropriate permissions instead.

    people like to grant sysadmin a lot because it solves permission problems instantly, but that's just being lazy.

    In general, I grant sysadmin for our developers on their Dev SandBox machines , but not on any servers with real business functions (like production) or admin servers.

    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!