Creating a View from ASP.Net page (Problems)

Hello,

I am having an error when using the CREATE VIEW statement and trying to execute it from an ASP.net page. I get an error that says something along the lines of 'dbo' schema does not exist or you do not have permissions to access it. I have checked and the user has been granted every permission I can think of (including setting it to the DB owner), but it does not work.

I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.

Here is an example (or as close as I can remember at this point) of some code I tried:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Create View TestView as Select 1, 2"
myCommand.ExecuteNonQuery()
myCommand.Close()

Any help would be appreciated.

Thanks,
StuporMan

I dont think that is a valid SQL statement.|||

You are correct, but it can be made correct by changing the statement to:

"Create View TestView as Select 1 as test1, 2 as test2"

(you can verify this by running in SQL Server management studio).

Andthis does not change the ASP.net error message.

You can do thefollowing test to show what I mean.

Create an ASP.net page with 2buttons.

One button click runs this code:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Select 1 as test1, 2 as test2"
myCommand.ExecuteNonQuery()
myCommand.Close()

(I have left the definitions and SQL connection string up to you)

On the other button click run this:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Create View TestView as Select 1 as test1, 2 as test2"
myCommand.ExecuteNonQuery()
myCommand.Close()

And you should produce the same error message as I am getting.

Thanks,

StuporMan

|||

StuporMan:

I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.

I've tried to run your command in SQL Server Management Studio:

Create View TestView as Select 1, 2

and it generates this error message:

Msg 4511, Level 16, State 1, Procedure TestView, Line 1
Create View or Function failed because no column name was specified for column 1.

|||

Ok, I had mistyped the SQL I was using as I had to do it from memoryat the time. Here is a valid Create View statement that produces thesame error in ASP.net:

"Create View TestView as Select 1 as test1, 2 as test2"

This should run in SQL Management studio. If it does not try"Create View TestView as Select * from <insert table name here>",which should also be valid. In either case, ASP.net should produce thesame error message.

Thanks,

StuporMan

|||

I am still having problems even after correcting the SQL string.Tryany valid CREATE VIEW statement from ASP.net and see if it will work. Ihave not been able to get any to work to date.

|||Presumably whatever context your web application is under doesn't have the needed security. Are you sure you elevated the permissions of the correct account? (Just guessing here, as I've never tried creating views from a web application myself.)|||

Thanks for the response, you prompted me to again check mypermissions. The user I was logging into the SQL server with DIDhave the correct permission set. The ASPNET user did not. Giving ASPNETthe permission did the trick.

Thanks,

StuporMan

Creating a user in SQLExpress with SQL Server Management Studio Express

I have created a user in SQL Server Management Studio Express. However,
whenever my ASP.NET application tries to connect I get the error
Cannot open database "ABC" requested by the login. The login failed. Login
failed for user 'Fred'.

If I change my login string to Integrated Security=SSPI it works. Also, I
was able to add a user successfully in SQL 2000 but SQLExpress is differnt.

Can anyone tell me how to add a user and give him permissions for a
particular database? Or refer me to a URL that does.

Many thanks.

In SQL Server Management Studio Express, when you login to the SQL Express Server, you'll see "Databases" as an item in the Object Explorer. If you expand that, you'll see all of your databases listed. Select the database you want and expand that, then you'll see Security as one of the items. Expand that one more time and you'll see Users. You can create a new user there, as well as right click on a user and select Properties to fine tune the permissions of that user.

You can also create global users (Logins) by going to the Security item at the top level list in the Object Explorer. Once you've created a Login, you can map it to certain users within a database.

Thanks< MJ

Creating a user in SQLExpress with SQL Server Management Studio Express

