Build a Connection String Programmatically

I'm getting really frustrated with using the web.config to store all database information. It seems like you really don't have any real flexibilty when moving an application from server to server (between dev, test and prod). Seems like you have to create separate config files when you move it every time. Seems like you always run into a risk of over writing one of them if you are not careful.

Is there a good way to design the connection object to be able to tell which server it is on and determine if it's a test, dev or prod type of connection. I have used a basic three connection strings that it will compare the computer name with the key. But again, the connection string is hard coded in the web config. At least this way I am able to move all code together without the worry of overwritting the config file. I just have to beleive there is a better way to build something like this. I've heard of setting up enviroment variables on the server, but not sure how safe that is.

I'm looking everywhere on the web, but get really lame examples. If anyone knows a good method or a sample I can check out, that would be great.

Thanks!

Im not sure about building a connectionstring dynamically, but the generally accepted way to solve this is with a User.config file for you and the host. You keep all the normal crap in the webconfig, and then your connection string and any other variables in the userconfig. Here is a good example.

http://blogs.msdn.com/rprabhu/articles/433979.aspx

Build 9.0.3050

I downloaded SP2 on March 10, 2007, and applied it. The system to which I applied it reports 9.0.3050 as the version number, not 9.0.3042. Is this due to the post-SP2 fix?My system, which was updated with the bugged SP2, and then the subsequent hotfix, also reports 9.0.3050. So that seems like a reasonable assumption.|||

refer this link for latest fixes in SQL Server 2005...

http://support.microsoft.com/kb/933508

SP2 Release version was --9.00.3042.00

Latest version with this fix(Mar 05) is -- 9.00.3050.00

Madhu

Build = Error: Value cannot be null.

Hello Everyone,
I am having a heck of a time with this report. I have five datasets,
all interacting with stored procedures in SQL Server 2005, all using
the same parameter. The datasets run fine individually. I can run the
report fine. When I try to build or deploy the report, I get this:
-- Build started: Project: JobApplication, Configuration: Debug
--
Error: Value cannot be null.
========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
==========
Not very descriptive unfortunately :( I'm not even sure where to
look.
Any ideas? Any help would be greatly appreciated.
Have a great day,
RyanDid one of your datasets change 'mysteriously' to text, rather than Stored
Procedure? I think I have seen the error before, but I can't remember if it
is related to this problem or not.
"Ryan" wrote:
> Hello Everyone,
> I am having a heck of a time with this report. I have five datasets,
> all interacting with stored procedures in SQL Server 2005, all using
> the same parameter. The datasets run fine individually. I can run the
> report fine. When I try to build or deploy the report, I get this:
> -- Build started: Project: JobApplication, Configuration: Debug
> --
> Error: Value cannot be null.
> ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> ==========> Not very descriptive unfortunately :( I'm not even sure where to
> look.
> Any ideas? Any help would be greatly appreciated.
> Have a great day,
> Ryan
>|||Just a guess, you have refered a value which is a PK and in the report
properties you have clicked "Allow Null"
Amarnath, MCTS
"Ryan" wrote:
> Hello Everyone,
> I am having a heck of a time with this report. I have five datasets,
> all interacting with stored procedures in SQL Server 2005, all using
> the same parameter. The datasets run fine individually. I can run the
> report fine. When I try to build or deploy the report, I get this:
> -- Build started: Project: JobApplication, Configuration: Debug
> --
> Error: Value cannot be null.
> ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> ==========> Not very descriptive unfortunately :( I'm not even sure where to
> look.
> Any ideas? Any help would be greatly appreciated.
> Have a great day,
> Ryan
>|||Thank you both for answering . . . in case anybody is interested:
I tried both stored procedure and text type datasets . . . both with
the same result. I'll check to make sure when it is set as a stored
procedure, it stays that way :)
Hmmm, I assume the Allow Null is for a parameter? If so, there is one
the one parameter and the "Allow null value" is unchecked.
Again, thank you for the responses; I'll give them a whirl. In the
mean time, I found that uploading the report manually works fine.
Ryan
On Mar 23, 12:56 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Just a guess, you have refered a value which is a PK and in the report
> properties you have clicked "Allow Null"
> Amarnath, MCTS
> "Ryan" wrote:
> > Hello Everyone,
> > I am having a heck of a time with this report. I have five datasets,
> > all interacting with stored procedures in SQL Server 2005, all using
> > the same parameter. The datasets run fine individually. I can run the
> > report fine. When I try to build or deploy the report, I get this:
> > -- Build started: Project: JobApplication, Configuration: Debug
> > --
> > Error: Value cannot be null.
> > ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> > ==========> > Not very descriptive unfortunately :( I'm not even sure where to
> > look.
> > Any ideas? Any help would be greatly appreciated.
> > Have a great day,
> > Ryan

bugs, Information_schema appeared as a user in master database

Hi, All
Recently, I was running a script in sql 2000 on window 2000 server like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipp ed'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
Yifei
Hi
You may want to try
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
'.INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipp ed'') = 0'
Although if that gives you thre right view you may get nothing back as the
OBJECT_ID function could cause problems, you may therefore want to resort to
using sysobjects.
John
"Yifei Jiang" wrote:

> Hi, All
> Recently, I was running a script in sql 2000 on window 2000 server like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipp ed'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via group
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the db
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the problem?
> any help is appreciated.
> Thanks
> Yifei
>
>
>
|||Hi, John,
Thank you for replay,
I think the issue relate to sql 2005, because from em on the server, the
information_shema does not show as user, from em installed sql2005,
information_shema shows as user. Same as another machine that installed sql
2005 and does not run the script.
The reason because I did not check the user until I run the script and have
to check it and find the problem.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...[vbcol=seagreen]
> Hi
> You may want to try
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> '.INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipp ed'') = 0'
> Although if that gives you thre right view you may get nothing back as the
> OBJECT_ID function could cause problems, you may therefore want to resort
> to
> using sysobjects.
> John
>
>
> "Yifei Jiang" wrote:
|||The original post has no mention of SQL 2005. If you use Management
Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
under system views in the master database. If you use Enterprise
manager to look at a SQL 2000 database there is no division between
system and user views. If you used a cursor instead of using the
undocumented xp_execresultset this should work.
John
Scott wrote:[vbcol=seagreen]
> Hi, John,
> Thank you for replay,
> I think the issue relate to sql 2005, because from em on the server, the
> information_shema does not show as user, from em installed sql2005,
> information_shema shows as user. Same as another machine that installed sql
> 2005 and does not run the script.
> The reason because I did not check the user until I run the script and have
> to check it and find the problem.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
|||Hi, John,
You are right, what I want to say that sql 2005 caused the information_shema
as a user in master db. it is nothing wrong with running script.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1134379113.719483.236400@.o13g2000cwo.googlegr oups.com...
> The original post has no mention of SQL 2005. If you use Management
> Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> under system views in the master database. If you use Enterprise
> manager to look at a SQL 2000 database there is no division between
> system and user views. If you used a cursor instead of using the
> undocumented xp_execresultset this should work.
> John
> Scott wrote:
>
|||Hi
In SQL 2005 you need to be more aware of the difference between a schema and
a user. I think Kalen wrote about this some time ago in SQL Server magazine,
but I can't put my hands on this, but you may want to read
http://msdn.microsoft.com/msdnmag/is...y/default.aspx
John
"Yifei" wrote:

> Hi, John,
> You are right, what I want to say that sql 2005 caused the information_shema
> as a user in master db. it is nothing wrong with running script.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1134379113.719483.236400@.o13g2000cwo.googlegr oups.com...
>
>

bugs, Information_schema appeared as a user in master database

Hi, All
Recently, I was running a script in sql 2000 on window 2000 server like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
YifeiHi
You may want to try
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
'.INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
Although if that gives you thre right view you may get nothing back as the
OBJECT_ID function could cause problems, you may therefore want to resort to
using sysobjects.
John
"Yifei Jiang" wrote:

> Hi, All
> Recently, I was running a script in sql 2000 on window 2000 server like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via gro
up
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the d
b
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the proble
m?
> any help is appreciated.
> Thanks
> Yifei
>
>
>|||Hi, John,
Thank you for replay,
I think the issue relate to sql 2005, because from em on the server, the
information_shema does not show as user, from em installed sql2005,
information_shema shows as user. Same as another machine that installed sql
2005 and does not run the script.
The reason because I did not check the user until I run the script and have
to check it and find the problem.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...[vbcol=seagreen]
> Hi
> You may want to try
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> '.INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
> Although if that gives you thre right view you may get nothing back as the
> OBJECT_ID function could cause problems, you may therefore want to resort
> to
> using sysobjects.
> John
>
>
> "Yifei Jiang" wrote:
>|||The original post has no mention of SQL 2005. If you use Management
Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
under system views in the master database. If you use Enterprise
manager to look at a SQL 2000 database there is no division between
system and user views. If you used a cursor instead of using the
undocumented xp_execresultset this should work.
John
Scott wrote:[vbcol=seagreen]
> Hi, John,
> Thank you for replay,
> I think the issue relate to sql 2005, because from em on the server, the
> information_shema does not show as user, from em installed sql2005,
> information_shema shows as user. Same as another machine that installed sq
l
> 2005 and does not run the script.
> The reason because I did not check the user until I run the script and hav
e
> to check it and find the problem.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...|||Hi, John,
You are right, what I want to say that sql 2005 caused the information_shema
as a user in master db. it is nothing wrong with running script.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
> The original post has no mention of SQL 2005. If you use Management
> Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> under system views in the master database. If you use Enterprise
> manager to look at a SQL 2000 database there is no division between
> system and user views. If you used a cursor instead of using the
> undocumented xp_execresultset this should work.
> John
> Scott wrote:
>|||Hi
In SQL 2005 you need to be more aware of the difference between a schema and
a user. I think Kalen wrote about this some time ago in SQL Server magazine,
but I can't put my hands on this, but you may want to read
[url]http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx[/url
]
John
"Yifei" wrote:

> Hi, John,
> You are right, what I want to say that sql 2005 caused the information_she
ma
> as a user in master db. it is nothing wrong with running script.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
>
>sql

bugs, Information_schema appeared as a user in master database

Hi, All
Recently, I was running a script in sql 2000 on window 2000 server like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
YifeiHi
You may want to try
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
'.INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
Although if that gives you thre right view you may get nothing back as the
OBJECT_ID function could cause problems, you may therefore want to resort to
using sysobjects.
John
"Yifei Jiang" wrote:
> Hi, All
> Recently, I was running a script in sql 2000 on window 2000 server like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via group
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the db
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the problem?
> any help is appreciated.
> Thanks
> Yifei
>
>
>|||Hi, John,
Thank you for replay,
I think the issue relate to sql 2005, because from em on the server, the
information_shema does not show as user, from em installed sql2005,
information_shema shows as user. Same as another machine that installed sql
2005 and does not run the script.
The reason because I did not check the user until I run the script and have
to check it and find the problem.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> Hi
> You may want to try
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> '.INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> Although if that gives you thre right view you may get nothing back as the
> OBJECT_ID function could cause problems, you may therefore want to resort
> to
> using sysobjects.
> John
>
>
> "Yifei Jiang" wrote:
>> Hi, All
>> Recently, I was running a script in sql 2000 on window 2000 server like:
>> declare @.sql nvarchar(4000)
>> declare @.db sysname ; set @.db = DB_NAME()
>> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
>> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.''
>> +
>> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
>> INFORMATION_SCHEMA.ROUTINES ' +
>> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> exec master.dbo.xp_execresultset @.sql,@.db
>> to grant stored procedures execution permition to role, after running the
>> script, the privilige is grant it to that role, but I got very strange
>> things happened on sql server.
>> 1. Master database get Information_Shema as a user. dbaccess "via group
>> membership"
>> 2.Master database got system_function_schema as a user , dbaccess "via
>> group
>> membership"
>> 3.guest user show in every database in the instance, dbaccess "via group
>> membership"
>> if I delete guest account, show me message "the user is not in
>> database", sp_helpuser did not show guest user, enterprice manager show
>> it
>> as a user.
>> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
>> delete it successful, however, a couple minutes later, it appears in the
>> db
>> as dbaccess via group membership.
>> it is really painful for me. could anyone give me a hint to fix the
>> problem?
>> any help is appreciated.
>> Thanks
>> Yifei
>>
>>
>>|||The original post has no mention of SQL 2005. If you use Management
Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
under system views in the master database. If you use Enterprise
manager to look at a SQL 2000 database there is no division between
system and user views. If you used a cursor instead of using the
undocumented xp_execresultset this should work.
John
Scott wrote:
> Hi, John,
> Thank you for replay,
> I think the issue relate to sql 2005, because from em on the server, the
> information_shema does not show as user, from em installed sql2005,
> information_shema shows as user. Same as another machine that installed sql
> 2005 and does not run the script.
> The reason because I did not check the user until I run the script and have
> to check it and find the problem.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> > Hi
> >
> > You may want to try
> >
> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> > '.INFORMATION_SCHEMA.ROUTINES ' +
> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >
> > Although if that gives you thre right view you may get nothing back as the
> > OBJECT_ID function could cause problems, you may therefore want to resort
> > to
> > using sysobjects.
> >
> > John
> >
> >
> >
> >
> > "Yifei Jiang" wrote:
> >
> >> Hi, All
> >>
> >> Recently, I was running a script in sql 2000 on window 2000 server like:
> >>
> >> declare @.sql nvarchar(4000)
> >> declare @.db sysname ; set @.db = DB_NAME()
> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> >>
> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.''
> >> +
> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> >> INFORMATION_SCHEMA.ROUTINES ' +
> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >>
> >> exec master.dbo.xp_execresultset @.sql,@.db
> >>
> >> to grant stored procedures execution permition to role, after running the
> >> script, the privilige is grant it to that role, but I got very strange
> >> things happened on sql server.
> >>
> >> 1. Master database get Information_Shema as a user. dbaccess "via group
> >> membership"
> >> 2.Master database got system_function_schema as a user , dbaccess "via
> >> group
> >> membership"
> >> 3.guest user show in every database in the instance, dbaccess "via group
> >> membership"
> >> if I delete guest account, show me message "the user is not in
> >> database", sp_helpuser did not show guest user, enterprice manager show
> >> it
> >> as a user.
> >> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> >> delete it successful, however, a couple minutes later, it appears in the
> >> db
> >> as dbaccess via group membership.
> >>
> >> it is really painful for me. could anyone give me a hint to fix the
> >> problem?
> >>
> >> any help is appreciated.
> >>
> >> Thanks
> >>
> >> Yifei
> >>
> >>
> >>
> >>
> >>
> >>|||Hi, John,
You are right, what I want to say that sql 2005 caused the information_shema
as a user in master db. it is nothing wrong with running script.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
> The original post has no mention of SQL 2005. If you use Management
> Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> under system views in the master database. If you use Enterprise
> manager to look at a SQL 2000 database there is no division between
> system and user views. If you used a cursor instead of using the
> undocumented xp_execresultset this should work.
> John
> Scott wrote:
>> Hi, John,
>> Thank you for replay,
>> I think the issue relate to sql 2005, because from em on the server, the
>> information_shema does not show as user, from em installed sql2005,
>> information_shema shows as user. Same as another machine that installed
>> sql
>> 2005 and does not run the script.
>> The reason because I did not check the user until I run the script and
>> have
>> to check it and find the problem.
>> Thanks
>> Yifei
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
>> > Hi
>> >
>> > You may want to try
>> >
>> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
>> > ''.'' +
>> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
>> > '.INFORMATION_SCHEMA.ROUTINES ' +
>> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> >
>> > Although if that gives you thre right view you may get nothing back as
>> > the
>> > OBJECT_ID function could cause problems, you may therefore want to
>> > resort
>> > to
>> > using sysobjects.
>> >
>> > John
>> >
>> >
>> >
>> >
>> > "Yifei Jiang" wrote:
>> >
>> >> Hi, All
>> >>
>> >> Recently, I was running a script in sql 2000 on window 2000 server
>> >> like:
>> >>
>> >> declare @.sql nvarchar(4000)
>> >> declare @.db sysname ; set @.db = DB_NAME()
>> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
>> >>
>> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
>> >> ''.''
>> >> +
>> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
>> >> INFORMATION_SCHEMA.ROUTINES ' +
>> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> >>
>> >> exec master.dbo.xp_execresultset @.sql,@.db
>> >>
>> >> to grant stored procedures execution permition to role, after running
>> >> the
>> >> script, the privilige is grant it to that role, but I got very strange
>> >> things happened on sql server.
>> >>
>> >> 1. Master database get Information_Shema as a user. dbaccess "via
>> >> group
>> >> membership"
>> >> 2.Master database got system_function_schema as a user , dbaccess "via
>> >> group
>> >> membership"
>> >> 3.guest user show in every database in the instance, dbaccess "via
>> >> group
>> >> membership"
>> >> if I delete guest account, show me message "the user is not in
>> >> database", sp_helpuser did not show guest user, enterprice manager
>> >> show
>> >> it
>> >> as a user.
>> >> 4. run sp_grantdbaccess guest. the guest get permitted to use
>> >> database. I
>> >> delete it successful, however, a couple minutes later, it appears in
>> >> the
>> >> db
>> >> as dbaccess via group membership.
>> >>
>> >> it is really painful for me. could anyone give me a hint to fix the
>> >> problem?
>> >>
>> >> any help is appreciated.
>> >>
>> >> Thanks
>> >>
>> >> Yifei
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>|||Hi
In SQL 2005 you need to be more aware of the difference between a schema and
a user. I think Kalen wrote about this some time ago in SQL Server magazine,
but I can't put my hands on this, but you may want to read
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx
John
"Yifei" wrote:
> Hi, John,
> You are right, what I want to say that sql 2005 caused the information_shema
> as a user in master db. it is nothing wrong with running script.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
> > The original post has no mention of SQL 2005. If you use Management
> > Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> > under system views in the master database. If you use Enterprise
> > manager to look at a SQL 2000 database there is no division between
> > system and user views. If you used a cursor instead of using the
> > undocumented xp_execresultset this should work.
> >
> > John
> >
> > Scott wrote:
> >> Hi, John,
> >>
> >> Thank you for replay,
> >>
> >> I think the issue relate to sql 2005, because from em on the server, the
> >> information_shema does not show as user, from em installed sql2005,
> >> information_shema shows as user. Same as another machine that installed
> >> sql
> >> 2005 and does not run the script.
> >>
> >> The reason because I did not check the user until I run the script and
> >> have
> >> to check it and find the problem.
> >>
> >> Thanks
> >>
> >> Yifei
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> >> > Hi
> >> >
> >> > You may want to try
> >> >
> >> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
> >> > ''.'' +
> >> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> >> > '.INFORMATION_SCHEMA.ROUTINES ' +
> >> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >> >
> >> > Although if that gives you thre right view you may get nothing back as
> >> > the
> >> > OBJECT_ID function could cause problems, you may therefore want to
> >> > resort
> >> > to
> >> > using sysobjects.
> >> >
> >> > John
> >> >
> >> >
> >> >
> >> >
> >> > "Yifei Jiang" wrote:
> >> >
> >> >> Hi, All
> >> >>
> >> >> Recently, I was running a script in sql 2000 on window 2000 server
> >> >> like:
> >> >>
> >> >> declare @.sql nvarchar(4000)
> >> >> declare @.db sysname ; set @.db = DB_NAME()
> >> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> >> >>
> >> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
> >> >> ''.''
> >> >> +
> >> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> >> >> INFORMATION_SCHEMA.ROUTINES ' +
> >> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >> >>
> >> >> exec master.dbo.xp_execresultset @.sql,@.db
> >> >>
> >> >> to grant stored procedures execution permition to role, after running
> >> >> the
> >> >> script, the privilige is grant it to that role, but I got very strange
> >> >> things happened on sql server.
> >> >>
> >> >> 1. Master database get Information_Shema as a user. dbaccess "via
> >> >> group
> >> >> membership"
> >> >> 2.Master database got system_function_schema as a user , dbaccess "via
> >> >> group
> >> >> membership"
> >> >> 3.guest user show in every database in the instance, dbaccess "via
> >> >> group
> >> >> membership"
> >> >> if I delete guest account, show me message "the user is not in
> >> >> database", sp_helpuser did not show guest user, enterprice manager
> >> >> show
> >> >> it
> >> >> as a user.
> >> >> 4. run sp_grantdbaccess guest. the guest get permitted to use
> >> >> database. I
> >> >> delete it successful, however, a couple minutes later, it appears in
> >> >> the
> >> >> db
> >> >> as dbaccess via group membership.
> >> >>
> >> >> it is really painful for me. could anyone give me a hint to fix the
> >> >> problem?
> >> >>
> >> >> any help is appreciated.
> >> >>
> >> >> Thanks
> >> >>
> >> >> Yifei
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >
>
>

Bugs without KB articles in Cumulative Update 5 (build 3215)

There are still a handful of bugs listed as fixed in build 3215 that don't
have KB articles. Will a KB be posted soon and how would I know in advance if
I am affected by these bugs without a KB to read?
The particular bug I'm interested in is 50002184: "You receive incorrect
results when you run a query in Microsoft SQL Server 2005."
Searches in these forums and elsewhere have come up empty for me. Based
purely on that brief bug description I have no way of knowing whether I am
impacted or not. And with hotfixes the recommendation is to not apply unless
you are explicitly affected.
Got any advice?If you notice Update 4 KB was last updated on Jan 15th, way after it ewas
released. You might get some answers if you subscribe to KBALERTZ under SQL.
Chris
"ahallock" <ahallock@.discussions.microsoft.com> wrote in message
news:24EF3CA9-52C9-4C91-AE60-C45B17DA1880@.microsoft.com...
> There are still a handful of bugs listed as fixed in build 3215 that don't
> have KB articles. Will a KB be posted soon and how would I know in advance
> if
> I am affected by these bugs without a KB to read?
> The particular bug I'm interested in is 50002184: "You receive incorrect
> results when you run a query in Microsoft SQL Server 2005."
> Searches in these forums and elsewhere have come up empty for me. Based
> purely on that brief bug description I have no way of knowing whether I am
> impacted or not. And with hotfixes the recommendation is to not apply
> unless
> you are explicitly affected.
> Got any advice?|||I don't know about this specific bug. But not every bug or bug fix will have
a public KB article.
Linchi
"ahallock" wrote:
> There are still a handful of bugs listed as fixed in build 3215 that don't
> have KB articles. Will a KB be posted soon and how would I know in advance if
> I am affected by these bugs without a KB to read?
> The particular bug I'm interested in is 50002184: "You receive incorrect
> results when you run a query in Microsoft SQL Server 2005."
> Searches in these forums and elsewhere have come up empty for me. Based
> purely on that brief bug description I have no way of knowing whether I am
> impacted or not. And with hotfixes the recommendation is to not apply unless
> you are explicitly affected.
> Got any advice?|||Also look in this blog http://blogs.technet.com/hot/default.aspx
Chris
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:uq2MuesXIHA.4896@.TK2MSFTNGP06.phx.gbl...
> If you notice Update 4 KB was last updated on Jan 15th, way after it ewas
> released. You might get some answers if you subscribe to KBALERTZ under
> SQL.
> Chris
> "ahallock" <ahallock@.discussions.microsoft.com> wrote in message
> news:24EF3CA9-52C9-4C91-AE60-C45B17DA1880@.microsoft.com...
>> There are still a handful of bugs listed as fixed in build 3215 that
>> don't
>> have KB articles. Will a KB be posted soon and how would I know in
>> advance if
>> I am affected by these bugs without a KB to read?
>> The particular bug I'm interested in is 50002184: "You receive incorrect
>> results when you run a query in Microsoft SQL Server 2005."
>> Searches in these forums and elsewhere have come up empty for me. Based
>> purely on that brief bug description I have no way of knowing whether I
>> am
>> impacted or not. And with hotfixes the recommendation is to not apply
>> unless
>> you are explicitly affected.
>> Got any advice?
>|||That looks like an excellent source of news that I didn't know about. Thank
you for bringing it to my attention!
"Chris Wood" wrote:
> Also look in this blog http://blogs.technet.com/hot/default.aspx