Creating User with Admin Rights on only one database

Question

Creating User with Admin Rights on only one database

kd_um

SSC-Addicted

Points: 460
More actions
September 22, 2011 at 4:03 am

#257772

Hello Experts,
We are working with SAP B1 and SQL Server 2005, and have different databases for different company. During the development phase of implementing SAP in new company we need a user with rights to create backups, reinstall it, and delete it. Is it possible to create a user with admin rights on only one company's database? If yes, how can it be done?
Thank you in advance
KD

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

If the db is not there, sdd the user to the db_creator server role, so that he can create the database and be its owner.

If the db is already there, add him to db_owner database role.

If the installation process involves dropping and creating the db from scratch, also add him to db_creator server role.

This should be enough, though I don't know what the app does. If it doesn't need to run anything outside of the database scope, you should be all right.

-- Gianluca Sartori

kd_um SSC-Addicted Points: 460 More actions · Answer 2

Hello Gianluca,

Thank you very much for your answer. Alhough I have to confess that I have not understood it completely due to my limited database administration vocabulary, but I think your solution can solve my problem. So, you mean that in a server with five different databases of the same application, I can create a user with 'db_creator server role'. This user Do Not have access to any other databases but only the one I want.

If I have got it right, could you please give me clue on the steps to create such user - by SQL Management Studio or any other way.

The database is for the ERP application - SAP Business One.

Kind Regards,

KD

spaghettidba SSC Guru Points: 105712 More actions · Answer 3

I'm sure there's a detailed installation guide shipped with SAP. Have you checked that?

As far as the db_creator role, in SSMS, expand security/logins, right click the user and select "properties". In the "Server Roles" applet, check the db_creator role. This allows the login to create any database. In your case, if the SAP database is not there, you should grant the user this server role in order to let him create the db.

If the db is already there, you don't need to grant db_creator, but you just have to grant permissions on the existing database. Again, open the properties window for the login and open the "User Mapping" applet. You should now see a list of databases. Click on the target database and, in the bottom panel, check the db_owner role.

This grants the user full control over the database.

Hope this helps

Gianluca

-- Gianluca Sartori

kd_um SSC-Addicted Points: 460 More actions · Answer 4

Hello,

Thanks a lot again. I have advised the server team about it. I do not have access to the server so I will get to know what was the result once they come back.

Kind Regards,

KD