we recentely tried migrating our databases from 2000 to sql server
2005.The migrated databases had some issues like collation issues,so
we created the scripts for each databases and then updated these
scripts to remove collation issues to make these compatible to SQL
server 2005.
But we have some permission issues for the users in case of these
created databases i.e. we are unable to see all the tables with
ssupdate user id ,or any other id .except the dbo id
which is the owner of this database,where we can see all the tables
and stored procedures.
iam fairly new at sql server .does anyone have any suggestions or
critcisms ?
you are welcome.

(83rish@gmail.com) writes:
> we recentely tried migrating our databases from 2000 to sql server
> 2005.The migrated databases had some issues like collation issues,so
> we created the scripts for each databases and then updated these
> scripts to remove collation issues to make these compatible to SQL
> server 2005.

If you had collations issues in your migration, it was simply because
someone had installed SQL 2005 with a different server collation than
you had in SQL 2000. This is something that could happen if you move
a database between different instances of the same version of SQL Server.

> But we have some permission issues for the users in case of these
> created databases i.e. we are unable to see all the tables with
> ssupdate user id ,or any other id .except the dbo id
> which is the owner of this database,where we can see all the tables
> and stored procedures.
> iam fairly new at sql server .does anyone have any suggestions or

This too is a problem that is not unique to when moving databases between
versions, but only when moving databses from one server to another.

There two concepts to keep in mind here: logins and users. Logins are on
server level, users are on database level. A database user usually map
to a server login through a SID. If you move database between servers,
users derived from Windows logins will still map to the same login on
the server - provided that they are authorised to log in on both servers.

However, this does not happen to SQL logins, as they are local to the
server. So in this case, you will need to remap the database users to
the logins on the new sever. Assuming that you have SQL 2005 SP2, you
can use the ALTER USER command:

ALTER USER nisse WITH LOGIN = nisse

Note: this particular syntax is missing from Books Online, as it was added
in SP2.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx