create table riht on one specified table to one user

how can i give rights to the a user to create and drop one specified table
Hi
I am not at all sure why you wish to do this! If the structure does not
change then you can just truncate the table, if you do have a variable
structure it will probably lead to a nightmare of dynamic code. You should
write a stored procedure to encapsulate this process.
John
"sunil" wrote:

> how can i give rights to the a user to create and drop one specified table
>
>
|||You can do
GRANT CREATE TABLE TO username
Once the user has created the table, you can revoke above. Then user will now own the table and be
able to drop it. The user will not be able to specify some other user as owner, for that the
db_ddladmin role is needed (which is documented in Books Online what permissions comes with it).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sunil" <sunil@.discussions.microsoft.com> wrote in message
news:BE56743E-05C8-4054-BA21-F997B63A53BD@.microsoft.com...
> how can i give rights to the a user to create and drop one specified table
>
>
|||Thanks for the reply but i don't know at wht time the user will create and
drop the table, In a store procedure he will create the table and in the end
the user drop the table so every time that store procedure runs the user
require the create right and drop table rihts but if i give him ddladmin then
he can create other table and drop other table also . So it is security risk,
hence i require that he user can create one specified table that is mention
in the stored procedure
"Tibor Karaszi" wrote:

> You can do
> GRANT CREATE TABLE TO username
> Once the user has created the table, you can revoke above. Then user will now own the table and be
> able to drop it. The user will not be able to specify some other user as owner, for that the
> db_ddladmin role is needed (which is documented in Books Online what permissions comes with it).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "sunil" <sunil@.discussions.microsoft.com> wrote in message
> news:BE56743E-05C8-4054-BA21-F997B63A53BD@.microsoft.com...
>
|||Hi,
When u will create the table it will be created under that user and u
can use grant create table
but if u created the table under dbo user then everyone who has access
to that database will have access.
hoipe this help
from
Killer
|||Hi
Use a temporary table if it only needed in the scope of the procedure.
John
"sunil" wrote:
[vbcol=seagreen]
> Thanks for the reply but i don't know at wht time the user will create and
> drop the table, In a store procedure he will create the table and in the end
> the user drop the table so every time that store procedure runs the user
> require the create right and drop table rihts but if i give him ddladmin then
> he can create other table and drop other table also . So it is security risk,
> hence i require that he user can create one specified table that is mention
> in the stored procedure
> "Tibor Karaszi" wrote:

create table riht on one specified table to one user

how can i give rights to the a user to create and drop one specified tableHi
I am not at all sure why you wish to do this! If the structure does not
change then you can just truncate the table, if you do have a variable
structure it will probably lead to a nightmare of dynamic code. You should
write a stored procedure to encapsulate this process.
John
"sunil" wrote:

> how can i give rights to the a user to create and drop one specified table
>
>|||You can do
GRANT CREATE TABLE TO username
Once the user has created the table, you can revoke above. Then user will no
w own the table and be
able to drop it. The user will not be able to specify some other user as own
er, for that the
db_ddladmin role is needed (which is documented in Books Online what permiss
ions comes with it).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sunil" <sunil@.discussions.microsoft.com> wrote in message
news:BE56743E-05C8-4054-BA21-F997B63A53BD@.microsoft.com...
> how can i give rights to the a user to create and drop one specified table
>
>|||Thanks for the reply but i don't know at wht time the user will create and
drop the table, In a store procedure he will create the table and in the end
the user drop the table so every time that store procedure runs the user
require the create right and drop table rihts but if i give him ddladmin the
n
he can create other table and drop other table also . So it is security risk
,
hence i require that he user can create one specified table that is mention
in the stored procedure
"Tibor Karaszi" wrote:

> You can do
> GRANT CREATE TABLE TO username
> Once the user has created the table, you can revoke above. Then user will
now own the table and be
> able to drop it. The user will not be able to specify some other user as o
wner, for that the
> db_ddladmin role is needed (which is documented in Books Online what permi
ssions comes with it).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "sunil" <sunil@.discussions.microsoft.com> wrote in message
> news:BE56743E-05C8-4054-BA21-F997B63A53BD@.microsoft.com...
>|||Hi,
When u will create the table it will be created under that user and u
can use grant create table
but if u created the table under dbo user then everyone who has access
to that database will have access.
hoipe this help
from
Killer|||Hi
Use a temporary table if it only needed in the scope of the procedure.
John
"sunil" wrote:
[vbcol=seagreen]
> Thanks for the reply but i don't know at wht time the user will create and
> drop the table, In a store procedure he will create the table and in the e
nd
> the user drop the table so every time that store procedure runs the user
> require the create right and drop table rihts but if i give him ddladmin t
hen
> he can create other table and drop other table also . So it is security ri
sk,
> hence i require that he user can create one specified table that is mentio
n
> in the stored procedure
> "Tibor Karaszi" wrote:
>sql

