It seems a bug of SQL Server 2005.

I do the following steps:

1:Use Manage Studio login the server with Integrated security.

2:Create a dabase named testdb;

3:Create a SQL Server login named amber ,and set it to be dbowner of testdb;

4:Create a SQL Server login named guxiaobo ,set it's default databse to be testbd,and in the testdb databse map login guxiaobo to user guxiaobo;

5:Close Manage Studio and reopen it ,this time use login amber log to the server.

6:In database testbd create a databse role role1 owned by dbo;

7:In database testdb create a schema schema1 owned by dbo;

8:Set user guxiaobo to be member of role1,and set guxiaobo's default schema to be schema1;

9:In the schema properties-schema1 dialog choose permissions,in the users or roles listview I add role1 to it.in the explicit permissions for role1 listbox,I choose select /update/insert/delete/view definition grant checkboxes.and apply the selects.

10:In the schema1 schema I create a table:create table a(a int ,b varchar(10));

11:In the dabase role properties-role1 dialog choose securables panel ,int securable listview I add the schema1.a table,and in explicit permissions for schema1.a I choose all for grant ,then apply the selects.

12:Now I use login guxiaobo to log into dabase and issue "select * from a",but got a error msg saying guxiaobo has not enough permission to select form table a.

Does anyone has found anything I missed?

And in step 11 ,I choose all the columns of table a for all permissions.|||Anybody helps ?

Issues after server rename

We have a new SQL Server 2005 box in place and we named this server - Server2. Restored all the production databases from original Server1. Server1 is running SQL Server 2000 Enterprise Edition. Server1 also has three subscribers ServerA, ServerB, and ServerC.

After Server2 was renamed to Server1 and Server1 decommissioned, replication is not working on the new Server1 due to the renaming issue. I can afford to re do replication from ground up on Server1 but cannot reinstall SQL Server 2005 and start from scratch since the server is already in production.

How can I resolve this issue? What is the right approach to take for the scenario described above?

Its unclear to me what state the new server 1 is in now. To get this to work you should have restored the master, msdb, distirbution, and publication databases, and then the transaction logs of the publication databases, followed by the tail of the publication database restored with the keep_replication switch. These should have been restored to a SQL 2000 server, the new SQL 2000 server should br renamed Server1 and restarted. Then run the sp_replrestart command, and run with the continue on data consistency error profile until the errors are cleared.

Then upgrade your publisher to SQL 2005.

If publications exist in Server1, attempt to script them out and then drop the publications and recreate them with the script and then redeploy your subcribers.

Issue

Anyone know this answer?
[New].[Configuration:]
O/S: Windows XP Service Pack2 on a machine named MERCURY
SQL Server Developer's Edition SP4
[Old].[Configuration]
O/S: Windows 2000 on a machine called PIONEER
SQL Server Developer's Edition SP4
[Problem].[ Description:]
1. Created 25 clean, functional DTS packages on PIONEER. On that machine,
any task in DTS designer which allows the user to select a single table name
from a drop-down list (Combo Box) (such as Bulk Insert Tasks), have Square
Delimited Indentifiers. For example:
[DBO].[TABLENAME1]
[DBO].[TABLENAME1]
[DBO].[TABLENAME1]
[DBO].[TABLENAME1]
2. Packages were then moved to a new computer, running the configuration
shown at the top.On that computer, the ODBC Administrator has default
settings for each new connection. These settings include the check box for
ANSI Quoted Identifiers which are ON (Checked). That is the same default as
Windows 2000 ODBC. We unchecked the box for every new connection.
3. When the packages from the PIONEER machine with square bracketed
identifiers in the Drop down lists were opened then square brackets
disappeared from the first display item as soon as you click the button of
the drop-down list. The drop down functionality apparently dropped the list
items from the old package, required the database with the same name on
PIONEER, repopulated the list, but this time, EVERY SINGLE IDENTIFIER WAS
DELIMITED WITH DOUBLE-QUOTES, and, worst of all, the correct name was
replaced with the first alphabetical name.The new list looked like this:
"DBO"."TABLENAME1"
"DBO"."TABLENAME1"
"DBO"."TABLENAME1"
"DBO"."TABLENAME1"
Doesn't that JUST BLOW!
Also, it appears that no connections can recognize the objects anymore.
Robert

> Anyone know this answer?
> [New].[Configuration:]
> O/S: Windows XP Service Pack2 on a machine named MERCURY
> SQL Server Developer's Edition SP4
> [Old].[Configuration]
> O/S: Windows 2000 on a machine called PIONEER
> SQL Server Developer's Edition SP4
> [Problem].[ Description:]
> 1. Created 25 clean, functional DTS packages on PIONEER. On that machine,
> any task in DTS designer which allows the user to select a single table
> name from a drop-down list (Combo Box) (such as Bulk Insert Tasks), have
> Square Delimited Indentifiers. For example:
> [DBO].[TABLENAME1]
> [DBO].[TABLENAME1]
> [DBO].[TABLENAME1]
> [DBO].[TABLENAME1]
> 2. Packages were then moved to a new computer, running the configuration
> shown at the top.On that computer, the ODBC Administrator has default
> settings for each new connection. These settings include the check box for
> ANSI Quoted Identifiers which are ON (Checked). That is the same default
> as Windows 2000 ODBC. We unchecked the box for every new connection.
> 3. When the packages from the PIONEER machine with square bracketed
> identifiers in the Drop down lists were opened then square brackets
> disappeared from the first display item as soon as you click the button of
> the drop-down list. The drop down functionality apparently dropped the
> list items from the old package, required the database with the same name
> on PIONEER, repopulated the list, but this time, EVERY SINGLE IDENTIFIER
> WAS DELIMITED WITH DOUBLE-QUOTES, and, worst of all, the correct name was
> replaced with the first alphabetical name.
>
>
>