Form认证的数据库表移植带来的问题

The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion'

 

USE aspnetdb(就是你创建form认证数据库的名称)

GO

sp_addrolemember 'aspnet_Membership_FullAccess', 'sql账户名'
go
sp_addrolemember 'aspnet_Personalization_FullAccess', 'sql账户名'
go
sp_addrolemember 'aspnet_Profile_FullAccess', 'sql账户名'
go
sp_addrolemember 'aspnet_Roles_FullAccess', 'sql账户名'
go
sp_addrolemember 'aspnet_WebEvent_FullAccess', 'sql账户名'
go
sp_addrolemember 'aspnet_Membership_FullAccess', 'sql账户名'
go

 

如果不能正常登录，有可能是数据库移动的时候携带的用户虽然到了新的server是一样的名字，但它的ID在新的数据库发生了变化，导致与aspnet的principal保存的不一样，请采用以下方法删除该用户：

Fix for 'The database principal owns a schema in the database, and cannot be dropped' Error in SQL Server 2005

I was trying to restore one database backup; which I successfully did (I'm using SQL Server 2005), and want to delete the users of the database which are restored along with the DB; I received this error which says "The database principal owns a schema in the database, and cannot be dropped".
While I googled the error, I found that the user which i want to delete is called "Database Principal" is having "DB_Owner" Role selected. That means the Database Pricipal I want to delete owns the Schema. One solution was to delete the Schema and then delete the user, :-) which I am obviously not going to do. Another Solution was to change the DB_Owner to some another "Database Principal"; let say to "dbo". I did it accordingly and then I was able to delete the user/Database Pricipal successfully.

To change the DB_Owner to some another "Database Principal"; simply Drill Down to your Database in Sql Server Management Studio and further more Drill Down to your_DB_Name > Schemas > db_owner > right click > select properties. You would find the name of the "Database Principal" that you want to delete. Change this to some another "Database Principal"; for Example, to "dbo".
You need to make the same changes to all the schemas where the "Database Principal" is having/owning the role. For Example, if it is having "db_owner, db_datareader, db_datawriter" roles; then you have to make the above said changes for all these 3 schemas.

 

然后执行

sp_dropuser '用户名'



运行成功后，再重建用户就可以重新登录了。