CREATE DATABASE permission denied in database 'master'

Hi,

I'm using Visual Studio 2005 Pro and SQLExpress that comes with it.

I have my program running fine in XP Pro OS using a window user "Glen" (Computer administrator) with Administrator rights. This means that I installed VS 2005 using this window user "Glen"

I created another windows user "TestUser" (Limited account) in the same physical PC.

I tried to run the program and on the part that I need to access SQL table, I got the error [CREATE DATABASE permission denied in database 'master']

At the same time while using "TestUser" and running sqlcmd (to check if I can connect to SQL), I also got error HResult 0x2, Level 16, State 1.

I read alot on MSDN discussions and related links but it seems that I can't get the solution that I need.

SO HERE ARE MY QUESTIONS :

1. Am I allowed to run my program using user "TestUser" since SQL is installed using "Glen" windows user?

2. Do I need to add access rights to "TestUser" to allow the user to have CREATE rights? (Note : for security reason, I can add other access rights except Administrator)

Thanks in advance for all you help.

It seems there are few things going on here. Let's take a moment and break each one down.

First, the account used to install SQL Server is normally a System Administrator. What's more important are the accounts used to start the services for SQL Server. You have a few choices there, but most often it's best to use a regular account, rather than LocalSystem or NetworkingSystem. You can find out more about that in Books Online searching for "Services" . When you install SQL Server, by default the local Windows Administrator's group is placed in the SQL Server sysadmin Role, which allows all rights for everything. Other users don't have access at all (yet).

Security inside SQL Server is independent of the installation or the startup accounts. Since the "Glen" account is a local administrator, he can do anything he wants in SQL Server. If you created a "TestUser" server login, they can connect, but they can't do anything else. You'll need to assign them a database, create a user in the database tied to the "TestUser" login, and grant rights there.

There are server-level rights, and database-level rights. The CREATE DATABASE statement is a server-level right, and most users don't need that.

Books Online has a great set of topics on SQL Server Security that will help you sort all this out. You can also see my articles on Security starting here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=35&rl=1

Buck Woody

|||

Thanks for a quick reply Buck.

I have another question related to your answer. You mentioned about "LocalSystem" or "NetworkingSystem".

Are you pertaining to the Log On tab section "Log on as:" found in the SQL Server (SQLEXPRESS) Properties in the SQL Server Configuration Manager? Are you advising me to select "This account:" and create a user from there?

I will read more on the Online Books at the same time. This is to know the database that I need to assign to "TestUser". I am guessing here if you are talking about the application database or the database originally in the SQLEXPRESS like the master, model, etc.

Thanks again.

|||

That's right. You can also set that in the Services applet of the Control Panel.

The application database is the only one that needs a user account, in addition to a server login. If you check that site on InformIT, you'll see a reference to those.

Buck