Any way to query EXECUTE perms on stored procs?

I've recently been tasked with duplicating the permissions from one
account to another. We have a development, system test, and production
SQL Server, and approximately 35 databases in each. We use a fine
level of control on this particular account because it's what the
applications use to log in.
I've granted datareader/datawriter to the new account for all the dbs.
Each db, however, has a ton of stored procedures. Not all of them are
executable by the original account. I found a script that grants
access to all user stored procs, which is ok and will save me a few
days worth of point-and-click - but I'd be a happier DBA if I could
find a way to query the execute permissions on the stored procs to copy
them to the new account.
Any ideas? Below is the script I've used so far on the dev server.
SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin
with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs
Thanks,
Bill
A.K.A. PSPDBAThe PERMISSIONS system function can help you identify permission for the
current user:
http://msdn.microsoft.com/library/d...br />
6f78.asp
You could use it in combination with the SETUSER function:
http://msdn.microsoft.com/library/d...br />
6f78.asp
ML
http://milambda.blogspot.com/|||Ok, the sp_helprotect gives me back a list of the execute permissions
for the first user. I don't see how SETUSER is going to help me..|||PSPDBA (williambr@.state.pa.us) writes:
> Ok, the sp_helprotect gives me back a list of the execute permissions
> for the first user. I don't see how SETUSER is going to help me..
I guess the idea is:
SETUSER 'accountunderinvestigation'
go
SELECT name
FROM sysobjects
WHERE xtype = 'P'
AND permissions(id) & 32 = 1
go
SETUSER
That would list all the procedures that the account has permissions to
execute.
I should not that this solution contains three elements that are
deprecated in SQL 2005:
* The SETUSER command (Use EXECUTE AS instead)
* sysobjects (use sys.procedures instead)
* permissions(). (Use fn_my_permissions instead).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Any reason to avoid SQL Mail on a Production Server?

We are supporting a SQL Server 2000 (sp3) / ASP application.
The application is extremely poorly written (deadlock, timeout, stored
procs longer than my kids Christmas lists...) and I would like to have
SQL Mail installed to get various information emailed to us when one
of these events occur (through alert).
I meet stiff resistance from the Production guys but without proper
reason.
Before I put my head on the line... Is there any reason we should be
scared to install SQL Mail on a Production box?
One developer told me that it used to hang their production SQL
Server... (but that may have been SQL Server 6.5)
Thanks
EricEric,
> The application is extremely poorly written (deadlock, timeout, stored
> procs longer than my kids Christmas lists...) and I would like to have
> SQL Mail installed to get various information emailed to us when one
> of these events occur (through alert).
>
I would not necessarily view long stored proc as a bad thing. If the task
needs a long stored proc, so be it. It will perform better than breaking it
down to shorter ones.
> One developer told me that it used to hang their production SQL
> Server... (but that may have been SQL Server 6.5)
>
Though not scared, we did see some problem with Outlook:
1. When a process involves Outlook mail, and the mail server or client is
down, the process hangs (at least we saw this on SQL7).
2. Not whether a general problem or server specific: we saw that a SQL7
server's service be restarted by a too long mail recipient value.
We have the notifications you are considering on most of our servers. Just
mention this for your consideration to be prepared to talk to your
counterparts in other groups.
hth
Quentin|||Well, you'll need to run SQL under a domain user account -- not necessarily
a domain admin if you set all the appropriate permissions, but without a
domain user, there's no way to hook to an Exchange mailbox.
You'll also need to install a MAPI-compliant email program on the server
(preferably Outlook). Yet anothe application on a production server means
another point of hacking attacks.
In my experience, SQL Mail and SQL Agent Mail (they are two different
things) don't crash a server, but there are circumstances where they'll
stop working. If they try to send an email while the Exchange server is
offline or unreachable, that particular MAPI session can get messed up, but
remains in memory. You need to stop and start SQL Mail/SQL Agent Mail to
get it working. There are some ways around this like including a .pst file
in the profile you use for mail, but that too has issues.
Another option you may want to look into is Gert Drapers SMTP-based email
(www.sqldev.net). The only thing installed is an extended stored procedure,
it doesn't run into the problems MAPI can create, and you don't need to set
SQL to run under a specific account. The only limitation is that you can
use it for the integrated alert system.
Hope that helps.
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message
news:11269dcb.0307250628.35979849@.posting.google.com...
> We are supporting a SQL Server 2000 (sp3) / ASP application.
> The application is extremely poorly written (deadlock, timeout, stored
> procs longer than my kids Christmas lists...) and I would like to have
> SQL Mail installed to get various information emailed to us when one
> of these events occur (through alert).
> I meet stiff resistance from the Production guys but without proper
> reason.
> Before I put my head on the line... Is there any reason we should be
> scared to install SQL Mail on a Production box?
> One developer told me that it used to hang their production SQL
> Server... (but that may have been SQL Server 6.5)
>
> Thanks
>
> Eric|||> The only limitation is that you can
> use it for the integrated alert system.
I assume you meant "can't"...
To get around this limitation, I simply add a step to jobs I need alerts
for:
step 1
call some sql
if succeeds, quit with success
if fails, goto step 2
step 2
call xp_smtp_sendmail to send alert
-- so that the failure of step 1 is correctly reflected in sys:
if succeeds, quit with failure
if fails, quit with failure|||I think I'll try to get them to install Outlook.
Our SQL Server and SQL Agent services already run under domain
accounts.
Otherwise I know there are ActiveX objects available to send mail on
our production machine but it would be far less convenient than
xp_sendmail.
Thanks for your comments
Eric

