New to administrative side of SQL

Question

New to administrative side of SQL

rbarrera-587788

Newbie

Points: 3
More actions
December 20, 2007 at 9:39 am

#119487

I've tried searching Books Online and it just confuses me more. Google is no help and no, we do not have a dba, so I have to resort to forums. I need to create a login that can create a database and have full ownership of that database. That login should also be able to start/stop SQL services, backup that database, restore from file, etc. I've tried creating the login first, assigning it db_owner persmissions to the databases created by that login. Does that login need other server roles besides dbcreator? What permissions does it need for master/model/msdb/tempdb, etc? This user will have to be created for both SQL Express and SQL 2005 Enterprise. I'd appreciate any help.

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

You can just create a user and the role of SysAdmin.

Members of the sysadmin fixed server role can perform any activity in the server. By default, all members of the Windows BUILTIN\Administrators group, the local administrator's group, are members of the sysadmin fixed server role.

-Roy

rbarrera-587788 Newbie Points: 3 More actions · Answer 2

Thanks for the reply but sysadmin is too powerful. I was hoping to get away with just one account but it looks like we'll have to duplicate the sa account to make the sysadmin account more secure, delete the sa account, and then use another account with minimal priveledges that our application will use to access the database.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

You should not have too many sysadmin accounts in your DB. Either keep the SA or remove it and add another one. Never let any admin privileged account access data from any application.

Create a role, Create a new user with No rights other than PUBLIC) and add that user to the role. Make sure you do not give direct Update, Insert, delete or even select permission to the user to any tables or views that is accessing the DB from application. All access should be through Stored Procs.

Just my 2 cents.....

-Roy

rbarrera-587788 Newbie Points: 3 More actions · Answer 4

Yep, we're going to script the SA account to create another sysadmin, and then disable the SA account. Only problem we see is updateing stored procedures.