locked
Give permission to other users RRS feed

  • Question

  • I am an Admin to a System and the user needs to work with SQL Server 2008 R2 but whenever he performs any action like creating a database, it gives an permission error requiring that the user has to start SQL server as administrator. How can I give him the permission once. Thanks
    Friday, April 11, 2014 9:24 AM

Answers

  • Hello Christian,

    That depends which permissions in Detail he Needs.

    To create databases he have to be at least member of the Server role "dbcreator"; see Permissions of Fixed Server Roles (Database Engine)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sofiya Li Monday, April 14, 2014 6:31 AM
    • Marked as answer by Sofiya Li Friday, April 18, 2014 1:58 AM
    Friday, April 11, 2014 10:27 AM
  • Or have the CREATE DATABASE permission granted to the user in the master database.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Sofiya Li Friday, April 18, 2014 1:58 AM
    Friday, April 11, 2014 4:02 PM
  • 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 Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sofiya Li Monday, April 14, 2014 6:31 AM
    • Marked as answer by Sofiya Li Friday, April 18, 2014 1:58 AM
    Sunday, April 13, 2014 1:08 PM
  • Hi Christian,

    You start SQL Server Management Studio with “Run as administrator”, connect to the SQL Server instance via your account, then check which server role is used to grant to your user account. If your account  just is public role, and you could not create database in the instance. You need to connect to your administrator or use windows admin account to grant the related permission  to your account or create new account with dbcreator role for you. As other post, if you want to add a member to a SQL Server Database role, you can review the following article.

    http://technet.microsoft.com/en-us/library/aa905200(v=sql.80).aspx

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Friday, April 18, 2014 1:58 AM
    Monday, April 14, 2014 7:17 AM

All replies

  • Hello Christian,

    That depends which permissions in Detail he Needs.

    To create databases he have to be at least member of the Server role "dbcreator"; see Permissions of Fixed Server Roles (Database Engine)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sofiya Li Monday, April 14, 2014 6:31 AM
    • Marked as answer by Sofiya Li Friday, April 18, 2014 1:58 AM
    Friday, April 11, 2014 10:27 AM
  • Or have the CREATE DATABASE permission granted to the user in the master database.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Sofiya Li Friday, April 18, 2014 1:58 AM
    Friday, April 11, 2014 4:02 PM
  • 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 Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sofiya Li Monday, April 14, 2014 6:31 AM
    • Marked as answer by Sofiya Li Friday, April 18, 2014 1:58 AM
    Sunday, April 13, 2014 1:08 PM
  • Hi Christian,

    You start SQL Server Management Studio with “Run as administrator”, connect to the SQL Server instance via your account, then check which server role is used to grant to your user account. If your account  just is public role, and you could not create database in the instance. You need to connect to your administrator or use windows admin account to grant the related permission  to your account or create new account with dbcreator role for you. As other post, if you want to add a member to a SQL Server Database role, you can review the following article.

    http://technet.microsoft.com/en-us/library/aa905200(v=sql.80).aspx

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Friday, April 18, 2014 1:58 AM
    Monday, April 14, 2014 7:17 AM