create table riht on one specified table to one user

how can i give rights to the a user to create and drop one specified tableHi
I am not at all sure why you wish to do this! If the structure does not
change then you can just truncate the table, if you do have a variable
structure it will probably lead to a nightmare of dynamic code. You should
write a stored procedure to encapsulate this process.
John
"sunil" wrote:
> how can i give rights to the a user to create and drop one specified table
>
>|||You can do
GRANT CREATE TABLE TO username
Once the user has created the table, you can revoke above. Then user will now own the table and be
able to drop it. The user will not be able to specify some other user as owner, for that the
db_ddladmin role is needed (which is documented in Books Online what permissions comes with it).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sunil" <sunil@.discussions.microsoft.com> wrote in message
news:BE56743E-05C8-4054-BA21-F997B63A53BD@.microsoft.com...
> how can i give rights to the a user to create and drop one specified table
>
>|||Thanks for the reply but i don't know at wht time the user will create and
drop the table, In a store procedure he will create the table and in the end
the user drop the table so every time that store procedure runs the user
require the create right and drop table rihts but if i give him ddladmin then
he can create other table and drop other table also . So it is security risk,
hence i require that he user can create one specified table that is mention
in the stored procedure
"Tibor Karaszi" wrote:
> You can do
> GRANT CREATE TABLE TO username
> Once the user has created the table, you can revoke above. Then user will now own the table and be
> able to drop it. The user will not be able to specify some other user as owner, for that the
> db_ddladmin role is needed (which is documented in Books Online what permissions comes with it).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "sunil" <sunil@.discussions.microsoft.com> wrote in message
> news:BE56743E-05C8-4054-BA21-F997B63A53BD@.microsoft.com...
> > how can i give rights to the a user to create and drop one specified table
> >
> >
> >
>|||Hi,
When u will create the table it will be created under that user and u
can use grant create table
but if u created the table under dbo user then everyone who has access
to that database will have access.
hoipe this help
from
Killer|||Hi
Use a temporary table if it only needed in the scope of the procedure.
John
"sunil" wrote:
> Thanks for the reply but i don't know at wht time the user will create and
> drop the table, In a store procedure he will create the table and in the end
> the user drop the table so every time that store procedure runs the user
> require the create right and drop table rihts but if i give him ddladmin then
> he can create other table and drop other table also . So it is security risk,
> hence i require that he user can create one specified table that is mention
> in the stored procedure
> "Tibor Karaszi" wrote:
> > You can do
> >
> > GRANT CREATE TABLE TO username
> >
> > Once the user has created the table, you can revoke above. Then user will now own the table and be
> > able to drop it. The user will not be able to specify some other user as owner, for that the
> > db_ddladmin role is needed (which is documented in Books Online what permissions comes with it).
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "sunil" <sunil@.discussions.microsoft.com> wrote in message
> > news:BE56743E-05C8-4054-BA21-F997B63A53BD@.microsoft.com...
> > > how can i give rights to the a user to create and drop one specified table
> > >
> > >
> > >
> >
> >

CREATE TABLE rights problems

I cannot fix the rights depending on customers server...

when i create a table dynamicly with ASP.NET for MS SQL 2000, should I use :

CREATE TABLE [dbo] .[Component]

or

CREATE TABLE [Component]

-------

[id_Component] [int] IDENTITY (1, 1) NOT NULL ,

or

[id_Component] [int] IDENTITY (1, 1) COLLATE French_CI_AS NOT NULL ,

-------

shall I add COLLATE French_CI_AS to all columns ?

thank youIf you don't explicitly specify the collation, the system will use the database collation as the default.

It's a best practice to be explicit for DDL/DML. So, [dbo] and [COLLATE] should be specified.|||thank you !

Create table rights

hi All,
I have given a user ddladmin to a database, but this user still can't create
tables. I tried to "grant create tables to username" that ran sucessfully
but still can't create a table in my DB.
Can anyone tell me what I missed for this user? he can drop tables but not
create them.
Thanks,
JoeWith the specific 'create table' permission or the DDLAdmin role membership
that should permit the user to creatr the table.
How is the user atempting to create the table? Are they executing a DDL
script? If they are executing a script, make sure that the CREATE TABLE
script does not have the dbo context set in the table name. If it does, an
d
the user executing the scriipt is not a DBO, then they will be unable to
successfully create the table. If the script does have dbo. in the table
name, just remove the dbo. from the name and try executing it.
"jaylou" wrote:

