sql server (alternate): This is a very basic question, perhaps more of a Windows XP
Professional OS permissions question than a dB or programming
question: how to create and access SQL SERVER databases from an
account other than "Administrator"; for example, from a "Power User"
account?

As anything other than an "Administrator" user (i.e. as a Power User),
I keep getting (when I try from inside of MS Visual Studio 2005
development environment) the error message: "CREATE DATABASE
permission denied in database 'master'

Using the SQL Server Express Surface Area Configuration tool, I set
the parameters below to "enabled" (they were disabled). This only
helped in one respect: now I can create a database with Visual Studio
2005 (using the Server Explorer tool) when logged in as an
"Administrator". But for security reasons (which I'm not even sure
are valid, but at least in my mind they are) I would like to log in as
a Power User.

My configuration: Windows OS on a standalone Pentium 4 PC connected
to the internet--I'm using Visual Studio 2005 and programming in
C#.NET and C++.NET using ADO.NET. I don't need to access any other PC
in any network--I'm just learning the language at this point.

Any ideas? I did remove some prior versions of SQL Server '7' which
helped remove some other unrelated error messages, and, like I say,
from inside the Administrator account I can program and create
databases using the Server Explorer of Visual Studio 2005, but I'd
like to do so from a non-Admin account.

Also whether I can disable some of the parameters below--i.e., do I
really need the "xp_cmdshell" enabled?

Thanks!

RL

Configuring and Managing SQL Server Express
=B7 For improved manageability and security, SQL Server 2005 provides
more control over the SQL Server surface area on your system. To
minimize the surface area, the following default configurations have
been applied to your instance of SQL server:

o TCP/IP connections are disabled [changed to enabled]
o Named Pipes is disabled [changed to enabled]
o SQL Browser must be started manually
o OPENROWSET and OPENDATASOURCE have been disabled
o CLR integration is disabled [changed to enabled]
o OLE automation is disabled [changed to enabled]
o xp_cmdshell is disabled [changed to enabled]

[This works fine but only from inside "Administrator"--RL]

raylopez99 (raylopez99@yahoo.com) writes:
[quoted text, click to view]

That's indeed an issue of SQL Server permissions.

When you are logged in as an Administrator in Windows and connect to
SQL Server, you account maps to BUILTIN\Administrator which has sysadmin
privilege in SQL Server. That is, you can do anything.

WHen you connect with some other Windows user, no get no such extra
thrills, but you need to grant that login rights to do things. For instance

GRANT CREATE DATABASE TO DOMAIN\PowerUser

You can also add that user a role which has the privileges you want,
for instance to the sysadmin role.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]

Erland Sommarskog-- thanks.

At the risk of looking even more stupid, if you know of how to "also
add that user a role which has the privileges you want, for instance
to the sysadmin role" within Visual Studio 2005 and/or Windows XP (for
the program SQL Server 2005 Express Edition), please feel free to let
me know. I just want to add the Power User to have Administrator
access for the Visual Studio 2005, when working on ADO.NET (SQL Server
2005), not for all programs, if possible. For now I will simply
program while logged in as an Administrator, which seems to be a good
workaround to my problem.

I've also ordered some books on ADO.NET and SQL SERVER from O'Reilly
and Microsoft Press; if you have any favorites for a C#/C++ programmer
hobbiest, let me know.

RL

raylopez99 (raylopez99@yahoo.com) writes:
[quoted text, click to view]

Do I understand this correctly that you want your user to have heavy
perms when connected through VS and your application, but not when it's
connected through Management Studio? There is no way you can assign
permissions per application. Permissions are per logins and users.

Of course, it's a legit requirement that a user should only be able to
access objects in the database through the application, as the application
then can control what the user can see and update. There are a couple of
ways to implement this requirement. The method that has been most tested
and rried is to use stored procedures. There are several ways that
permissions can be granted through stored procedures, whereof the most
useful is ownership chaining. If all stored procedures and tables are
owned by dbo, the users only need execute permissions to the stored
procedures.

There are ways to handle this without stored procedures, but I am less
of fond of these methods.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]

Thank you Erland. I see the problem is not as simple as I thought. I
also see I have two problems: one is what you addressed, the other is
more simple: how to use VS2005 from an account other than
"Administrator" when working on databases. So far I've not been able
to figure out this, and only use "Adminstrator" to code. This simple
question can be answered by an experienced user of VS2005, and is to
an extent a trivial question since I can do programming in VS as
"Administrator" (it's annoying to switch users using Windows XP Pro,
but it's only an annoyance, nothing more).

THanks for your help,

RL

raylopez99 (raylopez99@yahoo.com) writes:
[quoted text, click to view]


I did not answer that question, since I was uncertain of the scope of
your question. But it's fairly simple, although there are several options.

One is to enable SQL Server Authentication through Management Studio.
(Right-click the server itself in the Object Explorer, select Properties
and go the the Security tab. You need to restart SQL Server for the
setting to take effect.) Then you can connect as sa from VS and have
sysadmin rights. The good thing with this is that when you connect
through your application with Windows authentication, you are a plain
user and can test that you have granted that user the right permissions.

The other option is to add your Windows user to the sysadmin role:

sp_addsrvrolemember 'sysadmin', 'MACHINE\User'

(If command fails, try swapping the parameters; I may not remember the
order correctly.)
You would first have to grant MACHINE\User access to the SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]

Well, turns out I don't have "Management Studio" on my system (yet
strangely I was able to create a simple SQL database and run some
commands). If you don't have "Management Studio" you don't have
"Object Explorer", even though it's possible to have SQL Server 2005
Express (a 36.5 MB file) and not the SQL Server Management Studio
Express (a 46.1 MB file) installed on your PC, as I have. Details
here: http://go.microsoft.com/fwlink/?LinkId=65110

I'll post again if I'm successful, for anybody reading this thread in
the future.

RL

Information on OE below...

Using Object Explorer

Object Explorer, a component of SQL Server Management Studio, connects
to Database Engine instances, Analysis Services, Integration Services,
Reporting Services, and SQL Server Compact Edition. It provides a view
of all the objects in the server and presents a user interface to
manage them. The capabilities of Object Explorer vary slightly
depending on the type of server, but generally include the development
features for databases, and management features for all server types.

Viewing Object Explorer

Object Explorer is visible in the Management Studio by default. If you
cannot see Object Explorer, on the View menu, click Object Explorer.

Connecting Object Explorer to a Server

To use Object Explorer you must first connect to a server. Click
Connect on the Object Explorer toolbar and choose the type of server
from the drop-down list. The Connect to Server dialog box opens. To
connect, you must provide at least the name of the server and the
correct authentication information.

[quoted text, click to view]

Hi Erland--it worked! Thank you very much, now I can code as a non-
Administrator with no problem...except one: http://tinyurl.com/38ssp8
(a sort of bug in VS2005 doing SQL it seems)

However, at least I did solve this problem and I appreciate your
advice.

Cheers,

Ray

raylopez99 (raylopez99@yahoo.com) writes:
[quoted text, click to view]

I would definitely recommend that you download and install SQL Server
Management Studio Express. In the long run it will be difficult to be
without it. Particularly if you ask questions in newsgroups, because most
people answering questions will assume that you have Management Studio in
some form. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

raylopez99 (raylopez99@yahoo.com) writes:
[quoted text, click to view]

As I understand that link, it's not a bug at all. If you want to create
a procedure, you use CREATE PROCEDURE. If you want to change an existing
procedure, you use ALTER PROCEDURE. Alternatively, you drop the existing
procedure first, but then you would have to reapply permissions. Visual
Studio helps you out by changing CREATE to ALTER for you.

Also, one thing to keep in mind is that you enter things into the database,
that is not a Save operation, although unfortunately some tools use that
terminology. As with all other programming code, you save your code to disk
and then put it under version control. Regard what's in the database as
binaries.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at