How to script Sprocs permissions in Management Studio?

I am dissapointed that script options are missing in Management Studio. I
never understand why a good feature goes away in a newer version.
Is there any way to script the permissions for stored procedures?
John Dalberg
Depends on how you script them. If you select the stored
procedure and select "Script Stored Procedure" you won't get
the permissions. If you go from the database level, Tasks
and Generate SQL Scripts (or whatever other ways there are
to invoke the Generate SQL Server Scripts Wizard, you have
more options in the scripting. If you script the stored
procedures for the database and select to include object
level permissions, the grants will be scripted with the
stored procedures.
-Sue
On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
Dalberg) wrote:

>I am dissapointed that script options are missing in Management Studio. I
>never understand why a good feature goes away in a newer version.
>Is there any way to script the permissions for stored procedures?
>John Dalberg
|||Sue Hoegemeier <Sue_H@.nomail.please> wrote:
> Depends on how you script them. If you select the stored
> procedure and select "Script Stored Procedure" you won't get
> the permissions. If you go from the database level, Tasks
> and Generate SQL Scripts (or whatever other ways there are
> to invoke the Generate SQL Server Scripts Wizard, you have
> more options in the scripting. If you script the stored
> procedures for the database and select to include object
> level permissions, the grants will be scripted with the
> stored procedures.
Not in SQL Server 2005. I found out later that it can be done at the
database level. Still not as convenient as pre 2005.
John Dalberg
[vbcol=seagreen]
> On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
> Dalberg) wrote:
|||Yes...what I wrote was for SQL Server 2005. And I explained
how to do it at the database level in SQL Server 2005.
That's what I wrote from what I can see..."If you go from
the database level..."
-Sue
On 13 Mar 2006 15:36:47 GMT, nospam@.nospam.sss (John
Dalberg) wrote:
[vbcol=seagreen]
>Sue Hoegemeier <Sue_H@.nomail.please> wrote:
>
>Not in SQL Server 2005. I found out later that it can be done at the
>database level. Still not as convenient as pre 2005.
>John Dalberg
>
>
>

How to script Sprocs permissions in Management Studio?

I am dissapointed that script options are missing in Management Studio. I
never understand why a good feature goes away in a newer version.
Is there any way to script the permissions for stored procedures?
John DalbergDepends on how you script them. If you select the stored
procedure and select "Script Stored Procedure" you won't get
the permissions. If you go from the database level, Tasks
and Generate SQL Scripts (or whatever other ways there are
to invoke the Generate SQL Server Scripts Wizard, you have
more options in the scripting. If you script the stored
procedures for the database and select to include object
level permissions, the grants will be scripted with the
stored procedures.
-Sue
On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
Dalberg) wrote:
>I am dissapointed that script options are missing in Management Studio. I
>never understand why a good feature goes away in a newer version.
>Is there any way to script the permissions for stored procedures?
>John Dalberg|||Sue Hoegemeier <Sue_H@.nomail.please> wrote:
> Depends on how you script them. If you select the stored
> procedure and select "Script Stored Procedure" you won't get
> the permissions. If you go from the database level, Tasks
> and Generate SQL Scripts (or whatever other ways there are
> to invoke the Generate SQL Server Scripts Wizard, you have
> more options in the scripting. If you script the stored
> procedures for the database and select to include object
> level permissions, the grants will be scripted with the
> stored procedures.
Not in SQL Server 2005. I found out later that it can be done at the
database level. Still not as convenient as pre 2005.
John Dalberg
> On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
> Dalberg) wrote:
> >I am dissapointed that script options are missing in Management Studio.
> >I never understand why a good feature goes away in a newer version.
> >
> >Is there any way to script the permissions for stored procedures?
> >
> >John Dalberg|||Yes...what I wrote was for SQL Server 2005. And I explained
how to do it at the database level in SQL Server 2005.
That's what I wrote from what I can see..."If you go from
the database level..."
-Sue
On 13 Mar 2006 15:36:47 GMT, nospam@.nospam.sss (John
Dalberg) wrote:
>Sue Hoegemeier <Sue_H@.nomail.please> wrote:
>> Depends on how you script them. If you select the stored
>> procedure and select "Script Stored Procedure" you won't get
>> the permissions. If you go from the database level, Tasks
>> and Generate SQL Scripts (or whatever other ways there are
>> to invoke the Generate SQL Server Scripts Wizard, you have
>> more options in the scripting. If you script the stored
>> procedures for the database and select to include object
>> level permissions, the grants will be scripted with the
>> stored procedures.
>
>Not in SQL Server 2005. I found out later that it can be done at the
>database level. Still not as convenient as pre 2005.
>John Dalberg
>
>
>
>> On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
>> Dalberg) wrote:
>> >I am dissapointed that script options are missing in Management Studio.
>> >I never understand why a good feature goes away in a newer version.
>> >
>> >Is there any way to script the permissions for stored procedures?
>> >
>> >John Dalberg

How to script Sprocs permissions in Management Studio?

I am dissapointed that script options are missing in Management Studio. I
never understand why a good feature goes away in a newer version.
Is there any way to script the permissions for stored procedures?
John DalbergDepends on how you script them. If you select the stored
procedure and select "Script Stored Procedure" you won't get
the permissions. If you go from the database level, Tasks
and Generate SQL Scripts (or whatever other ways there are
to invoke the Generate SQL Server Scripts Wizard, you have
more options in the scripting. If you script the stored
procedures for the database and select to include object
level permissions, the grants will be scripted with the
stored procedures.
-Sue
On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
Dalberg) wrote:

>I am dissapointed that script options are missing in Management Studio. I
>never understand why a good feature goes away in a newer version.
>Is there any way to script the permissions for stored procedures?
>John Dalberg|||Sue Hoegemeier <Sue_H@.nomail.please> wrote:
> Depends on how you script them. If you select the stored
> procedure and select "Script Stored Procedure" you won't get
> the permissions. If you go from the database level, Tasks
> and Generate SQL Scripts (or whatever other ways there are
> to invoke the Generate SQL Server Scripts Wizard, you have
> more options in the scripting. If you script the stored
> procedures for the database and select to include object
> level permissions, the grants will be scripted with the
> stored procedures.
Not in SQL Server 2005. I found out later that it can be done at the
database level. Still not as convenient as pre 2005.
John Dalberg
[vbcol=seagreen]
> On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
> Dalberg) wrote:
>|||Yes...what I wrote was for SQL Server 2005. And I explained
how to do it at the database level in SQL Server 2005.
That's what I wrote from what I can see..."If you go from
the database level..."
-Sue
On 13 Mar 2006 15:36:47 GMT, nospam@.nospam.sss (John
Dalberg) wrote:
[vbcol=seagreen]
>Sue Hoegemeier <Sue_H@.nomail.please> wrote:
>
>Not in SQL Server 2005. I found out later that it can be done at the
>database level. Still not as convenient as pre 2005.
>John Dalberg
>
>
>
>sql

How to return all records when date filter parameter is missing

I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date

I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@.last, only the records where the last name is 'Smith' will be returned if @.last = 'Smith', but all records are returned is @.last = "". But that's not how it's working with the date.

I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of

select * from table where (MONTH([AD ENDS]) = @.month)

MONTH(datefield) always returns an integer from 1 to 12. If @.month is empty, I want all the records to be displayed, but nothing is. If @.month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not?

Diane

HI Diane,

It think there are two options that might be better than having two objectdatasources.

1:change the select statement programmatically
strSQL = "select * from table ";
if (strMyMonth == string.empty)
{ strSQL+= "where (MONTH([AD ENDS]) = @.month)"}
else
{srSQL += "where (MONTH([AD ENDS]) > Date('01/01/0001')}

2:use the databases own If Then Else capabilities (this is dependent on your DB.. I know oracle, sqlserver and even access allows this)

Access Example: If [targetgoal] <> 0 then ([Total Of $FeeBilled]/6)*12)/[TargetGoal] else [targetgoal]= 0

|||I don't know how you can get all records without send anything in with Select * from table wherelast=@.last

But I would try to use Select * from table where last=ISNULL(@.last,last).

For the month query,please try something like this:

WHEREMONTH(tDate)=ISNULL(@.month,MONTH(tDate))

|||

Yup, those are much better options. I forgot about changing the select statement programmatically. Been at this too many hours I guess. I like the idea of using the database's capabilities though. If my current select statement is select * from table where (MONTH([AD ENDS]) = @.month) and I'm using SQL Server 2005, what would the new select statement be?

select * from table where ( If @.month <> 0 then (MONTH([AD ENDS]) = @.month) )?

or

select * from table ( If @.month <> 0 then (whereMONTH([AD ENDS]) = @.month) )?

Diane

|||SELECT * FROM Table WHERE MONTH([AD ENDS]) = ISNULL(@.month, MONTH([AD ENDS])|||

Thank you Dinakar! That worked!

Linmo, in a strongly typed dataset (which are pretty much all I know in .NET so far), if an empty parameter is returned, the filter for that parameter isn't set. This behavior is a life saver IMHO. It kust had me stumped this time around.

Diane

How to retrive a value using a query string?

Hello,

I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.

This is what I have:

<script runat="server"> Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Dim conn As SqlConnection Dim cmd As SqlCommand Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @.Password) AND (users.username = @.Username)" conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance") cmd = New SqlCommand(cmdString, conn) cmd.Parameters.Add("@.Username", SqlDbType.VarChar, 50) cmd.Parameters("@.Username").Value = Me.Login1.UserName cmd.Parameters.Add("@.Password", SqlDbType.VarChar, 50) cmd.Parameters("@.Password").Value = Me.Login1.Password conn.Open() Dim myReader As SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) If myReader.Read() Then FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False) Else 'Response.Write("Invalid credentials") End If myReader.Close() End Sub</script>

I would like to know how can I get now the "user.FirstName" and pass it to a session variable?
how should I code it?

thanks,

Inside your read have

Session["FirstName"]=myReader.getString(2)

Is there any reason that you're getting 7(I think) fields from the database and throwing them away?

Also, it's poor form to store unencrypted passwords in the database. At least use xor or something.