locked
CREATE DATABASE permission denied in database RRS feed

  • Question

  • I am trying to use the new AS COPY OF syntax to copy a database on a different server (but on the same location) and I am unable to succeed.

    I can confirm that the login and passwords for both databases are the same

    I added the dbmanager role to the login of the destination

    When I run the command on the Master table of the destination database

    CREATE DATABASE MyTable  AS COPY OF [serverid.MyTable]

    I get the error

    Msg 262, Level 14, State 1, Line 1

    CREATE DATABASE permission denied in database 'serverid.MyTable'

    What step have I missed?

    Friday, August 27, 2010 12:40 AM

Answers

  • Sorry, I didn't mean to propose that as an answer... I'm new to these forums :)

    I'm sure you probably already figured the issue out, but I ran into the same problem. How I resolved it was by using the following syntax:

    CREATE DATABASE [dbname] AS COPY OF [source-hostname-only].[dbname];

    Example:

    CREATE DATABASE [test] AS COPY OF [lbbajgx93].[test];

     

    The problem I kept having is I was using the full dns name instead of simply the hostname.

    • Proposed as answer by cadams501 Thursday, October 14, 2010 3:43 PM
    • Marked as answer by Brad Calder Sunday, March 27, 2011 12:50 AM
    Thursday, October 14, 2010 3:43 PM
  • I just had this problem and the in my case and I am guessing in your case it the source dbname is incorrect.

    In my case I has made a typo in the dbname and was refering to a db that did not exist. So it was annoying that a permissions issue was thrown rather than something more useful such as 'database does not exist' or similar.

    In your case it is because you have encased the server and db name in square brackets rather separately i.e:

    CREATE DATABASE MyTable  AS COPY OF [serverid].[MyTable] rather than incorrectly CREATE DATABASE MyTable  AS COPY OF [serverid.MyTable].

    Equally you would get this error if you had something like CREATE DATABASE MyTable  AS COPY OF [MyTableThatIsSpletIncorrectly]

    HTH.

    • Proposed as answer by Narasimhan Padmanabhan Friday, September 30, 2011 8:37 PM
    • Marked as answer by BTMI Friday, September 30, 2011 9:09 PM
    Saturday, June 11, 2011 7:07 AM

All replies

  • Hi,

    Could you check that login in source database, ensure the login has the database owner (DBO) permission of the source database.

    http://msdn.microsoft.com/en-us/library/ff951624.aspx#permissions
    On the source server: The login must be the DBO of the source database. Only the login that created the source database, the DBO, can copy that database to another database on the same server or on a different server.

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Friday, August 27, 2010 5:13 AM
  • I did check this by generating a DELETE/CREATE Script for the login and according to this the main login is the dbo one (I only have one login for all of my databases)

    Is there a more conventional way to check this?

    Friday, August 27, 2010 6:54 AM
  • You can check the DB creator with below query:

     

    select d.name, l.name from sys.sysdatabases as d

     inner join sys.sql_logins as l

     on d.sid = l.sid

     

    You can reproduce this error on the same server. You will still see that error message even if the login is the admin account, because the DB creator is not that login.

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Friday, August 27, 2010 7:48 AM
  • Thanks for that...

    This proved that the correct account was being used....it must be something different with username passwords between the 2 tables.  I will drop the new one and see if I can create it with the correct information again.

    Thank you for your help

    • Proposed as answer by cadams501 Thursday, October 14, 2010 3:40 PM
    Friday, August 27, 2010 8:05 AM
  • Sorry, I didn't mean to propose that as an answer... I'm new to these forums :)

    I'm sure you probably already figured the issue out, but I ran into the same problem. How I resolved it was by using the following syntax:

    CREATE DATABASE [dbname] AS COPY OF [source-hostname-only].[dbname];

    Example:

    CREATE DATABASE [test] AS COPY OF [lbbajgx93].[test];

     

    The problem I kept having is I was using the full dns name instead of simply the hostname.

    • Proposed as answer by cadams501 Thursday, October 14, 2010 3:43 PM
    • Marked as answer by Brad Calder Sunday, March 27, 2011 12:50 AM
    Thursday, October 14, 2010 3:43 PM
  • I just had this problem and the in my case and I am guessing in your case it the source dbname is incorrect.

    In my case I has made a typo in the dbname and was refering to a db that did not exist. So it was annoying that a permissions issue was thrown rather than something more useful such as 'database does not exist' or similar.

    In your case it is because you have encased the server and db name in square brackets rather separately i.e:

    CREATE DATABASE MyTable  AS COPY OF [serverid].[MyTable] rather than incorrectly CREATE DATABASE MyTable  AS COPY OF [serverid.MyTable].

    Equally you would get this error if you had something like CREATE DATABASE MyTable  AS COPY OF [MyTableThatIsSpletIncorrectly]

    HTH.

    • Proposed as answer by Narasimhan Padmanabhan Friday, September 30, 2011 8:37 PM
    • Marked as answer by BTMI Friday, September 30, 2011 9:09 PM
    Saturday, June 11, 2011 7:07 AM
  • I just hit this error and it turned out that I had also just typoed it - spelled the name of the source database wrong. Sigh. Very confusing error message.

    Friday, September 30, 2011 8:37 PM