the account to create database

Question

the account to create database

sqlfriend

SSC Guru

Points: 52769
More actions
April 12, 2011 at 1:57 pm

#237601

As a dba, when you create database on production server, what account do you usually use?
user sa account, or your own windows account, or some other system account?
I know it's a good practice to disable sa account, so what are most dba do?
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

When I create a database I am always logged in as my Windows Authenticated logic which places me as part of the SysAdmin group.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

Windows Authentication is preferred, however if you do not have it setup for easy use then create a separate SQL Login for yourself and add it to the sysadmin Fixed Server Role. It's not considered good practice to use the sa login for regular use.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

Thank you. Sorry I didn't make it clear.

I do use my window authenticated login- my own username to create databases.

What I mean is if the database is created in the context of me, - the database owner is my login, and if my login is removed when some day I no longer work here, will that become a trouble?

Thanks

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

Yes, it can cause problems. Here is a good thread on the topic: http://www.sqlservercentral.com/Forums/FindPost949846.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 5

when the login is dropped the db owner will be null but still accessable to every one who have access to it. But there are scenarions where it restricts to do some operations.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Jeffrey Williams SSC Guru Points: 90269 More actions · Answer 6

After creating a new database, I will change the owner of the database to sa. It doesn't matter who I am logged in as - whether that is a SQL login or Windows login - I still change the owner to sa.

I also make sure this is done after restoring databases to a new server.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sqlfriend SSC Guru Points: 52769 More actions · Answer 7

So what you do is a simple step like below:

USE dbname

EXEC sp_changedbowner 'sa'

Is that correct?

Jeffrey Williams SSC Guru Points: 90269 More actions · Answer 8

Basically, I will either use the GUI - or I will use powershell, depending upon how many databases and what else is going on at the moment.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

annasql (4/12/2011)
So what you do is a simple step like below:
USE dbname
EXEC sp_changedbowner 'sa'
Is that correct?

The note at the top of this article http://msdn.microsoft.com/en-us/library/ms178630.aspx says sp_changedbowner will be removed in a future version of SQL Server and to use ALTER AUTHORIZATION instead.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sqlfriend SSC Guru Points: 52769 More actions · Answer 10

Thanks all.

When you say you use GUI to change dbowner, how do you do that?

Thanks

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 11

Exec Sp_changedbowner '<loginnAme>'

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Jeffrey Williams SSC Guru Points: 90269 More actions · Answer 12

annasql (4/13/2011)
Thanks all.
When you say you use GUI to change dbowner, how do you do that?
Thanks

Right-click on the database in Object Explorer or Object Explorer Details - select properties and change the owner.

Most of the time, I use powershell though - which is fairly easy. Right-click on the database folder and select the option for powershell. That will open powershell at that location. In the powershell prompt, type 'dir' or Get-ChildItem to list the databases and their current owners.

From the powershell prompt:

PS> Get-ChildItem | % {$_.SetOwner('sa'); $_.Refresh()}

This will change the owner to 'sa' for all user databases. If you only wanted to change the owner for a single database, you would do the following:

PS> Get-Item dbName | % {$_.SetOwner('sa'); $_.Refresh()}

Replace dbName with your database name. You could also do the following:

PS> $db = Get-Item dbName; $db.SetOwner('sa'); $db.Refresh()

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sqlfriend SSC Guru Points: 52769 More actions · Answer 13

I will play with the way you do it in power shell.

I learned powershell for windows and ad, but don't know how to work with sql server with SSMS, is there any good resource or tutorial to start with?

Thanks so much

Jeffrey Williams SSC Guru Points: 90269 More actions · Answer 14

I don't know of any specific tutorials - there are some, you just have to google them. I mostly just have learned on my own and don't really do a lot with it.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs