create table permission denied

Hi,

i run an asp.net application which uses sql server express.
i defined a login 'aspnet' (IIS 5.0) and for the specific database, an user
'aspnet' with following roles:
db_datareader and db_datawriter.

Now, any user who uses that application must also be able to create
programmatically tables in that database. My question is: which role do i
have to give to user 'aspnet'?

I use Studio Management express.

Thanks
Tartuffe

The answer depends on which version of SQL Server you are using, since SQL Server 2005 dramatically enhanced permissions in the database. Which version?

Also, must the user be able to create ANY table in the database, or only a specific table? I think the former, but please confirm.

Don

|||

Hi, thanks for replying.

I use sql server 2005 express with Studio Management express.

Each windows-account in the domain in our organization who starts the application creates automatically (in code-behnd) a table with his personal (unique) number in the organization (e.g. L0564).

This happens only the first time he starts the application. So each member has his own table.

We use IIS 5.1, so the account which runs under asp.net is ASPNET. For another application, i defined a login in Studio Management and then for the database of that application, i defined a user 'aspnet' with following roles: db_reader and db_writer (i didn't use schema's because it's not very clear to me ..). This works, but there was no need to cerate a table.

I did the same for this new application +I db_owner and then it works. But i think it's probably too many privileges ... So: my question is: which privileges to give to 'aspnet' and how to do that in Studio Management?

Thanks

|||

You need the dbowner... only then you will be able to do the specific operations like creating tables and other things. DBWrite and Read will allow you to do simple updates insert and deletes.

|||

Thanks. I'll try.

If you don't mind, .. what if the ASPNET account must also be able to create databases? Is it suffisant with db_owner only?

|||

Yes i think so .. it doesn't matter which account it is .. till the time you map correct roles

|||

i tried with db_owner and Aspnet can create tables programmatically.

But Aspnet cannot create a new database with only db_owner.

|||

you need to be sysadmin for that

|||

Thanks

sql

create table permission denied

Hello,
I hope this is something simple to fix, but so far I had no luck...
Situation:
I created sql login, let's say 'test' and allowed access to database
db1. Actually, I made the account as the db_owner of db1. The test
account was not added to any of the server roles. Now, everything is
working fine until I try to create table or view or proc, etc. in the
db1 while logged in as the test user.
When creating table I get that CREATE TABLE permission denied in
database 'db1'. I tried executing GRANT CREATE TABLE after logging on as
sysadmin to that test account. That didn't help. IS there some DENY
somewhere that I don't see? How can I check what is preventing me from
creating table using that account?
Any comments?
Thanksgot it ... database role 'public' had DENY on creating tables. So even
if my test login had GRANT, the DENY on public denied for test as well
because test is public and db_owner.
laimis wrote:
> Hello,
> I hope this is something simple to fix, but so far I had no luck...
> Situation:
> I created sql login, let's say 'test' and allowed access to database
> db1. Actually, I made the account as the db_owner of db1. The test
> account was not added to any of the server roles. Now, everything is
> working fine until I try to create table or view or proc, etc. in the
> db1 while logged in as the test user.
> When creating table I get that CREATE TABLE permission denied in
> database 'db1'. I tried executing GRANT CREATE TABLE after logging on as
> sysadmin to that test account. That didn't help. IS there some DENY
> somewhere that I don't see? How can I check what is preventing me from
> creating table using that account?
> Any comments?
> Thanks

Create SP denied ?

We added a user to a database with db_datareader, db_datawriter and Create Procedure Privilages .. while creating stored procedures using dbo. prefix he gets an error

Server: Msg 2760, Level 16, State 1, Procedure test4, Line 3
Specified owner name 'dbo' either does not exist or you do not have permission to use it.

What is the problem. We don't want him to create procedures with his uid as prefix .. Please helpYou need to put him/her in the dbo_owner role...|||Is it necessary ? If he is dbo he doesn't need to specify dbo. while creating objects . I don't want him to be a dbo|||If yo uwant him to vcreate objetcs as dbo (the owner) he has to have the rights to be in the group...

Or...

He can develop in his own db you set up for him/her...make the dbo in that group (or not) and let them create objects without hard coding the owner...

Then have him give you the scripts and you compile them.

What'dya think?sql

CREATE FILE access denied

I get this error message when trying to create a database. This happens from
the Management Studio, from regular queries, etc. No other posts seem to
resolve this problem exactly that I can find. Thanks for any insight into
this problem...
USE MASTER
CREATE DATABASE XYZ
ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
Error message:
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check
related errors.
Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'c:\XYZ_data.mdf'.
Hi
Check that the account that SQL Server runs under has file system
permissions on c:\
OS Error 5 = access denied.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"XCode247" <XCode247@.discussions.microsoft.com> wrote in message
news:7DE6CBB0-087A-4CD2-9B45-AEAE45532C06@.microsoft.com...
>I get this error message when trying to create a database. This happens
>from
> the Management Studio, from regular queries, etc. No other posts seem to
> resolve this problem exactly that I can find. Thanks for any insight into
> this problem...
> USE MASTER
> CREATE DATABASE XYZ
> ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
> LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
> Error message:
> Msg 1802, Level 16, State 4, Line 2
> CREATE DATABASE failed. Some file names listed could not be created. Check
> related errors.
> Msg 5123, Level 16, State 1, Line 2
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'c:\XYZ_data.mdf'.
>
|||Yes, sure enough...
In Configuration Manager, Service properties. The 'Log on as' account was
'Network Service'. The help button from that page says a 'domain user account
with minimal rights' is recommended. Changing the account causes it to work.

CREATE FILE access denied

I get this error message when trying to create a database. This happens from
the Management Studio, from regular queries, etc. No other posts seem to
resolve this problem exactly that I can find. Thanks for any insight into
this problem...
USE MASTER
CREATE DATABASE XYZ
ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
Error message:
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check
related errors.
Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'c:\XYZ_data.mdf'.Hi
Check that the account that SQL Server runs under has file system
permissions on c:\
OS Error 5 = access denied.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"XCode247" <XCode247@.discussions.microsoft.com> wrote in message
news:7DE6CBB0-087A-4CD2-9B45-AEAE45532C06@.microsoft.com...
>I get this error message when trying to create a database. This happens
>from
> the Management Studio, from regular queries, etc. No other posts seem to
> resolve this problem exactly that I can find. Thanks for any insight into
> this problem...
> USE MASTER
> CREATE DATABASE XYZ
> ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
> LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
> Error message:
> Msg 1802, Level 16, State 4, Line 2
> CREATE DATABASE failed. Some file names listed could not be created. Check
> related errors.
> Msg 5123, Level 16, State 1, Line 2
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'c:\XYZ_data.mdf'.
>|||Yes, sure enough...
In Configuration Manager, Service properties. The 'Log on as' account was
'Network Service'. The help button from that page says a 'domain user account
with minimal rights' is recommended. Changing the account causes it to work.

CREATE FILE access denied

I get this error message when trying to create a database. This happens from
the Management Studio, from regular queries, etc. No other posts seem to
resolve this problem exactly that I can find. Thanks for any insight into
this problem...
USE MASTER
CREATE DATABASE XYZ
ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
Error message:
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check
related errors.
Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'c:\XYZ_data.mdf'.Hi
Check that the account that SQL Server runs under has file system
permissions on c:\
OS Error 5 = access denied.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"XCode247" <XCode247@.discussions.microsoft.com> wrote in message
news:7DE6CBB0-087A-4CD2-9B45-AEAE45532C06@.microsoft.com...
>I get this error message when trying to create a database. This happens
>from
> the Management Studio, from regular queries, etc. No other posts seem to
> resolve this problem exactly that I can find. Thanks for any insight into
> this problem...
> USE MASTER
> CREATE DATABASE XYZ
> ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
> LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
> Error message:
> Msg 1802, Level 16, State 4, Line 2
> CREATE DATABASE failed. Some file names listed could not be created. Check
> related errors.
> Msg 5123, Level 16, State 1, Line 2
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'c:\XYZ_data.mdf'.
>|||Yes, sure enough...
In Configuration Manager, Service properties. The 'Log on as' account was
'Network Service'. The help button from that page says a 'domain user accoun
t
with minimal rights' is recommended. Changing the account causes it to work.

CREATE DATABASE permission denied in database master. error

got rid of my error about user login rights, it was all working yesterday. but for some reason i now get this error

 CREATE DATABASE permission deniedin database'master'.An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack tracefor more information about the error and where it originatedin the code.Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission deniedin database'master'.An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.Source Error:An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identifiedusing the exception stack trace below.Stack Trace:[SqlException (0x80131904): CREATE DATABASE permission deniedin database'master'.An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

i have this in my web.config file

<connectionStrings>
<add name="ConnectionStringTest" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf;Integrated Security=SSPI;Connect Timeout=30;User Instance=False"
providerName="System.Data.SqlClient" />
</connectionStrings>
<appSettings />

<system.web>
<!--
Set compilation debug="true" to insert debugging symbols into the compiled page.
Because this affects performance, set this value to true only during development.
-->
<compilation debug="true" />
<identity impersonate="true"/>

and my asp connection string is

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>"
SelectCommand="SELECT [entryID], [compID], [emailAddy], [answer] FROM [entry]"></asp:SqlDataSource>

if i set user instance to true i get a user permission error.

it says on sql server management that i have dbo rights on my database, but it wont let me put datareader or write on this login. any ideas? its driving me insane

Looks like SQL server could not connect you file to system

An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.

so it automatically switched you to master database which is default for new users and probably you have no rights to create table in it.

Is your database file local to your server?

Does you SQL server user accont has rights to access this database file?

Check it.

Thanks

|||

Is your database file local to your server? - yup

Does you SQL server user accont has rights to access this database file? - i only have 1 user account - funkymp which i log onto my computer which, it says on sql management tool that im the dbo for the database

|||

if i go into databases - databasename - security - users i have dbo (which is the funkymp account), guest, info schema, rob\aspnet and sys.

if i go into securiy - logins, i have funkymp there again, default database is master - should i change this to the database im trying to access? this is driving me up the wall had the same error for 2 das now, are there any sql commands that i can run as a new query to check if i have the rights to access that table with the user funkymp?

|||

Hi,

It seems that you might not be connecting to the correct database.

Please try to add Initial Catalog=<DatabaseName> in your connection string.

HTH.

CREATE DATABASE permission denied in database ''master''. (Microsoft SQL Server, Error: 262)

How exactly do I correct this problem on Vista?

TITLE: Microsoft SQL Server Management Studio Express

Create failed for Database 'kkl'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

I have ensured that I am logged into the system as Admin but cannot find where on the Server, I assign my login to the dbcreator group.

I have gone into Security >> Server Roles >> dbcreator. of which I am not a member. On a different system, where all works, however, I am not a member of this group either.

Any suggestions on how to kill this one?

Thanks in Advance.

Klaus

and this fixed my problem:

http://msdn2.microsoft.com/en-us/library/bb326612.aspx

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

Create database permision denied in database ' master' (MS SQL SERVER, ERROR 262

Cn not do anything with my sql server, everything i trt to do i get this message, user does not have permision, etc, ,

I am running windows Vista Business, SQL SERVER 2005

so what going on here

SQL Server SP1 is not supported on Vista... U must have SQL Server SP2... but sp2 is in CTP version... SP2 is to be released soon...so wait for a while...

http://www.microsoft.com/sql/howtobuy/windowsvistasupport.mspx

Madhu

|||True and try to test it on a other Windows edition and see the user privileges are not an issue.|||Just tryed with SP2. Got the same error. This is fun. Can any Microsoft MVP enlighten us please?|||

Have a look at this article:

http://msdn2.microsoft.com/en-us/library/bb326612.aspx

Thanks
Laurentiu