trouble migrating a SQLExpress DB to SQL 2005

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

trouble migrating a SQLExpress DB to SQL 2005

Author

Topic

LesBerg
Starting Member

1 Post

Posted - 2009-12-21 : 16:13:19

I have a website project in development on Visual Studio 2008 and I can't migrate the project to any other computer. When I attach the database to my 'production' SQL Server 2005, users can log in the application and authenticate against the integrated aspnet users & roles tables, however; any further calls to the database throw the following error:

The SELECT permission was denied on the object 'D_FACULTY', database 'FACULTYINTAKE', schema 'dbo'.

The object in the error varies depending on what link a user tries to follow, but it always fails. This behavior also happens if I try to run the project on a second development computer running Visual Studio 2008.

I have compared every single permissions dialog between the development computer and the database/web server - at the server, database, and table levels, and I can not find any differences. Both are configured to use Windows and SQL Server authentication, both have the same logins and database users, both have the exact same list of Roles for the database and the same users and permission assigned to the roles. I even went so far as to use the following script to assign SELECT, INSERT, DELETE, and UPDATE permissions to the ServiceLearning user on all the tables (I found the script here: [url]http://www.tech-recipes.com/rx/2298/sql_server_2005_easily_grant_select_all_tables_views/[/url]).

USE [FACULTYINTAKE]
GO
DECLARE @login varchar(50)
SET @login = 'ServiceLearning'

DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))
INSERT INTO @tables
SELECT 'GRANT DELETE ON ' + NAME + ' TO '+@login
FROM sysobjects
WHERE TYPE = 'U'
AND NAME NOT LIKE 'SYNC%'

DECLARE @rowid int, @sqlstr varchar(500)
SET @rowid = 0
SET @sqlstr = ''
DECLARE grant_tbl_cursor CURSOR FOR
SELECT ROWID, SQLSTR
FROM @tables
ORDER BY ROWID
OPEN grant_tbl_cursor
FETCH NEXT FROM grant_tbl_cursor
INTO @rowid,@sqlstr
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (@sqlstr)
FETCH NEXT FROM grant_tbl_cursor
INTO @rowid,@sqlstr
END
CLOSE grant_tbl_cursor
DEALLOCATE grant_tbl_cursor

Any help would be grreatly appreciated. Is there a specific set of steps to follow to move a SQLExpress database onto a production server? the website in question is here: [url]http://216.161.142.211/

[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources