locked
Database permission not set for the user when creating new database RRS feed

  • Question

  • I am using sql server 2008 express.It has some users created with different permission. The admin has all permission and i have created a user "reader" which has only read permission. The problem is that if i create new database then admin will get proper permission but the user "reader" will not get the permissions properly.Why does this happen? Please help me so that the user gets proper default permission to the database.
    • Moved by Kalman Toth Wednesday, January 30, 2013 10:16 PM Not database design.
    Wednesday, January 30, 2013 10:40 AM

Answers

  • What you can do is add the user to the model database and give the default permissions, So that any new database created will have the user already added with default permissions



    Regards
    Satheesh

    • Marked as answer by IT researcher Friday, February 1, 2013 8:09 AM
    Wednesday, January 30, 2013 10:57 AM

All replies

  • but the user "reader" will not get the permissions properly.Why does this happen?

    That's quite normal. "SysAdmin" is a server role and "db_datareader" is a database role, so you have to add the user as database user and you have to add the database user to the database role for every new database.

    Olaf Helper

    Blog Xing


    Wednesday, January 30, 2013 10:50 AM
  • What you can do is add the user to the model database and give the default permissions, So that any new database created will have the user already added with default permissions



    Regards
    Satheesh

    • Marked as answer by IT researcher Friday, February 1, 2013 8:09 AM
    Wednesday, January 30, 2013 10:57 AM
  • http://sqlblog.com/blogs/uri_dimant/archive/2010/09/02/be-careful-to-grant-dbcreator-server-role-to-the-user.aspx

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, January 30, 2013 12:18 PM
  • So my question before going the model bd route that Satheesh suggests is if this is something you want for all databases? And how you are using this "reader" user. By default, when you create a user, it has no permissions. There is a dbo user that is created in the database that is the owner of the database, but I don't really think of it is a user that normal people use, but is mapped to the owner (and typically when a system administrator login is using the database they will be mapped to that user).

    So when you create the user "reader" what DDL did you use the first time, and then again the second. An important factor to understand is that users are part of the database, and when you create the database, it is empty (except for what is in the model database), and doesn't have users typically. When you create a new user, you usually link them to a login, and then assign them to either system roles like db_reader, db_writer (which I generally dissuade people from doing unless it is truly what they want in all use cases..._), or are granted rights to use a entire schema or individual objects.

    If I were going to do this, and I wanted a "cookie cutter" database that I could replicate with the same users I would strongly consider just building a script that has all of the things you want in the database set up, parameterizable by database name (probably using SQLCMD variables).

    Putting your objects in model means that any database you build (even one for another purpose) would have this security/any objects you want to load by default, and I prefer to have control over the situation more strongly.  Post your DDL for your database create/user create and we can try to help you fashion the solution you want to end up with.


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Kalman Toth Wednesday, January 30, 2013 10:17 PM
    Wednesday, January 30, 2013 8:13 PM