Migration Problems From SQL 2000 To SQL 2005: User Cannot Find The User 'RSExecRole'
Jan 25, 2008
Hello,
I am trying to migrate reporting services from SQL server 2000 to SQL 2005. I followed the migration steps listed in one of the TechNet documents. Installed SQL 2005 and SP2.
Backed up existing SQL 2000 databases and restored on SQL 2005.
Went to Configuration tool and then to the Database setup. Connected to SQL 2005.
Clicked on the Upgrade button. The upgrade fails and I always get the following errors:
"The database version (C.0.6.54) does not match your reporting services installation. You must upgrade your Reporting services database"
Then I get this exception:
System.Data.SqlClient.SqlException: Cannot find the user 'RSExecRole', because it does not exist or you do not have permission.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
I do not understand why it is looking for user 'RSExecRole', which is a role.
I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
I'm getting a specific error when i try to run a .sql script for a Custom TableProfileProvider ,when i Parse the .sql file Commands execute perfectly. But when i actually "Execute" the .sql script i get the following error. It will produce the table but with "Errors" which dosn't do anyone any good to have errors in the table. I even used the Sql Server Surface Area Configuration to add a new Administrator with the name listed below (WYATT-PCWyatt), that didnt help either..Here is part of the Sample Scheme the problem lies in the [YOURMACHINENAMEASPNET] Server = WYATT-PCSQLEXPRESS Computer Name = Wyatt-PC OS: Windows Vista Home Premium =========================================================== use TableProfileProviders go -- --grants on ASP.NET stored procedures and tables used by the custom providers -- grant EXECUTE on dbo.aspnet_Applications_CreateApplication to [YOURMACHINENAMEASPNET] grant EXECUTE on dbo.aspnet_Users_CreateUser to [YOURMACHINENAMEASPNET] grant SELECT on dbo.aspnet_Users to [YOURMACHINENAMEASPNET] grant UPDATE on dbo.aspnet_Users(LastActivityDate) to [YOURMACHINENAMEASPNET] go --drop table dbo.ProfileTable_1 --go create table dbo.ProfileTable_1 ( UserId uniqueidentifier not null Primary Key, FirstName nvarchar(50) null, LastName nvarchar(50) null, Age int null, LastUpdatedDate datetime not null) go grant SELECT,INSERT,UPDATE,DELETE on dbo.ProfileTable_1 to [YOURMACHINENAMEASPNET] go ================================================================================ Msg 15151, Level 16, State 1, Line 5 Cannot find the user 'WYATT-PCWyattASPNET', because it does not exist or you do not have permission.
How do you handle user level security with SQL Server 2005?
Say I have an HR database.
In Active Directory I have two groups: Managers, Employees.
Now in this HR Database I want to setup permissions in such a way that Managers can see all employees under them (but not other managers) and the employees can only see themselves.
(I'd have various levels of management defined in a table somewhere, so that each employee has a manager ID that links to another employee so that the CEO would be manager of everyone by working down the chain).
What I'm trying to understand is the best way to handle the permissions.
I'm not entirely clear on how to deal with that.
Would I use user chaining to do that, I wouldn't need impersonation (that's just for instances where you want dynamic SQL and it won't execute with user chaining, correct?)
Anyway, just looking for some general direction on this (obviously I need to get a good book it would seem).
Would I create a stored procedure that runs with EXECUTE AS permissions so that I'd have a non-interactive login it uses that has table access then all the other users have permission to execute the sproc?
So that sproc runs, pulls back a SELECT * FROM tbl_HRINFO and using a WHERE constraint limits who is returned WHERE SupervisorID = CurrentLoggedInEmployeeID ?
Also: How can I determine who is logged in and running the procedure, would the sproc use the SELECT USER_NAME command to see who was running it?
As you can see, I'm working from square one on all of this. Not sure if my posting entirely made sense, but hopefully someone can get me pointed in the right direction, thanks!
I set up a test server with Win2003 R2 and SQL 2000 Standard Ed., just like our production server and copied a couple of smaller databases to it. I'm testing an upgrade to SQL 2005 Standard.
Went through the Upgrade Advisor. There were some minor issues that I resolved prior to the install. Ran the installation to upgrade (not migrate). Installation went fine. Installed SP2 from CD, as well. Again, no installation issues.
When finished, I found that the test databases couldn't be used. I checked and found only the default users in the Security -> Logins. However, the databases still had all the users listed. All of the added SQL and Windows Domain users/groups were no longer there. By the way, we have set the security on SQL 2000 to be both SQL Server and Windows Authentication Mode to accomodate an older application.
If I try to add the user in Security -> Logins and grant the rights/roles to the database, I get an error that the user already exists in the database. If I try to remove the user from the database, I get an error that they own a schema and can't be dropped.
I can resolve that, but I was wondering if this issue will occur when I attempt this on the production server. There will be a lot more work re-establishing the server/database logins and roles. Is there a way to avoid this issue during the upgrade?
Good day , Does anyone know how to moe / copy database users from sql server 2000 to sql server 2005. I ave been successful will everything except this in my migration. I can copy the database users themselves but not the users under the security section.
I am attempting to migrate an old third party app from 2000 to 2005 but I have a problem that I hope you can help me with.
To explain,
On 2000, in sysusers of the third party app db I have a username that contains a . This user has an associated server login (that uses SQL Server authentication) of the same name but without the .
so, the database user name is APP1 and the server login is APP1
I checked the SID's between the user and the login and they are the same which would suggest they are mapped to each other , however when I run sp_helplogins the login does not show as mapping to any user. The database user also has a different status, altuid and Isaliased value than any other user in sysusers but I confess to not knowing what the significance of this might be.
As far as I was aware you can't create a database user with a name that contains this invalid character so firstly I'm wondering how it got there. When I look at the databases access tab of the properties through enterprise manager of the APP1 server login, the APP1 user is not displayed. If the SID's between the login and the user match then shouldn't the APP1 user be displayed in the database access list? or have I got the wrong end of the stick here?
Despite all that, it isn't really a problem on 2000 because the app works ok. Now, to migrate it to 2005 I restored a full back up of the 2000 database and now the problem is that when I look in sys.database_principals the APP1 user is not there after I've restored it. It still shows in sysusers however. The main problem is that I cannot map it to a login using sp_change_users_login because I get the following error:
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'SQSUPD' is absent or invalid.
If anyone can tell me why this user does not show in sys.database_principals or more importantly, give me any pointers on how to resolve thie login mapping it would be much appreciated as I am now stuck!
I don't know if this is the right forum to post this question, but here it goes.
We have restored into sql 2005 the database backups made in sql 2000. We connect with trusted connection and application roles, and when trying to execute a transaction to another database (with the guest user), we get a permission error.
Does anyone know if, apart from restoring the databases, we should do something else to get the guest user working the same way as with sql 2000 in the restored databases?
Hi, I wanted to know How can i find out that with which user i am logged in. Is there any command to find out the same. ( like in oracle, we have command -- showuser, which will return the logged in user of your session).
One of them literally has hundreds of sql logins, obviously i need to clean these up, however i a not sure how to determine when a user last logged in ?
How can i find out the last login date of a user ?
I am using SQL 6.5 and I would like to know (using Isql/w) the number of users connected to my server at any given time. Thanks for your help in advance.
I need to find the one day old(in last 24hrs) user details:
Below script i am using , but its not working as expected.
select name,createdate,* from syslogins where isntgroup=0 and isntname=1 and name not LIKE ('%sa-%') and name not LIKE ('%NT %') and createdate > DATEADD(hh,-24,GETDATE())
how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.
I have 2 users that their client software must be going crazy.. theyare sending packets every .02 seconds to the db server... I know thisbecause I stuck a sniffer on teh traffic.. but now i just need to knowwhat user is doing this (all traffic is encrypted.. so i couldn’tsniff out that.. i could only get an IP).any ideas?--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict232446.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=805276
Hi there,Is there any way to find the person or user who did update transactionmanuallyin query analyser and not through application in certain time?As i have check thru log file but cannot find anything.Any command or software to check it?Thanks for the help!
Our system administrator set up an NT server group in order to allowour users to login to our application via https to our sql server.The group appears as a User in SQL Server when you look at it inEnterprise Manager. That said, I can not see the users associatedwith the group from Enterprise Manager, but know they can login to thedatabase.The problem is this. When we login via the web we get access to thedatabase without problem, but when you look at the current_user whatyou see is the login Name the user entered and NOT the name of thegroup/User. That is to say, I can see a UserID which is not listed asa User in SQL Server and can't see the name of the group, which islisted as a user in SQL Server.I need to know who's logging in order to direct them to theappropriate web page via their role. Before the admin set up thegroup, I was using sp_helpuser to get the role, but then again I hadthe userID to do this.The question I have now, is there any way to see what thegroup/user is who logged in i.e. the goup listed as the User inEnterprise Manager? Otherwise I have to build a table of userIDs andtheir group/User name, which seems to defeat the purpose of having heserver authenticate users.Thanks,Tom
Earlier one of my team member has created a user login and password but forgot the password after few days and now we need to know the password of that login. Some of the application are using this login so we can delete and create a new login with the same name hence is there any possibility or script to find out the password of the existing login.
Hi, my first question, tried to use search first but couldnät find what I need.
Hopefully someone has asuggestion.
I work for a ISV producing HR applications.
When user starts the applications the program shows all databases that the user have access to. This is done by select all names from master..sysdatabases and then trying to "USE dbname" to see if user have access,. Users doesn't have sa rights.
This procedure takes approximately 90 secs for a server with 500 databases and that has become a problem.
Does anyone know off a faster method, any suggestions?
When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
I have a database created in server 2000, and now I have moved it to server 2005.
All works do fine, but there is a user which cannot be removed.
In the user properties window, the assigned schema is empty. The user is a db_owner of the database. When I was trying to update the user, it asked me for the login. The login is empty, but the field is disabled.
Hi, I have a stored procedure that calls a scalar function. It works fine on my development database, but on my production database, I get this error when trying to execute the procedure: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.MyFunction", or the name is ambiguous. However, I am able to execute the function outside of the procedure like this: select dbo.MyFunction(0, 0) Anyone have idea? I can't figure it out.
I need a stored procedure to find out if the current user is a member of a certain role. I want to pass the role name and return a bit to tell whether he is a member or not. I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.DECLARE @usr varchar(32) SET @usr = USER EXEC sp_helpuser @usr But if the current user is a member of more than one role it returns multiple rows. So if I could do something like: DECLARE @grpName varchar(32) SELECT * FROM (EXEC sp_helpuser @usr) WHERE GroupName=@grpNameIF rowcount > 0 THENRETURN 1ELSERETURN 0END IF I know that doesn't work so how can I do this?
Hi folks, i've an instance with many userdatabases. i want to use SP_HELPUSER to output all database users and roles defined to em. how come i do this through a script to view permissions in all databases. I couldn't use (USE database ) in a loop.
I have created table called Login in sql server where i have column usercode, email and login_date (login_date is datetime type)So, i created web application using .net. whenever user logged in, i am allowing based userLoged table and  i am inserting into login table.
login table usercode email    login_date 001      a@gmail.com   2015-11-18 22:02:41.153 001      a@gmail.com   xxx . . .
I have another table called userLoged where i have column usercode,email and web_accessÂ
UserLoged table usercode email     web_access 001     a@gmail.com  Y
Now, if a@gmail.com (001) is not logged in for 45 days, i need to update web_access to be 'N' how to know if he /she not logged for 45 days.
how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.
I need to get a website up and running for a small group of people. I've had SQL Server for a while, but didn't have any use for it. It came with Visual Studio 2005, which gave me a little know how on scripting. Now, I have Visual Web Developer and what I need to make a web page. I just need to know how to configure all my settings to allow my website to be viewed online by anyone with an internet connection. I'm a person that understands detailed or simple directions, but I can not seem to find directions online. Now, I know what your thinking, but I've never attemped anything like this before. So, please, HELP! I need and want to learn as much as possible.
Thank you, tecfreak213
P.S. also, how do I change or get a web address? Thanks.
I'm getting a specific error when i try to run a .sql script for a Custom TableProfile from the Asp.net site,when i Parse the .sql file Commands execute perfectly. But when i actually "Execute" the .sql script i get the following errors::
Msg 15151, Level 16, State 1, Line 5
Cannot find the user 'WYATT-PCWyattASPNET', because it does not exist or you do not have permission.
