Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
interclubs
Yak Posting Veteran
63 Posts |
 Posted - 2002-04-30 : 11:55:54
|
| I had to move a database in a big hurry, so I detached it, then reattached it to the new server. All seems fine, but the user that is used to login to the DB, needs to have full access to every single roll. If I just give it db-owner and public access it can't do anything in the database, and keeps giving me the error that it cannot create account context or something. Any help would be greatly appreciated. Thanks! |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-04-30 : 12:50:50
|
| Run the following in the new database and see if it helps:EXEC sp_change_users_login 'auto_fix', 'YourUserNameHere'GOFrom the above command replace 'YourUserNameHere' with the user name in question.If that doesn't help, post back the actual error messages you are getting.--HTH,VyasCheck out my SQL Server site @http://vyaskn.tripod.com |
 |
|
|
interclubs
Yak Posting Veteran
63 Posts |
Posted - 2002-04-30 : 14:04:41
|
| I think that may have done the trick. Quick dumb question..What Role should a login participate in for your typical user (is used as login for web users, so they can add/edit/enter info to DB).Should it be public,or public and db_owner.Thanks again! |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-04-30 : 15:00:49
|
| Our basic security model for web logins to SQL is to have a single SQL Login that the ASP pages use to connect to SQL server and grant it only SELECT permissions on tables (because we do some dynamic SQL and it is required) and EXECUTE permissions on stored procedures.We never grant db_owner to a login that will be used from the web. In fact, our production SQL Server is locked down so that we only have sysadmins and restricted web logins. |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-04-30 : 16:01:45
|
quote:
I think that may have done the trick. Quick dumb question..What Role should a login participate in for your typical user (is used as login for web users, so they can add/edit/enter info to DB).Should it be public,or public and db_owner.Thanks again!
In my environment the login and user don't belong to any roles. I just grant EXEC permissions on the required stored procedures. Everything happens through stored procs and we avoid dynamic SQL as much as possible--HTH,VyasCheck out my SQL Server site @http://vyaskn.tripod.com |
|
|
|
|
|
|
|
|
I screwed up big time