modifying user security

[geoff]

I have an asp page that currently is creating a database and a user
login for that database. After everything successfully (I thought)
executed, I tried to change my connection properties for the server
and then login as this new user. It wouldn't allow me to, so I logged
back in as the administrator and looked at the properties for the new
login. On the general tab, it had the user's default database
specified as the new database that I had created in the asp page, but
when I went to the database access tab, the database was not selected.
So, I'm not sure how to set that in my script. I've done some
searching in BOL, but I can't figure it out. Also, if there's a way
to do this in a query, or stored procedure, will it also specify what
type of role the user has (public, db_owner, etc.)? Thanks.

[Simon Hayes]

ca********@hotmail.com (geoff) wrote in message news:<bf**************************@posting.google. com>...

I have an asp page that currently is creating a database and a user
login for that database. After everything successfully (I thought)
executed, I tried to change my connection properties for the server
and then login as this new user. It wouldn't allow me to, so I logged
back in as the administrator and looked at the properties for the new
login. On the general tab, it had the user's default database
specified as the new database that I had created in the asp page, but
when I went to the database access tab, the database was not selected.
So, I'm not sure how to set that in my script. I've done some
searching in BOL, but I can't figure it out. Also, if there's a way
to do this in a query, or stored procedure, will it also specify what
type of role the user has (public, db_owner, etc.)? Thanks.

You need to be clear about the difference between a login (allows
access to the server), and a user (allows access to a database). I'm
not sure that I followed your description, but it sounds as if you
have created a new login, however that login has not been granted
access to the database.

In TSQL, you can use sp_grantdbaccess to give the login access to a
database, or you can use the SQLDMO object model from VB or whatever.
Note that granting a login access to a database creates a user, but
doesn't add the user to any roles (eg. db_owner), except for the
public role (all users are in the public role). So you may be looking
for something like this (see BOL for more details of the syntax):

use database NewDB
go
exec sp_grantdbaccess 'NewLogin', 'NewLogin'
exec sp_addrolemember 'db_owner', 'NewLogin'
go

Simon

[geoff tyler]

Thanks Simon, I had found the sp_grantdbaccess stored procedure, but
yes, I was looking for the sp_addrolemember sp also. That all works
fine now. Thanks for your help. Now, another question. Since my UI
lets a user create a database, I also have it set up so that they can
delete a database. When I test it, sometimes it will delete just fine,
but other times, when I run the drop database sp, it tells me it can't
delete the database because it's currently in use. I'm not quite sure
how it's in use since I just created it and there's no way anyone is
connected to it. But, regardless, is it possible to programmatically
shut down the database and then delete it so that the user does not
encounter this error? Thanks again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"geoff tyler" <ca********@hotmail.com> wrote in message
news:40*********************@news.frii.net...

Thanks Simon, I had found the sp_grantdbaccess stored procedure, but
yes, I was looking for the sp_addrolemember sp also. That all works
fine now. Thanks for your help. Now, another question. Since my UI
lets a user create a database, I also have it set up so that they can
delete a database. When I test it, sometimes it will delete just fine,
but other times, when I run the drop database sp, it tells me it can't
delete the database because it's currently in use. I'm not quite sure
how it's in use since I just created it and there's no way anyone is
connected to it. But, regardless, is it possible to programmatically
shut down the database and then delete it so that the user does not
encounter this error? Thanks again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

That depends who is connected - you can use sp_who to find out who is using
the database. Make sure you set your connection's current database context
to master before trying to drop the database, as that avoids the situation
where you are blocking yourself. Apart from that, you can use this command
to 'kick out' other users before dropping it:

alter database MyDB set offline with rollback immediate

Simon

[geoff tyler]

Thanks Simon, but I get an error stating incorrect syntax near the
keyword 'set'. I've done some searching to try to figure it out, but
everything I've come across has the same syntax as what you provided.
Any ideas what I should check? Here's the actual command I was
executing....

alter database newtestdatabase set offline with rollback immediate

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[geoff tyler]

Nevermind, I found a stored procedure to use to take it offline and it
worked great. Thanks for the idea.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!