Defaulting the User's ID in a column

Hello,

I'm new to SQL Server and I'm wondering how I can default the User's Id in to a column when they create/modify a record.

Better yet, is there a way to get a list of allowable functions for the Default Parm?

thx so much.

Use the system_user.

ALTER TABLE MyTable
ADD ChangeUser varchar(100) DEFAULT system_user

Most of the system functions are available as DEFAULTS. Check Books Online.

|||

if you want to know the database username then system_user will not be the one. sometimes, you can have many database user names mapped to single login. try this and understand.

drop table TestuserDefault

create table TestuserDefault (userid int)

ALTER TABLE TestuserDefault

ADD systemuser varchar(100) DEFAULT system_user

ALTER TABLE TestuserDefault

ADD dbuser varchar(100) DEFAULT current_user

ALTER TABLE TestuserDefault

ADD dbuser1 varchar(100) DEFAULT user

ALTER TABLE TestuserDefault

ADD dbuser2 varchar(100) DEFAULT user_name()

insert into TestuserDefault(userid) select 1

select *from TestuserDefault

Madhu

|||

And of course, if you have multiple users mapped to the same login, there is no way that SQL Server can provide you the User identification information -you will have to do that in the application code, that is, if your application even tracks UserID information.

However, in those situations where IntegratedSecurity is being used, SYSTEM_USER will provide the correct UserName.

Defaulting the export option

Is there a way to control the contents of the report export drop down
list? Specifically, I would like to set a default export to PDF
(instead of the "Select a format" entry)You can force the rendering format for that report but I don't think there is
a way to default the drop-down selection.
Here is a PDF render format against the AdventureWorks sample reports (the
report gets generated in PDF):
//localhost/ReportServer?/AdventureWorks Sample Reports/Company
Sales&rs:Format=PDF&rs:Command=Render
"Paul" wrote:
> Is there a way to control the contents of the report export drop down
> list? Specifically, I would like to set a default export to PDF
> (instead of the "Select a format" entry)

Defaulting Start and End dates to the previous month.

I have two parameters in my report (StartDate and EndDate). I want to default these parameters to the previous month.

For example... If today is 5/17/2007, I want StartDate to be 4/1/2007 and EndDate to be 4/30/2007. If today would be January 30th 2007, I would want StartDate to be 12/1/2006 and EndDate to be 12/31/2006.

How can I do this?

Nevermind... I used the following two functions. However, if you know of a better way to do this, please let me know.

Public Function EndDt() As Date

Dim Today As Date = Date.Today

Dim Day As Integer = Today.Day

Dim EndDate As Date

EndDate = Today.AddMonths(1)

EndDate = Today.AddDays(-Day)

Return EndDate

End Function

Public Function StartDt() As Date

Dim Today As Date = Date.Today

Dim Day As Integer = Today.Day - 1

Dim StartDate As Date

StartDate = Today.AddMonths(-1)

StartDate = Today.AddDays(-Day)

Return StartDate

End Function

|||

I can't think of a quick direct way

but I do want to suggest using BETWEEN, and first of month (so that you don't have to worry about # of days in a month)

e.g. for 05/17/2007

you'd want SQL code to be

Code Snippet

column BETWEEN 04/01/2007 AND 05/01/2007

since BETWEEN is inclusive, but only up to '05/01/2007 00:00:00', anything after 05/01/2007 midnight will NOT show up

so in backend SQL

it'll be like

Code Snippet

SELECT
previous_month = CAST( MONTH(getdate())-1 AS varchar) + '/01/' + CAST(YEAR(getdate()) AS varchar)
, current_month = CAST( MONTH(getdate()) AS varchar) + '/01/' + CAST(YEAR(getdate()) AS varchar)

then you get

previous_month current_month

4/01/2007 5/01/2007

Defaulting Parameters based on time of day?

I need to set up some parameters which are based on the time of day. For instance, night shift vs/ day shift. If I run the report during the day, default to the day shift criteria (5 am to 5 pm), and likewise for the night shift (5 pm to 5 am). On the night shift, I also need to accomodate for the change in dates as well. Is this even possible in a single report? HELP!!

Thanks!

Does it really need to be parameters, or could you just check the time in your SQL or report code?

defaulting newly created objects to DBO

Hi, i know that for non-sysadmin role members, non-qualified objects
will be owned by the creating user.
Is it possible to change this so that objects created by users
belonging to database role 'db_owner' default to dbo?
Thanks,
RafetJust have the CREATE statement use dbo as the schema.
create table dbo.test
Randy Dyess
www.Database-Security.Info|||Not really. You could use sp_addalias but it's not
recommended to go this route and sp_addalias is provided for
backwards compatibility only.
Members of db_owner should qualify the objects they are
creating with dbo. It's considered good practice to always
qualify objects with the owner name - when creating or
referencing objects. Qualifying objects improves performance
and readability.
-Sue
On 5 Mar 2004 07:16:48 -0800, rducic@.hotmail.com (Rafet)
wrote:

>Hi, i know that for non-sysadmin role members, non-qualified objects
>will be owned by the creating user.
>Is it possible to change this so that objects created by users
>belonging to database role 'db_owner' default to dbo?
>Thanks,
>Rafet|||I am puzzled about this too. I am about to start using sp_addalias though.
I think Microsoft needs to think this area through a little better. I know
sp_addalias may go away some day, but the current version doesn't handle thi
s well.
In test, we like to give developers logins that link to the dbo user so that
we can have the system enforce all objects getting created by dbo. Then wh
en we create these in production we can be comfortable that the user is the
same and any code that refe
rences the user will always be "dbo" and not "joe_developer".
While it is best practice for developers to always reference user name with
objects, in practice it is harder to enforce without the system to do it for
us. Our company employs consultants regularly and they all seem to have di
fferent habits we end up ha
ving to work on with them.

Defaulting Date Parameter

Hello,

I have a report parameter StartDate. Properties are

DataTypeataTime

Prompt: StartDate

Default Values:

Non Queried : =NOW()

I set the default value to Now(). When I go to preview, the StartDate parameter is blank and its been locked & grayed out. I also tried

Today() and Globals!SystemTime but that does not work either. Is there any other solution to make this work?

Thanks

Raj

I have not seen any responses on this. Wanted to see if there are any ideas/ thoughts on this.|||

Try:

Code Snippet

=cDate(FormatDateTime(Now, DateFormat.ShortDate))

Larry|||HI,
You can give the following expression for the default value of startdate;
DateValue(now()).

Cheers,
Shri|||

Is this the only parameter in your report? If it is, then you shouldn't be having any problems.

If not, try entering the values for all the parameters that come before StartDate. Then you should be able to see the default value & it should not be greyed out any more.

-Aayush