ANSI_NULLS and null comparison

Hi All,

I'm converting some stored procs from Sybase to SQL Server, and I'm
having serious problems with Null comparisons.

When I run the code below on Sybase, it returns both rows. When I run
it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
the code (and on the session through Query Analyzer), but it doesn't
seem to make any difference. Am I missing something?

-----
set ANSI_NULLS off

drop table #TestNull

create table #TestNull (Field1 varchar(10), Field2 varchar(10))

insert into #TestNull values (1, null)
insert into #TestNull values (1,1)

declare @.TestVar varchar(10)

select @.TestVar = Null

select * from #TestNull where Field1 = @.TestVar
-----

Thanks in advance,
SaulYour SQL Server select should look like this:

SELECT * FROM #TextNull WHERE Field1 IS NULL

CJ
"Saul Margolis" <saul_margolis@.hotmail.com> wrote in message
news:c20aa081.0402171416.744303af@.posting.google.c om...
> Hi All,
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
>
> -----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----
>
> Thanks in advance,
> Saul|||"Saul Margolis" <saul_margolis@.hotmail.com> wrote in message
news:c20aa081.0402171416.744303af@.posting.google.c om...
> Hi All,
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
>
> -----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----
>
> Thanks in advance,
> Saul

I guess you mean WHERE Field2 = @.TestVar ? That will return the (1, NULL)
row.

See SET ANSI_NULLS in Books Online - there are a number of examples. By the
way, you should generally say which version of MSSQL you're using, as some
behaviour may vary - see the comments in Books Online.

Simon|||Saul Margolis (saul_margolis@.hotmail.com) writes:
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
-----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----

I fail to see how the batch above could return any rows with any
setting. Looks like there is a typo in your repro.

Anyway, first of all, try to write your code so that you can use
ANSI_NULLS ON. There are features in MS SQL Server that requires
ANSI_NULLS to be ON:

o Indexed views.
o Index on computed columns.
o Access to linked servers.

With ANSI_NULLS off, these features are unavailable.

Also, be aware of when writing stored procedures, that the setting
of ANSI_NULLS when you create the procedure applies. That is, not
the run-time setting.

Also, you may need to have a go for ANSI_NULLS to have effect.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The Sybase family used to allow "foobar = NULL" to mean "foobar IS
NULL"; you just need to set everything to ANSI and change all your code.
Also, why are you putting integers into VARCHAR(10) columns in your
sample code?

Now you know why experienced progrmamers laugh at newbies who use
proprietary code because they think they will never have to port it :)

That is probably not funny right now; and it might get worse if the
Sybase is old -- look for *= as the outer join and other non-standard
behavior in some of the predicates.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!