> hi All,
> I have given a user ddladmin to a database, but this user still can't crea
te
> tables. I tried to "grant create tables to username" that ran sucessfully
> but still can't create a table in my DB.
> Can anyone tell me what I missed for this user? he can drop tables but no
t
> create them.
> Thanks,
> Joe
>|||Hi,
Looks like some one DENY the create table previlage for that user. But in
this case a GRANT command will over rule the DENY.
Could you post the exact error you are getting after running the GRANT
statement.
Thanks
Hari
SQL Server MVP
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:8DF5770F-4D14-4ADE-A3F1-2A47AFB3AAD4@.microsoft.com...
> hi All,
> I have given a user ddladmin to a database, but this user still can't
> create
> tables. I tried to "grant create tables to username" that ran sucessfully
> but still can't create a table in my DB.
> Can anyone tell me what I missed for this user? he can drop tables but
> not
> create them.
> Thanks,
> Joe
>|||i think the user is be a member of a denied group
or he is denied as an individual
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"jaylou" wrote:

> hi All,
> I have given a user ddladmin to a database, but this user still can't crea
te
> tables. I tried to "grant create tables to username" that ran sucessfully
> but still can't create a table in my DB.
> Can anyone tell me what I missed for this user? he can drop tables but no
t
> create them.
> Thanks,
> Joe
>|||Hi Hari,
Here is the error I get when I try to create a table with this userID
"Server: Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database 'SOXstuff'."
I don't need to add him to a server role as well as the DB_DDLADMIN?
Thanks again,
Joe
"Hari Prasad" wrote:

> Hi,
> Looks like some one DENY the create table previlage for that user. But in
> this case a GRANT command will over rule the DENY.
> Could you post the exact error you are getting after running the GRANT
> statement.
> Thanks
> Hari
> SQL Server MVP
>
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:8DF5770F-4D14-4ADE-A3F1-2A47AFB3AAD4@.microsoft.com...
>
>|||hi Jose,
Is there a way to find out if he is being denied? I have granted him
DB_DDLADMIN rights to the database. that should over ride the deny correct?
Thanks,
Joe
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
[vbcol=seagreen]
> i think the user is be a member of a denied group
> or he is denied as an individual
>
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "jaylou" wrote:
>|||If I try to create in EM. I get a message saying:
I am not logged on as databse owner or SA.
When I try to save it it tells me I dnt have create rights.
Do I need to be SA or owner to create a table? I didnt think I had to be.
"Hari Prasad" wrote:

> Hi,
> Looks like some one DENY the create table previlage for that user. But in
> this case a GRANT command will over rule the DENY.
> Could you post the exact error you are getting after running the GRANT
> statement.
> Thanks
> Hari
> SQL Server MVP
>
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:8DF5770F-4D14-4ADE-A3F1-2A47AFB3AAD4@.microsoft.com...
>
>|||Hi Hari
Which case are you talking about that GRANT will overrule a DENY?
Thanks
Kalen Delaney
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Od%237RfPrFHA.528@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Looks like some one DENY the create table previlage for that user. But in
> this case a GRANT command will over rule the DENY.
> Could you post the exact error you are getting after running the GRANT
> statement.
> Thanks
> Hari
> SQL Server MVP
>
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:8DF5770F-4D14-4ADE-A3F1-2A47AFB3AAD4@.microsoft.com...
>
>|||Kalen,
From my understanding Grant will override the exsisting deny as well as a
deny staement will override the exsisting Grant. I may be wrong since I can
t
seem to get my user create table rights in my DB without giving DB_Owner
rights.
"Kalen Delaney" wrote:

> Hi Hari
> Which case are you talking about that GRANT will overrule a DENY?
> Thanks
> Kalen Delaney
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:Od%237RfPrFHA.528@.TK2MSFTNGP09.phx.gbl...
>
>|||Don't think a grant overrides a deny. Deny takes precedence.
It almost sounds like you may be referring to revoke which
removes a previously granted or denied permission. Revoke
and Deny are very different.
-Sue
On Tue, 30 Aug 2005 13:19:07 -0700, jaylou
<jaylou@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Kalen,
>From my understanding Grant will override the exsisting deny as well as a
>deny staement will override the exsisting Grant. I may be wrong since I ca
nt
>seem to get my user create table rights in my DB without giving DB_Owner
>rights.
>"Kalen Delaney" wrote:
>

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.