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.
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.
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?
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.