locked
user security permssion RRS feed

  • Question

  • What is the best  way to manage users security( 100 + users)that need to be assigned a data specific permission in sql server 2005/2008.
    I want to give them :   Read/ Write/Delete data and  create /execute stored procedures right

    I don’t want to configure each individual’s permission one by one.. what is the best way to do this in real world environment?

    What is the best to way to mange users security in SQL server 2005? Any best practice I can follow?

    Monday, January 31, 2011 3:59 PM

Answers

  • Yes. Create a database role. Grant the Windows User logins or Windows Group logins access to the database as database users. Then put the database users (the Windows User logins or Windows Group logins) into the database role.

    How you assign permissions on the objects to the role, depends on how the database is structured. You can assign read, insert, update, delete on tables and views, and execute permissions on procedures to each individual table, view, and proc. (You mentioned CREATE PROC but that seems unlikely.) Or if all the tables, views, procs are in the same schema, you can grant insert, update, delete, and execute permission to the schema. If they need to see everything, you can grant insert, update, delete, and execute permission to the whole database, but that sounds like too much. You can grant permission to one or more schemas and then deny the same permission to a few tables if that helps. A deny overrides all grants. You would have to be careful not to include users in the role that need the permissions to all the tables.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Monday, January 31, 2011 6:05 PM
  • If you have a Windows Login (Domain\Mylogin) it can be granted access to the SQL Server as a login.  But many of us prefer to use Windows Groups (Domain\MyDBStandardUsers) to grant access to the SQL Server as a login.  (At the domain level, this group would have all Windows logins that need to same rights as members of the group.) 

    Once a login is added to the SQL Server, it can be made a user of a database.  Database rights are granted to users not to logins.  (But your user name can be the same as the login name unless you want it to be different.)

    Database roles are simply containers for rights.  Create a role for the StandardUsers and assign the Domain\MyDBStandardUsers as a role member.

    Now grant rights to the role and all users (whether as Windows Logins or as members of a Windows Group) who are role members will get those rights.

    Granting rights:

    public - Everybody with access to the database is a member of public.  Which means that you want to limit what rights get granted to public.

    Granting very sweeping rights can be done at the schema or the database level.   Here is how you grant everyone in a role rights to execute all stored procedures in the database.

    GRANT EXECUTE ON DATABASE::MyDB TO StandardUsers

    You can do this for SELECT, UPDATE, DELETE, and INSERT as well, but there are hazards with giving away so many direct rights.  However, if all of your selecting, inserting, deleting is done through Stored Procedures, the users do not need any SELECT,UPDATE,DELETE, or INSERT rights.   This is because the Stored Procedures (which they can execute) will do all the work for them.

    Of course, you can mix and match these rights according to your needs.  Granting at the database level is very broad, but you can grant to specific objects at a much finer level.

    You should find these posts interesting:  http://blogs.msdn.com/b/lcris/archive/tags/basic+sql+server+security+concepts/default.aspx

    RLF

    Monday, January 31, 2011 6:31 PM

All replies

  • A best practice is not to give permissions to users directly but use databaes roles and group memberships.

    If the users are Windows users I would reccomend creating a group per required roles. Then create a role within the database and set the proper permissions for this role. Eventually you grant the Windows Group access to the role within the database. This way you have seperated permissions and users properly.

    If you are using SQL logins it's a bit more complex because you cannot group them. This means you have to grant each induvidial SQL login the created database role.

    That's why it might be the best thing to use Windows Accounts.


    Robert Hartskeerl - http://sqlblog.hartskeerl.nl - http://twitter.com/rhartskeerl
    Monday, January 31, 2011 4:04 PM
  • thansk..I am using a window login..

    I added a user(test) login to sql server 2005.how shoud i configure for  UserRoles , UserMapping and Securible options?
    DO i have to put public?

    after that, I am tying to create a databased role. and I am not sure what kind of permssion should i give  users so taht they can Read/ Write/Delete data and  create /execute procedures ? do i have to configure each stored procesdure's permssion?is there anyway i can do this for the whole SPs at the same time?

    Monday, January 31, 2011 4:58 PM
  • Yes. Create a database role. Grant the Windows User logins or Windows Group logins access to the database as database users. Then put the database users (the Windows User logins or Windows Group logins) into the database role.

    How you assign permissions on the objects to the role, depends on how the database is structured. You can assign read, insert, update, delete on tables and views, and execute permissions on procedures to each individual table, view, and proc. (You mentioned CREATE PROC but that seems unlikely.) Or if all the tables, views, procs are in the same schema, you can grant insert, update, delete, and execute permission to the schema. If they need to see everything, you can grant insert, update, delete, and execute permission to the whole database, but that sounds like too much. You can grant permission to one or more schemas and then deny the same permission to a few tables if that helps. A deny overrides all grants. You would have to be careful not to include users in the role that need the permissions to all the tables.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Monday, January 31, 2011 6:05 PM
  • If you have a Windows Login (Domain\Mylogin) it can be granted access to the SQL Server as a login.  But many of us prefer to use Windows Groups (Domain\MyDBStandardUsers) to grant access to the SQL Server as a login.  (At the domain level, this group would have all Windows logins that need to same rights as members of the group.) 

    Once a login is added to the SQL Server, it can be made a user of a database.  Database rights are granted to users not to logins.  (But your user name can be the same as the login name unless you want it to be different.)

    Database roles are simply containers for rights.  Create a role for the StandardUsers and assign the Domain\MyDBStandardUsers as a role member.

    Now grant rights to the role and all users (whether as Windows Logins or as members of a Windows Group) who are role members will get those rights.

    Granting rights:

    public - Everybody with access to the database is a member of public.  Which means that you want to limit what rights get granted to public.

    Granting very sweeping rights can be done at the schema or the database level.   Here is how you grant everyone in a role rights to execute all stored procedures in the database.

    GRANT EXECUTE ON DATABASE::MyDB TO StandardUsers

    You can do this for SELECT, UPDATE, DELETE, and INSERT as well, but there are hazards with giving away so many direct rights.  However, if all of your selecting, inserting, deleting is done through Stored Procedures, the users do not need any SELECT,UPDATE,DELETE, or INSERT rights.   This is because the Stored Procedures (which they can execute) will do all the work for them.

    Of course, you can mix and match these rights according to your needs.  Granting at the database level is very broad, but you can grant to specific objects at a much finer level.

    You should find these posts interesting:  http://blogs.msdn.com/b/lcris/archive/tags/basic+sql+server+security+concepts/default.aspx

    RLF

    Monday, January 31, 2011 6:31 PM