I have created a user in SQL Server Management Studio Express. However,
whenever my ASP.NET application tries to connect I get the error
Cannot open database "ABC" requested by the login. The login failed. Login
failed for user 'Fred'.
If I change my login string to Integrated Security=SSPI it works. Also, I
was able to add a user successfully in SQL 2000 but SQLExpress is differnt.
Can anyone tell me how to add a user and give him permissions for a
particular database? Or refer me to a URL that does.
Many thanks.
hi Andrew,
Andrew Chalk wrote:
> I have created a user in SQL Server Management Studio Express.
> However, whenever my ASP.NET application tries to connect I get the
> error Cannot open database "ABC" requested by the login. The login failed.
> Login failed for user 'Fred'.
> If I change my login string to Integrated Security=SSPI it works.
> Also, I was able to add a user successfully in SQL 2000 but
> SQLExpress is differnt.
> Can anyone tell me how to add a user and give him permissions for a
> particular database? Or refer me to a URL that does.
> Many thanks.
please verify your SQLExpress instance accepts standard SQL Server logins,
enabling mixed security.. your connection string changed to ...Integrated
Security=SSPI implies a trusted connection...
SQL Server uses a so called "2 phases" authentication policy:
first an SQL Server Login or a Windows login must be created of granted
access to the SQL Server instance... at the server level a login can be made
member of none, 1 or all of the fixed server roles, which include "sysadmin"
role and so on...
you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires user's
credential becouse it's directly provided by Windows via the logins'ID
(sid), which authenticate user's login at the windows login step... SQL
Server only needs to verify that the corresponding login and/or group is
granted to log on the instance...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to...
CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
GRANT {database permission}
http://msdn2.microsoft.com/en-us/library/ms178569.aspx
GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea: Thanks you for your detailed reply.
I understand the concptual difference between Windows and SQL authentication
and thought I had done all the necessary steps, which is why I am puzzled.
As I mentioned, with SQL 2000 I used SQL Enterprise manager and have the
login working. I have tried to map the steps I took in SQL2000's SQL EM to
those in the new SQL Server Mgt. Studio Express.
1) I created a login under the server->security folder.
I did not give this Login any Roles. It is just a plain ol' user (public).
2) I went to the server instance name at the top and GRANTed "Connect SQL"
permission to the new Login just created;
3) I went to the database and granted "Connect" permission to the Login.
After these steps I get the error reported in my earlier post.
I should add that I am not an SQL Server expert and first looked at the SQL
Server Mgt. Studio Express BOL.They are absolutely useless.
Hope the above hels.
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40acn2F19erhvU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> please verify your SQLExpress instance accepts standard SQL Server logins,
> enabling mixed security.. your connection string changed to ...Integrated
> Security=SSPI implies a trusted connection...
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created of granted
> access to the SQL Server instance... at the server level a login can be
> made
> member of none, 1 or all of the fixed server roles, which include
> "sysadmin"
> role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires user's
> credential becouse it's directly provided by Windows via the logins'ID
> (sid), which authenticate user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||I don't understand the following stement:
> database access, but a (database) user must be mapped to the corresponding
> login..
what is the difference between a Login and a User?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40acn2F19erhvU1@.individual.net...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Andrew,
Andrew Chalk wrote:
> Andrea: Thanks you for your detailed reply.
> 1) I created a login under the server->security folder.
> I did not give this Login any Roles. It is just a plain ol' user
> (public).
it's not a user, it's a login and there's no "public" server role... it's
just a login which is not member of server roles...

> 2) I went to the server instance name at the top and GRANTed "Connect
> SQL" permission to the new Login just created;
yep... the login has been created, enabled, and granted access to the
instance..

> 3) I went to the database and granted "Connect" permission to the
> Login.
ok, you granted db access to the login, which is now member of th public
database role..

> After these steps I get the error reported in my earlier post.
I reproduced your steps and I can log on to SQLExpress as a standard SQL
Server Login, and access a db the login has been mapped to a corresponding
user.. I did it via SSMSExpress and not via ASP..

> I should add that I am not an SQL Server expert and first looked at
> the SQL Server Mgt. Studio Express BOL.They are absolutely useless.
:D
download the full BOL, december released..

