There is server with a sql server 2000 at the source. the sql 2000 databases from Source server are backed up and they are send to the destination server sql server 2008.the sql 2000 databases are restored and they are maintained at the same compatibility 80 and they are not changed. the user names and login names at the source server are different then the login names and user names at the destination server.
i had taken the roles and permissions at the source server and granted roles and permissions to the logins and users at the destination server. the users are having issues in viewing the objects at the destination server.
If there is a user at the source server named abcd who has created some tables and stored procedures.
The objects are stored under his name abcd.tablename and abcd.storedprocedurename at the source server.
At the destination server i had created a user called abcd123 i had granted roles and permissions to the abcd123 user similar to abcd user at the source server.
but when the user abcd123 is logging in at the destination server he cant see the objects abcd.tablename and abcd.storedprocedurename. he wants to read his tables (abcd.tablename) and execute his stored procedures abcd.storedprocedurename.
There are schemas created at the source server like abcd in the databases.
I did not create any schema's like abcd123 at the destination server.
Do i have to create the schemas abcd123 at the destination server and move the objects from the schema abcd to the schema abcd123 at the destination server to resolve this issue.that is option i am thinking right now.
Please let me know if you have any solution for this situation.
I would need a script which will take the user abcd as an input and moves all his objects( tables , stored procedures ) to the user abcd123. I want to run this script at the destination server.
I want to make sure the user abcd123 has all the objects of the user abcd. is there any way to check it.
How to grant permissions on the schema level for the schema abcd123. i know that we need to assign permisions at the object level in sql 2000 instead of using schema abcd. Is there any workaround.
The database we are working is a sql 2000 database on sql server 2008 destination server .
SQL Server 2000 was not schema based but SQL Server 2005 and 2008 is schema based
GRANT SELECT ON SCHEMA::[abcd] TO [abcd123]
you can give different rights also
1-Go to your required database
2-Go to Security
4-Select schema and right click on it then take properties
5-then click on the permission pane in the left side of the window then add required user to access this schema objects
Syed Jahanzaib Bin Hassan