Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Red face Unanswered: dbo Cannot Add Users in SQL DB

    I'm using SQL 2000 and I'm the database owner. I have been running tests with stored procedures to add logins, revoke logins, add db access, revoke db access etc.

    Now I can't seem to add users to the database. I've tried using 'dbo' and 'domain\my_username' but I'm denied permission to run any stored procedures to try and add myself to the db_accessadmin or sysadmin roles.

    I've tried the New Database User dialog and choose <new> under login name. I browse for the user name on my domain, click OK, and I get the message 'You must be logged in as 'sa' or a member of sysadmin or securityadmin to perform this operation.'

    I thought the dbo always had full permissions on his database. This doesn't make sense to me.

    The only thing I can figure is my permissions have been revoked on the master db.

    Can anyone help?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    A DBO has full permissions on his own database, but adding a login is a server level task. You need to have the administrator add new logins for you, or be added to the securityadmin server role. You can, of course, add other logins that already exist as users in your database. You just can't create new logins.

  3. #3
    Join Date
    Mar 2003
    Posts
    130
    Oh and I'm using NT authentication.

    I forgot to add that I have always been able to create logins until now by browsing for the user on our network. I was also able to create databases on the SQL Server before now. Now the option to create a new db is grayed out on the menu.

    I can remove users (except in master) and delete databases but can't add users or create databases anymore.

    There's no way anyone else would have changed my permissions. It had to have something to do with the stored procedures I ran yesterday. So it seems if I could revoke my permissions then there would be some way for me to re-grant them.

    Would the 'sa' account be the admin for the server? I assume that would be the person that installed the SQL Server Application on our physical server. Would the only way to get my permissions back be to have him login under the 'sa' account and grant me permissions again?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Not necessarily, but you may be lucky if the server was setup for Mixed Atuhentication. You may be out of luck if it is for Windows Authentication only. But even this can be fixed if you all were doing master backup. In this case restoring master to the time before you "ran those stored procedures" would fix the problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •