Assisted Editor for stored procedures

I have been using SQL server management studio beta to manage my SQL server 2000 and to develop stored procedures. I was very happy with Assisted Editor, but it seems to be missing from final SQL server 2005 products (developer's version) .

In object browser when I right-click on stored procedure and chose modify I used to get Assisted editor (described in http://msdn.microsoft.com/sql/learn/prog/tsql/default.aspx?pull=/library/en-us/dnsql90/html/tsqlqueries.asp). Now I get full Alter procedure statement (like in old Query analyzer) that can be edited and executed but not "real time" edited.

Is there any setting to get my beloved Assisted Editor back or Microsoft decided to get us rid of this user friendly addition?

Thanks and regards, Danko Jevtovic

The assisted editors functionality was cut from SQL Server 2005.

We had a lot of work left to make the feature correctly handle all the scenarios it needed to handle, and we ran out of time to fix it.

Assigning Execute Permissions to All My Stored Procedures

Hi All,
I have a database with about 250 stored procedures. I need to give some
users execute permissions on all of these procedures.
Can anyone tell me if there is an easy way to select a group and grant
execute permissions to it for all 250? I basically need the group to be
able to execute every SProc that I've created
Surely I don't have to go through every SProc one by one?
Please, please say it aint so!
:-(
Thanks
Simon
This might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@.User varchar(25) = 'db_executor',
@.Force bit = 0
)
as
set nocount on
declare @.Name varchar(100),
@.Command varchar(255),
@.uid int
declare @.ProcCount int
set @.ProcCount = 0
select @.Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @.Name is not null begin
select@.uid = uid
from sysusers
where[name] = @.User
if not exists (select * from sysprotects where id = object_id(@.Name)
and
action = 224 and uid = @.uid) or
@.Force = 1 begin
set @.Command = 'grant execute on ' + @.Name + ' to ' + @.User
print @.Command
set @.ProcCount = @.ProcCount + 1
exec (@.Command)
end
select @.Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @.Name
end
if @.ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
>
In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.
EG
create role MyApplicationUsers
create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser
grant execute on schema::dbo to MyApplicationUsers
go
create table t(id int)
go
create procedure p_t
as
select * from t
execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go
David
|||Thanks guys!
|||On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh
|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegr oups.com...
> On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>

Assigning Execute Permissions to All My Stored Procedures

Hi All,
I have a database with about 250 stored procedures. I need to give some
users execute permissions on all of these procedures.
Can anyone tell me if there is an easy way to select a group and grant
execute permissions to it for all 250? I basically need the group to be
able to execute every SProc that I've created
Surely I don't have to go through every SProc one by one?
Please, please say it aint so!
:-(
Thanks
SimonThis might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@.User varchar(25) = 'db_executor',
@.Force bit = 0
)
as
set nocount on
declare @.Name varchar(100),
@.Command varchar(255),
@.uid int
declare @.ProcCount int
set @.ProcCount = 0
select @.Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @.Name is not null begin
select @.uid = uid
from sysusers
where [name] = @.User
if not exists (select * from sysprotects where id = object_id(@.Name)
and
action = 224 and uid = @.uid) or
@.Force = 1 begin
set @.Command = 'grant execute on ' + @.Name + ' to ' + @.User
print @.Command
set @.ProcCount = @.ProcCount + 1
exec (@.Command)
end
select @.Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @.Name
end
if @.ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
>
In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.
EG
create role MyApplicationUsers
create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser
grant execute on schema::dbo to MyApplicationUsers
go
create table t(id int)
go
create procedure p_t
as
select * from t
execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go
David|||Thanks guys!|||On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegroups.com...
> On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
>> Hi All,
>> I have a database with about 250 stored procedures. I need to give some
>> users execute permissions on all of these procedures.
>> Can anyone tell me if there is an easy way to select a group and grant
>> execute permissions to it for all 250? I basically need the group to be
>> able to execute every SProc that I've created
>> Surely I don't have to go through every SProc one by one?
>> Please, please say it aint so!
>> :-(
>> Thanks
>> Simon
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
>>From sysobjects o
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>

Assigning Execute Permissions to All My Stored Procedures

Hi All,
I have a database with about 250 stored procedures. I need to give some
users execute permissions on all of these procedures.
Can anyone tell me if there is an easy way to select a group and grant
execute permissions to it for all 250? I basically need the group to be
able to execute every SProc that I've created
Surely I don't have to go through every SProc one by one?
Please, please say it aint so!
:-(
Thanks
SimonThis might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@.User varchar(25) = 'db_executor',
@.Force bit = 0
)
as
set nocount on
declare @.Name varchar(100),
@.Command varchar(255),
@.uid int
declare @.ProcCount int
set @.ProcCount = 0
select @.Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @.Name is not null begin
select @.uid = uid
from sysusers
where [name] = @.User
if not exists (select * from sysprotects where id = object_id(@.Name)
and
action = 224 and uid = @.uid) or
@.Force = 1 begin
set @.Command = 'grant execute on ' + @.Name + ' to ' + @.User
print @.Command
set @.ProcCount = @.ProcCount + 1
exec (@.Command)
end
select @.Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @.Name
end
if @.ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
>
In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.
EG
create role MyApplicationUsers
create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser
grant execute on schema::dbo to MyApplicationUsers
go
create table t(id int)
go
create procedure p_t
as
select * from t
execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go
David|||Thanks guys!|||On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegroups.com...
> On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>

Assessment of Database Stored Procedures

I am attempting to compile a list of questions that will enable me
assess 'at risk' stored procedures that need to be remediated in order
to minimize unplanned downtime and enhance database performance. Is
there a subset of criteria (parameters) from the MS SQL Server Best
Practices Analyzer that can be used for this purpose? Any pointers to
the appropriate documentation is most welcome.

Thanks in advance.It depends what you class as acceptable criteria. It depends on the nature
of the stored procedures.
For example, if it's a simple SELECT statement , returning it in 6 seconds
may be to slow for the end user., and could point to something else being
the problem.
Check http://www.sql-server-performance.c...icles_audit.asp for a
systematic approach

--

Jack Vamvas
___________________________________
Need an IT job? <a href="http://links.10026.com/?link=http://www.itjobfeed.com">uk it jobs</a>

"Umar Reyi" <iyerra@.indiatimes.comwrote in message
news:1177366044.733092.100680@.y80g2000hsf.googlegr oups.com...

Quote:

Originally Posted by

>I am attempting to compile a list of questions that will enable me
assess 'at risk' stored procedures that need to be remediated in order
to minimize unplanned downtime and enhance database performance. Is
there a subset of criteria (parameters) from the MS SQL Server Best
Practices Analyzer that can be used for this purpose? Any pointers to
the appropriate documentation is most welcome.
>
Thanks in advance.
>

Assembly.Load can't load my custom assembly from the GAC.

Hi there.

I have an assembly, call it A1, that I've deployed to a SQL Server 2005 database. I can use the managed stored procedures from A1 in SQL Server no problem.

In A1 there is a bit of code which uses the Assembly.Load() method, so load another assembly and use instances of class found in that external assembly. However, when I run the managed stored proc in A1 that uses Assembly.Load() I get the error:

Could not load file or assembly 'A1, Version=1.0.0.0, Culture=neutral,PublicKeyToken='?' or one of its dependencies. The system cannot find the file specified.

(note: for security I've changed some of the above line).

So I changed the Assembly.Load() to use

System.Data,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089

I re-built the project, re-deployed it and ran the code in SQL Server - it worked. I could create an instance of a System.Data.DataSet for example. So why can't I load my own custom assembly? My assembly does have a strong name and it's installed in the GAC. I wrote a console app to try and Assembly.Load() my custom assembly and that worked fine (it was also running on the same server as the SQL Server).

So it's defiantely the SQL Server that can't 'see' my customer assembly. What do I need to do this assembly so that SQL Server will allow me to Assembly.Load it, just as it can with System.Data?

Thanks
Jas.

Hi Jas,

In order you load your assembly using Assembly.Load, the assembly already has to be loaded into your database using CREATE ASSEMBLY. SqlClr will only load assemblies directly from the gac that are on the list of supported framework assemblies available here: http://msdn2.microsoft.com/en-us/library/ms403279.aspx. All the assemblies on that list (including System.Data) can be accessed by any assembly in your database, but all other assemblies must be loaded using CREATE ASSEMBLY first.

Steven

asp.net Stored Procedures vs not

Hey,

I'm developing an asp.net page that will be looking at having approx 500 000 members.

I have be warned by my webhost admin not to use stored procedures. They say that they are much less efficient with large databases. Harder to manage, and will lock me down.

This struck me as odd, as everything I have read and done so far points to SP's being the way to go.

People that have experience with large databases, what advice / comments can you give me? Should I use stored procedures, or should I put all my sql in the asp.net pages.

My situation, asp.net, ms sql. I will be storing the users in user groups, each group will have its own table containing member information, each group will have between 20 and 500 members. Each group will also have 2-3 tables associated with it. (not sure if this information is relevant, but if it is, there ya go).

ThanksWhat?? The only way I could see it being harder to manage would be if they didn't know what the heck they were doing with stored procedures...

I've been developing web sites with database interaction for the last 6 years or so and IMO stored procs are a must.

Stored procedures are less efficient with large databases?? Um,... no,... especially not if you are comparing them to straight sql statements being executed... yes, you can get problems with execution paths not using the right indexes but if you know what you are doing it's not a big issue...|||Phew, thought I was going mad. Because I couldn't understand why not to use SP's, lol.

One question though. (here we see the n00b emerge) lol

-Quote
yes, you can get problems with execution paths not using the right indexes but if you know what you are doing it's not a big issue...

I don't follow what people mean when they say indexing etc. I've sort of learn sql by playing and reading forums. I've never actually found a good book I thought worth buying (or resource really) other than these forums, lol.

How can I avoid bad indexing? What is indexing? This may be big and hard to answer, so if you'd rather throw me at a resource (online or book), do it, lol. A little reading never hurt anybody. It's a lot of 'reading the wrong stuff' that hurts, lol.

Thanks again rokslide,
-Ashleigh|||In short an index is exactly that....

Think about a book,.. it has an index that tells you where to find what you are looking for...

Tables are like books and they can have indexes,...

The indexes help "organise" the data and mean that when you are in there looking for things you can find it alot faster... Indexes will make the database take up more space but generally the performance increase and the reduced table locking will be well worth it...

It's difficult to say exactly what is bad indexing. I guess it's indexes that are too large for the return they give...

The problem I was referring to is... stored procedures are compiled, when they compile the build and execution plan and decide what indexes they will use. Sometimes they will not pick the best index and sometimes the best index will change depending on the data in the table. When this helps you need to force the stored procedure to build a new execution plan. This is probably not technically right (eg I have the phrases wrong) but the gist of it is correct from my understanding...

Deciding what indexes you want to build really depends on what you want to search the table for and how the data in the table relates to other things... drop me a PM (do we have PM's here?) and I can help you if you want to provide specific details.

ASP.NET Membership tables dissappear when db is attached to a different sql server instanc

A SQL Server 2005 (developer edition) database contains both application
data tables and ASP.NET membership tables and stored procedures and runs
without error on computer A. When the database is detached, copied to
computer B, and re-attached, the ASP.NET membership tables and stored
procedures are gone. If you know the cause/cure for this issue, I would
appreciate your help.
Thanks,
Keith
Are they gone, or just not visible? It could be a permissions issue
where you're connecting with elevated permissions on A and not on B.
--Mary
On Sat, 24 Feb 2007 22:22:06 -0800, "keith" <kbrickey@.dslextreme.com>
wrote:

>A SQL Server 2005 (developer edition) database contains both application
>data tables and ASP.NET membership tables and stored procedures and runs
>without error on computer A. When the database is detached, copied to
>computer B, and re-attached, the ASP.NET membership tables and stored
>procedures are gone. If you know the cause/cure for this issue, I would
>appreciate your help.
>Thanks,
>Keith
>

ASP.NET Membership tables dissappear when db is attached to a different sql server instanc

A SQL Server 2005 (developer edition) database contains both application
data tables and ASP.NET membership tables and stored procedures and runs
without error on computer A. When the database is detached, copied to
computer B, and re-attached, the ASP.NET membership tables and stored
procedures are gone. If you know the cause/cure for this issue, I would
appreciate your help.
Thanks,
KeithAre they gone, or just not visible? It could be a permissions issue
where you're connecting with elevated permissions on A and not on B.
--Mary
On Sat, 24 Feb 2007 22:22:06 -0800, "keith" <kbrickey@.dslextreme.com>
wrote:
>A SQL Server 2005 (developer edition) database contains both application
>data tables and ASP.NET membership tables and stored procedures and runs
>without error on computer A. When the database is detached, copied to
>computer B, and re-attached, the ASP.NET membership tables and stored
>procedures are gone. If you know the cause/cure for this issue, I would
>appreciate your help.
>Thanks,
>Keith
>