> what is the difference between a Login and a User?
a Login is the very first part of the 2 phases security policy enforcement
of SQL Server... I repeat what I already wrote..
SQL Server uses a so called "2 phases" authentication policy:
first an SQL Server Login or a Windows login must be created in order to
grant access to the SQL Server instance... at the server level a login can
be made member of none, 1 or all of the fixed server roles, which include
"sysadmin" role and so on...
you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires credential
becouse it's directly provided by Windows via the logins' ID (sid), which
authenticate (Windows) user's login at the windows login step... SQL Server
only needs to verify that the corresponding login and/or group is granted to
log on the instance (checking the granted Login's existence in the SQL
Server instance)...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
The phrase:
> the login has been mapped to a corresponding user..
Can you explain that.
How did it happen?
Who is the user?
Is it correct to say that the user is an instance of utilizing a login? I.e.
"users" have logins. Some may use the same logins as others (for example
several instances of an application). In which case each application trying
to use SQL server is a user and their name/password pair correspond to their
login?
Thanks.
|||I now have a role named 'fred' and a user named 'fred' of the database that
I want the application to access. Is the use of the same name the problem?
Thanks
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
> :D
> download the full BOL, december released..
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||OK, fixed it, though mainly through luck than knowhow.
Deleted user "fred" and login "fred".
Recreated login Fred but made sure that I went to 'User Mapping' and checked
the database that I wanted this Login to be able to access;
Login was created and, like magic, a user appeared, also named 'fred', under
the database.
I still don't understand this.
Nonetheless, thanks for your help,
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
> :D
> download the full BOL, december released..
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Andrew,
Andrew Chalk wrote:
> The phrase:
> Can you explain that.
> How did it happen?
> Who is the user?
perhaps my english is not as good as I thought.. :D
perhaps here,
http://msdn.microsoft.com/library/de...rity_4fol.asp,
the architectural design is more clear..
a SQL Server Login (both standard SQL Server login or a WinNT login) is just
the very first brick in the wall in order to access a SQL Server instance...
without that you can not log in and connect to a specified instance... it
has a server wide range...
a User is just a database user (can have the same name as the corresponding
Login [Login is now somehaw obsolete, to be replaced by principals] but this
is not mandatory even if it's recommended in order to minimize troubles :D)
that, at creation time, is mapped via it's sid column
(dbname.sys.sysusers.sid) to an existing login
(master.sys.server_principals.sid)
SELECT u.name AS [Name in DB], u.hasdbaccess ,
p.name AS [Login Name]
FROM master.sys.server_principals p JOIN sys.sysusers u
ON p.sid = u.sid
Principals
http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
DB Users
http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx

> Is it correct to say that the user is an instance of utilizing a
> login? I.e. "users" have logins.
Logins can be granted db access as database users, but they can even not..

>Some may use the same logins as
> others (for example several instances of an application). In which
> case each application trying to use SQL server is a user and their
> name/password pair correspond to their login?
an application is not a user or a login... in certain case an application
role can represent this scenario
(http://msdn2.microsoft.com/en-us/library/ms190998.aspx)

> Deleted user "fred" and login "fred".
> Recreated login Fred but made sure that I went to 'User Mapping' and
> checked the database that I wanted this Login to be able to access;
> Login was created and, like magic, a user appeared, also named
> 'fred', under the database.
it's correct... you created a login... then you granted that login db access
via a database user (in the defined database) mapping it to the original
login ...
if you only create a login without grating him/her access to your desired
database(s), that login will only access it if the predefined "guest"
database user is available (and usually that db user is not available for
security reason)...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||That URL does not use the work 'Login', only 'User'.
What is the difference between a login and a user?
Thanks,
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40dk3lF198jigU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> perhaps my english is not as good as I thought.. :D
> perhaps here,
> http://msdn.microsoft.com/library/de...rity_4fol.asp,
> the architectural design is more clear..
> a SQL Server Login (both standard SQL Server login or a WinNT login) is
> just the very first brick in the wall in order to access a SQL Server
> instance... without that you can not log in and connect to a specified
> instance... it has a server wide range...
> a User is just a database user (can have the same name as the
> corresponding Login [Login is now somehaw obsolete, to be replaced by
> principals] but this is not mandatory even if it's recommended in order to
> minimize troubles :D) that, at creation time, is mapped via it's sid
> column (dbname.sys.sysusers.sid) to an existing login
> (master.sys.server_principals.sid)
> SELECT u.name AS [Name in DB], u.hasdbaccess ,
> p.name AS [Login Name]
> FROM master.sys.server_principals p JOIN sys.sysusers u
> ON p.sid = u.sid
> Principals
> http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
> DB Users
> http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx
>
> Logins can be granted db access as database users, but they can even not..
>
> an application is not a user or a login... in certain case an application
> role can represent this scenario
> (http://msdn2.microsoft.com/en-us/library/ms190998.aspx)
>
> it's correct... you created a login... then you granted that login db
> access via a database user (in the defined database) mapping it to the
> original login ...
> if you only create a login without grating him/her access to your desired
> database(s), that login will only access it if the predefined "guest"
> database user is available (and usually that db user is not available for
> security reason)...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql

Creating a row guid in SQL Express

I am an asp developer who is finally serious about learning .net. I have downloaded SQL Express and am working through the ADO Step by Step book. I can easily create a table and set all the columns. What I cannot do is create a row guid. The option for this is grayed out in the column properties window and when I try to set a field in the "Row Guid Column" in the right side properties window I get an error mesage stating the field must match an entry in the list.

I have set the id field to primary key and no nulls and to identity and I still cannot set as row guid. I even downloaded Northwind and these tables also do not have a rowguid and I cannot set.

I noticed this as I tried to create a data adapter. I was able to create the Select and Insert statements but the update and delete failed I think due to the rowguid issue. Any help is appreciated Thanks Brad

Hi Brad...in order to set the rowguid column attribute, the column must be of the datatype 'uniqueidentifier'...once you have a column of that datatype, you can then assign the column the rowguid attribute. Note that you can only have a single rowguid column in a single table, though you can have multiple columns of the datatype uniqueidentifier.

HTH

|||Thanks Chad I have been working with MySql the last 3 years and was not used to the uniqueidentifier column. I now know and have fixed my db.

creating a folder on the server

Hi,
I have a script that runs on an IIS webserver. The webserver is on a
different domain as the MSSQL server. I have an asp page that uses DSNless
connection to connect to the SQL server and execute stored procedures to
create a database locally. Everything works fine and both the MDF and LOG
files get created. The problem is that I want the files to be created in a
new folder everytime with name passed to it by the asp page.
How can i do this, i can't create it through the asp page because the IIS
server cannot access the MSSQL's servers harddrive.
Is there a way to create a folder locally ?
I was thinking of using a batch file and execute that, but don't know how to
call that.
Any help would be appreciated
Thanx
You can use xp_cmdshell to run internal commands such as MD, bat files and exe files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"robert" <rob@.ms.com> wrote in message news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a script that runs on an IIS webserver. The webserver is on a
> different domain as the MSSQL server. I have an asp page that uses DSNless
> connection to connect to the SQL server and execute stored procedures to
> create a database locally. Everything works fine and both the MDF and LOG
> files get created. The problem is that I want the files to be created in a
> new folder everytime with name passed to it by the asp page.
> How can i do this, i can't create it through the asp page because the IIS
> server cannot access the MSSQL's servers harddrive.
> Is there a way to create a folder locally ?
> I was thinking of using a batch file and execute that, but don't know how to
> call that.
> Any help would be appreciated
> Thanx
>
|||Another alternative is to do this as an ActiveX script task inside a DTS
package, which can be kicked off from your ASP page.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"robert" <rob@.ms.com> wrote in message
news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a script that runs on an IIS webserver. The webserver is on a
different domain as the MSSQL server. I have an asp page that uses DSNless
connection to connect to the SQL server and execute stored procedures to
create a database locally. Everything works fine and both the MDF and LOG
files get created. The problem is that I want the files to be created in a
new folder everytime with name passed to it by the asp page.
How can i do this, i can't create it through the asp page because the IIS
server cannot access the MSSQL's servers harddrive.
Is there a way to create a folder locally ?
I was thinking of using a batch file and execute that, but don't know how to
call that.
Any help would be appreciated
Thanx
|||Thanks will try it out.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ekf8gz6rFHA.464@.TK2MSFTNGP15.phx.gbl...
> Another alternative is to do this as an ActiveX script task inside a DTS
> package, which can be kicked off from your ASP page.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "robert" <rob@.ms.com> wrote in message
> news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a script that runs on an IIS webserver. The webserver is on a
> different domain as the MSSQL server. I have an asp page that uses DSNless
> connection to connect to the SQL server and execute stored procedures to
> create a database locally. Everything works fine and both the MDF and LOG
> files get created. The problem is that I want the files to be created in a
> new folder everytime with name passed to it by the asp page.
> How can i do this, i can't create it through the asp page because the IIS
> server cannot access the MSSQL's servers harddrive.
> Is there a way to create a folder locally ?
> I was thinking of using a batch file and execute that, but don't know how
> to
> call that.
> Any help would be appreciated
> Thanx
>

Creating a database with a GUI

Is there any tools to create a database with tables, views, and all that jazz with a GUI. I went through the exammle on beta.asp.net but the way they created their db was with a script. I want to create everything with a GUI app. If this gui app doesn't exist can someone point me to where I can find more info about creating db, tables etc with sql server 2005 express? Thanks in advance.
-Daticus

Try the link below and download the Express Manager. Hope this helps.
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx#EBAA|||I think this is what your looking for...
Andrea Montanari
andrea.sql@.virgilio.it
http://www.asql.biz/DbaMgr.shtm
You can also use an Access Project to build an SQL database graphically
Good Luck
Simon

Creating a Database using ASP.NET

Hi All,

Can anybody help me/does anybody have the code to create a database on ms sql server 2000 using asp.net?

This needs to be done without being asked for the sql server login credentials (i.e the sa username/password can be inserted in the code)Using Microsoft Data Access Application Blocks, I would do the following (C#):
SQLHelper.ExecutNonQuery("Connection string", CommandType.Text, "Create Database...");
That's a simple brute force way to do it, you'll obviously need to play and tweak it, but hopefully that will get you started.

http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProvider
is a link to where the connection strings for ADO.NET are.

HTH|||Thanks for the pointer.

Not being much of a .net coder, do you know of any complete solutions (web based database administration tools) or even better, do you know anybody that would be able to create the script for me...i'll pay of course!|||http://weblogs.asp.net/ashben/archive/2003/09/28/29469.aspx is a link to some web based datbase administration tools. Let us know if that is what you need or if you actually need someone to do this for you.|||Hi ericlandes,

Thanks for the link. I do still require someone to code an asp.net page for me to create databases.

* The page must be available to anonymous users (anyone can create a database) - So maybe the 'sa' user credentials are coded into the asp.net form?

* The form will allow the user to specify a database name to create and create an sql username/password with permissions to that database

* When the database is created, it will have certain options set (i.e maximum database size of 2mb)|||So will each person not only create the database, but create different fields, with different types, etc, or will each person basically generate a new 'template' database, all of them having the same fields/datatypes, with the only difference being the name of the database, it's username & password?|||The basic concept is:

User visits page, enters the name of the database they want to create, and also picks a username/password to create which will have access this new database.

Basic checks will need to be made to ensure that the database and username do not already exist.

The database will be created totaly blank, with the maximum size of the database set to 2mb|||anyone got any ideas?|||Hello xmsms,

It sounds to me like you are creating a web-hosting environment with data-access.

Even if this is not your intended useage, I'll bet that if you look for hosting tools for SQL-Server then you will be on the right track to finding the tool, or the right code for your needs.

Did you ever find your coder to do the job for you? If not and you are still stuck, I will be able to look into your question further after June 20th.

creating a database on the server

Is there a way to create a brand new database on the server?
Someone gave me the asp code below, but I haven't gotten it to work yet.
Thanks for any help.
<%
Dim strSQL
Dim cnnTest
strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
strSQL = strSQL & "(" & vbCrLf
strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
strSQL = strSQL & ")" & vbCrLf
Set cnnTest = Server.CreateObject("ADODB.Connection")
response.write "got to here"
cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
& "Initial Catalog=test;User Id=sa;Password=;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
response.write "are we heare"
cnnTest.Execute strSQL
cnnTest.Close
Set cnnTest = Nothing
%>
that creates a Table, not a new DB...which is it you are after? Also, why
are you posting it to these groups? some .Net, some not, some SQL... ?
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Is there a way to create a brand new database on the server?
> Someone gave me the asp code below, but I haven't gotten it to work yet.
> Thanks for any help.
> <%
> Dim strSQL
> Dim cnnTest
> strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
> strSQL = strSQL & "(" & vbCrLf
> strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
> strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
> strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
> strSQL = strSQL & ")" & vbCrLf
> Set cnnTest = Server.CreateObject("ADODB.Connection")
> response.write "got to here"
> cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
> & "Initial Catalog=test;User Id=sa;Password=;" _
> & "Connect Timeout=15;Network Library=dbmssocn;"
> response.write "are we heare"
> cnnTest.Execute strSQL
> cnnTest.Close
> Set cnnTest = Nothing
> %>
>
>
|||Hello,
Thanks for your help. I just wanted to create the actual database on the
server, then create a table within the database. I realize the sample code
I sent is probably not the right code.
I posted to several groups I thought seemed related to this question. Maybe
I picked the wrong ones.
Thanks.
Scott Baxter
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
> that creates a Table, not a new DB...which is it you are after? Also, why
> are you posting it to these groups? some .Net, some not, some SQL... ?
> --
> Curt Christianson
> Owner/Lead Developer, DF-Software
> Site: http://www.Darkfalz.com
> Blog: http://blog.Darkfalz.com
>
> "Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
> news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
>
|||You need to create a script, sql statement that does
something like:
create database YourDB
go
use YourDB
go
create table YourTable
(col1 int not null)
-Sue
On Mon, 24 May 2004 11:12:28 -0700, "Scott Baxter"
<sbaxter@.websearchstore.com> wrote:

>Hello,
>Thanks for your help. I just wanted to create the actual database on the
>server, then create a table within the database. I realize the sample code
>I sent is probably not the right code.
>I posted to several groups I thought seemed related to this question. Maybe
>I picked the wrong ones.
>Thanks.
>Scott Baxter
>"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
>news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
>

creating a database on the server

Is there a way to create a brand new database on the server?
Someone gave me the asp code below, but I haven't gotten it to work yet.
Thanks for any help.
<%
Dim strSQL
Dim cnnTest
strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
strSQL = strSQL & "(" & vbCrLf
strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
strSQL = strSQL & ")" & vbCrLf
Set cnnTest = Server.CreateObject("ADODB.Connection")
response.write "got to here"
cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
& "Initial Catalog=test;User Id=sa;Password=;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
response.write "are we heare"
cnnTest.Execute strSQL
cnnTest.Close
Set cnnTest = Nothing
%>that creates a Table, not a new DB...which is it you are after? Also, why
are you posting it to these groups? some .Net, some not, some SQL... '
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Is there a way to create a brand new database on the server?
> Someone gave me the asp code below, but I haven't gotten it to work yet.
> Thanks for any help.
> <%
> Dim strSQL
> Dim cnnTest
> strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
> strSQL = strSQL & "(" & vbCrLf
> strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
> strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
> strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
> strSQL = strSQL & ")" & vbCrLf
> Set cnnTest = Server.CreateObject("ADODB.Connection")
> response.write "got to here"
> cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
> & "Initial Catalog=test;User Id=sa;Password=;" _
> & "Connect Timeout=15;Network Library=dbmssocn;"
> response.write "are we heare"
> cnnTest.Execute strSQL
> cnnTest.Close
> Set cnnTest = Nothing
> %>
>
>|||Hello,
Thanks for your help. I just wanted to create the actual database on the
server, then create a table within the database. I realize the sample code
I sent is probably not the right code.
I posted to several groups I thought seemed related to this question. Maybe
I picked the wrong ones.
Thanks.
Scott Baxter
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
> that creates a Table, not a new DB...which is it you are after? Also, why
> are you posting it to these groups? some .Net, some not, some SQL... '
> --
> Curt Christianson
> Owner/Lead Developer, DF-Software
> Site: http://www.Darkfalz.com
> Blog: http://blog.Darkfalz.com
>
> "Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
> news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
>|||You need to create a script, sql statement that does
something like:
create database YourDB
go
use YourDB
go
create table YourTable
(col1 int not null)
-Sue
On Mon, 24 May 2004 11:12:28 -0700, "Scott Baxter"
<sbaxter@.websearchstore.com> wrote:

>Hello,
>Thanks for your help. I just wanted to create the actual database on the
>server, then create a table within the database. I realize the sample code
>I sent is probably not the right code.
>I posted to several groups I thought seemed related to this question. Mayb
e
>I picked the wrong ones.
>Thanks.
>Scott Baxter
>"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
>news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
>

creating / changing owner problem

I'm trying to create a new database and new login to allow a client
(through my asp.net page) to create their own database, username and
password. I've tried using the stored procedures I've found in BOL,
but I can't get it to work right. It keeps saying that I can't assign
this username as the db owner since it's already a user for the
database. I then read other posts about reassigning the db owner to
another dummy account and then trying to reassign it to the new one,
but that isn't working either. Can anyone look at my code and tell me
what I'm doing wrong? Thanks.

//create db login
qry = "sp_addlogin '" + username + "', '" + password + "', '" + dbname
+ "'";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();

//grant login access to new database to new owner
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_grantdbaccess '" + username + "'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;

//connect to new database under sa account and change owner to new
account
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_changedbowner
'HolderUserDoNotDelete'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.CommandText = "sp_changedbowner '" + username + "'";
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;geoff (cakewalkr7@.hotmail.com) writes:
> I'm trying to create a new database and new login to allow a client
> (through my asp.net page) to create their own database, username and
> password. I've tried using the stored procedures I've found in BOL,
> but I can't get it to work right. It keeps saying that I can't assign
> this username as the db owner since it's already a user for the
> database. I then read other posts about reassigning the db owner to
> another dummy account and then trying to reassign it to the new one,
> but that isn't working either. Can anyone look at my code and tell me
> what I'm doing wrong? Thanks.

If you intend to make the login owner of the database, there is no
reason to create a user for him in the database, but just skip that
step. Once the login owns the database, he also has a user in it.

Note: A login in SQL Server is server-wide. A user is something in a
database, which can be connected to a login. The username and loginname
may be the same, but they don't have to.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp