changing the server.database against which a script is executed

Hi there:
I write scripts which I must execute in multiple environments (Dev,
Test, UAT, Prod). Dev and Test are located on the same server, so
it's easy to change the db with the USE command. UAT and Prod are on
another server. If I close the script and re-open it, I'm not
prompted for connection information -- it simply defaults to whichever
connection I used last (because I haven't closed SSMS?).
So far, I've use the Object Browser to navigate to an object within
the server.db I want, click New Query, then copy & paste the query
into the new window. This seems pretty cheesy. Is there a more
elegant approach?
Thanks;
Duncan
Duncan A. McRae (google.com@.mcrae.ca) writes:
> I write scripts which I must execute in multiple environments (Dev,
> Test, UAT, Prod). Dev and Test are located on the same server, so
> it's easy to change the db with the USE command. UAT and Prod are on
> another server. If I close the script and re-open it, I'm not
> prompted for connection information -- it simply defaults to whichever
> connection I used last (because I haven't closed SSMS?).
> So far, I've use the Object Browser to navigate to an object within
> the server.db I want, click New Query, then copy & paste the query
> into the new window. This seems pretty cheesy. Is there a more
> elegant approach?
Yes. Right-click in the query window and select Change Connection from
the context menu.
Overall, I prefer Query Analyzer over Mgmt Studio, but being able to
change the connection for a query window is a great feature.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Changing Permissions for all Stored Procs in a SQL2005 DB

I have a massive number of Stored Procedures in a SQL Database. I need to
give a Database Role in that Database Execute permissions for all of the
Stored Procedures I had created (but not the System Stored Procedures). Doin
g
this through the SQL Management Tool UI will take FOREVER. Is there another
way? If it's a script of some kind, can someone give me a hint or a sample?
This may be a bit beyond me.
AlexHi,
In SQL Server 2005 it is very easy to set the execute rights to all
procedures. See the script
USE DBNAME
GO
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
Assign this role to the database user and he will be able to execute all
procedures inside the databse
THanks
Hari
SQL Server MVP
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:1BCFF9ED-12DC-4FD3-921F-3A1E185DB18E@.microsoft.com...
>I have a massive number of Stored Procedures in a SQL Database. I need to
> give a Database Role in that Database Execute permissions for all of the
> Stored Procedures I had created (but not the System Stored Procedures).
> Doing
> this through the SQL Management Tool UI will take FOREVER. Is there
> another
> way? If it's a script of some kind, can someone give me a hint or a
> sample?
> This may be a bit beyond me.
> Alex|||Hi Alex,
Thanks for posting.
To execute a stored procedure, you have to be granted execute permission on
this stored procedure. As we know, stored procedure referenced as object in
SQL 2005. To grant permission on a object, we have a T-SQL command: Grant.
You can Grants permissions on a table, view, table-valued function, stored
procedure, extended stored procedure, scalar function, aggregate function,
service queue, or synonym.
For more information about Grant command, please refer to following link:
http://msdn2.microsoft.com/en-us/library/ms188371.aspx
Hope this information helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
--[vbcol=seagreen]
Doing[vbcol=seagreen]
another[vbcol=seagreen]
sample?[vbcol=seagreen]|||Hari -
Hi. This is very helpful. Can I ask one more detail? Assuming that I've
already created the database role, if I do "GRANT EXECUTE TO MyNewRole", wil
l
this grant execute permissions to ALL of the stored procedures in the
database (including system stored procedures)? I don't really want to do
that. Is there a way that I can have it grant execute only to stored procs
that I created (dbo.<procname> )? Or can I do it by matching a pattern in the
SP name? All of my stored procedures start with a special 3 character prefix
.
Can I use a "where" clause or something? And if I can, how?
Let me know?
Thanks!
Alex
"Hari Prasad" wrote:

> Hi,
> In SQL Server 2005 it is very easy to set the execute rights to all
> procedures. See the script
> USE DBNAME
> GO
> /* CREATE A NEW ROLE */
> CREATE ROLE db_executor
> /* GRANT EXECUTE TO THE ROLE */
> GRANT EXECUTE TO db_executor
> Assign this role to the database user and he will be able to execute all
> procedures inside the databse
> THanks
> Hari
> SQL Server MVP
>
> "Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
> news:1BCFF9ED-12DC-4FD3-921F-3A1E185DB18E@.microsoft.com...
>
>|||Hi,
As far as I know you need to give EXEC privilages to indvidual stored
procedures.
sample script:-
Run this in the database, then copy the results to the query window
and execute.
select 'Grant EXEC on ' + name + ' to WhomEver'
from sysobjects
where type = 'P'
Thanks
Hari
SQL Server MVP
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:E57C2633-AE66-4837-8769-A33CB3C36CE1@.microsoft.com...[vbcol=seagreen]
> Hari -
> Hi. This is very helpful. Can I ask one more detail? Assuming that I've
> already created the database role, if I do "GRANT EXECUTE TO MyNewRole",
> will
> this grant execute permissions to ALL of the stored procedures in the
> database (including system stored procedures)? I don't really want to do
> that. Is there a way that I can have it grant execute only to stored procs
> that I created (dbo.<procname> )? Or can I do it by matching a pattern in
> the
> SP name? All of my stored procedures start with a special 3 character
> prefix.
> Can I use a "where" clause or something? And if I can, how?
> Let me know?
> Thanks!
> Alex
>
> "Hari Prasad" wrote:
>|||> Is there a way that I can have it grant execute only to stored procs
> that I created (dbo.<procname> )? Or can I do it by matching a pattern in
> the
> SP name? All of my stored procedures start with a special 3 character
> prefix.
> Can I use a "where" clause or something? And if I can, how?
One method is to generate a script to do this. The example below was
developed for SQL 2000 but works with SQL 2005 too.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO MyNewRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1 AND
ROUTINE_SCHEMA = N'dbo' AND
ROUTINE_NAME LIKE N'usp%'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:E57C2633-AE66-4837-8769-A33CB3C36CE1@.microsoft.com...[vbcol=seagreen]
> Hari -
> Hi. This is very helpful. Can I ask one more detail? Assuming that I've
> already created the database role, if I do "GRANT EXECUTE TO MyNewRole",
> will
> this grant execute permissions to ALL of the stored procedures in the
> database (including system stored procedures)? I don't really want to do
> that. Is there a way that I can have it grant execute only to stored procs
> that I created (dbo.<procname> )? Or can I do it by matching a pattern in
> the
> SP name? All of my stored procedures start with a special 3 character
> prefix.
> Can I use a "where" clause or something? And if I can, how?
> Let me know?
> Thanks!
> Alex
>
> "Hari Prasad" wrote:
>