How can I retrieve domain username for group login?

The title says it all. Given that I have created a login for a domain group, and a database user for that login. What I want to do is retrieving the domain username for the active user. USER_NAME retrieves the database username, suser_name returns (of course) NULL as this is not a sql user.

The goal is to use domain group logins, while still allowing for logging what user performed which action.USER_NAME retrieves the database username, suser_name returns (of course) NULL as this is not a sql user.

Really?

When I query suser_sname() while logged in under a windows domain account (using a windows domain group login) I get my DOMAIN\UserName.

Regards,

hmscott

How can i restrict the SA user

hi,
Is there any way to restrict the SA access. The problem is that all my
client machine users knows the sa password, and also right now i don't want
to change the password. So i just want to know is there any way to restrict
their access ? thanking in advance
ARHi
SA is not restrictable as it is the highest built in security account.
If security is so important, you have to change the password. Remember, if
you are using integrated security, domain and local admins are by default,
mapped to SA.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Aneesh" <aneesh.r@.eostek.com> wrote in message
news:u4iPss30EHA.2608@.TK2MSFTNGP10.phx.gbl...
> hi,
> Is there any way to restrict the SA access. The problem is that all my
> client machine users knows the sa password, and also right now i don't
want
> to change the password. So i just want to know is there any way to
restrict
> their access ? thanking in advance
> AR
>|||"Aneesh" <aneesh.r@.eostek.com> wrote in message
news:u4iPss30EHA.2608@.TK2MSFTNGP10.phx.gbl...
> hi,
> Is there any way to restrict the SA access. The problem is that all my
> client machine users knows the sa password, and also right now i don't
want
> to change the password. So i just want to know is there any way to
restrict
> their access ? thanking in advance
> AR
Make a new user and give him an appropriate permissions.
regards,
Dra

How can I restore the db successfully

I'm a new user to SQL Server.
I use the Restore Database function in the Entreprise Manager to restore a database. But I have no idea about why it prompt error when I click OK and say :
Cannot find file ID 2 on device c:\Program File\Microsoft SQL Server\MSSQL\BACKUP\abc'. RESTORE DATABASE is terminating abnormally.
On the other hand, if I have a backup database file from someone, how can I restore it in the normal way.
1st Step: New a Database (no change to the setting)
2nd Step: Right click DB->Restore Database-> but how can I choose the backup database file that someone give me.?
Best regards,
Grace
Hi,
How can I choose the backup database file that someone give me.?
In the Restore databaase window -- Select the option button "From Device" ,
Click the "Select Device" -- Click "Add"
and in the file name get the .BAK file from the directory and click "OK".
There you select Backup Number "View contents" . This
will show you all the files associated with the backup file.
If you have mutiple files select the one you require and click "OK" to
restore.
Cannot find file ID 2 on device c: ?
I feel that this Backup is taken in mutiple files (Devices) and you have
only one file. In that case you cant restore till u get the other file.
Thanks
Hari
MCDBA
"Grace" <anonymous@.discussions.microsoft.com> wrote in message
news:17BBF6CC-AC73-44FE-A31D-AE0848ED7B83@.microsoft.com...
> I'm a new user to SQL Server.
> I use the Restore Database function in the Entreprise Manager to restore a
database. But I have no idea about why it prompt error when I click OK and
say :
> Cannot find file ID 2 on device c:\Program File\Microsoft SQL
Server\MSSQL\BACKUP\abc'. RESTORE DATABASE is terminating abnormally.
> On the other hand, if I have a backup database file from someone, how can
I restore it in the normal way.
> 1st Step: New a Database (no change to the setting)
> 2nd Step: Right click DB->Restore Database-> but how can I choose the
backup database file that someone give me.?
> Best regards,
> Grace
|||Grace,
The restore dialog in EM can use the backup history produced when you took the backup. But that doesn't
necessarily reflect what you actually have on the backup files. In this case, it seems like the backup history
say that there should be at least two backups on the backup file, and there isn't. Perhaps the backup file
doesn't exist at all...
If you want to restore a backup for a database which doesn't exists, *do not* create the database first. If
you do, you will most probably not create it with the same file structure as you had when you took the backup.
Let the restore process create the database for you. Just enter the restore dialog, type in the database name,
and select "from device". Here you can specify the backup file from which you want to do the restore.
I strongly suggest you read about how backup and restore work in Books Online, without that knowledge, you
might find above a bit difficult to understand. Also, I find it easier to work with the BACKUP and RESTORE
commands from Query Analyzer instead of using a GUI like EM. At least until you have good understanding about
the architecture of backup and restore and then understand more of how it work and what EM does when you
select options in the GUI. Just my opinion, that is...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Grace" <anonymous@.discussions.microsoft.com> wrote in message
news:17BBF6CC-AC73-44FE-A31D-AE0848ED7B83@.microsoft.com...
> I'm a new user to SQL Server.
> I use the Restore Database function in the Entreprise Manager to restore a database. But I have no idea
about why it prompt error when I click OK and say :
> Cannot find file ID 2 on device c:\Program File\Microsoft SQL Server\MSSQL\BACKUP\abc'. RESTORE DATABASE
is terminating abnormally.
> On the other hand, if I have a backup database file from someone, how can I restore it in the normal way.
> 1st Step: New a Database (no change to the setting)
> 2nd Step: Right click DB->Restore Database-> but how can I choose the backup database file that someone give
me.?
> Best regards,
> Grace
|||You are trying to restore from database, which uses the history of backups. The history path is different from the path of the file on the disk you are trying to restore from.
To restore from the file you have, you should click the bullet marked device (change from database) and browse to the actual file system file you are trying to restore the database to(via select devices... then Add...button).
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

how can i reserve a record to prevent anyone else from editing for X minutes?

Hey All,

I think this is something like locking, but not quite. I need to select a record in a database and display the information to the end user. The end user has a few minutes to review it before giving the ok at which point I will update the record.

However, while the user is reviewing the record, i dont want anyone else to be able to grab that record. I also want to make sure that if the user doesnt submit the page, that the record will be freed up and not indefinitely marked as taken.

What would be the best way to do this? This is with .net 2.0 and sql2000

I am afraid you want to prevent the following problem:

User A retrieves a record and it takes a long time before he submit and during that time User B updated that record so when user submit he will overwrite the record.

You can solve this problem by using the following way:

When a user submits a record you check the database to see if it has been updated by other users if so stop updating, display the updated record and ask him to try again.

Hope it helps.

|||

Thats not really an ideal solution since it means the user may fill out all their info, hit submit and then find out everything they submitted is now invalid.

However, i got some advice from the aspadvice.com ,mailing lists which seem like a good idea. I can add a date field to the table called LastLocked - and when i do my select, i only grab records where the lock is older then X minutes. I can also add a field LockedBy so that the user can access their own locked records. I think this will work for me.

thanks

How can i print a ssrs report from a aspx page without opening the report

Hello friends

How can i print a ssrs report from a aspx page without opening the report in viewer

I need to print the report when the user clicks on the print button

Thank you

Sara

Please.....Can somebody help.

Sara

|||You'll need to use the ssrs web service. I don't know the details, but it's in the MS book online.|||

Thanks dr_99 , I will look into it

regards Sara

How can I persist user credentials?

Hi,
I'm using SSRS 2005 on SQL Server 2000. I have 8 reports for a particular
user. Each report has its own data source. The way our security is set up,
I have to use the following configuration in the data source property sheet
for each report: Data Source=ServerName;Integrated Security=SSPI;Initial
Catalog=databaseName. Credentials stored securely in the report server. Use
as Windows credentials when connecting to the data source. I don't want the
user to have to enter their windows user name and password for each of the 8
reports. Does anyone know of a way to enter the credentials for the first
report and have them persist for the rest of the reports?
Thanks,
Jeff
--
Thanks,
JeffOn May 17, 6:07 pm, Jeff Stroope
<JeffStro...@.discussions.microsoft.com> wrote:
> Hi,
> I'm using SSRS 2005 on SQL Server 2000. I have 8 reports for a particular
> user. Each report has its own data source. The way our security is set up,
> I have to use the following configuration in the data source property sheet
> for each report: Data Source=ServerName;Integrated Security=SSPI;Initial
> Catalog=databaseName. Credentials stored securely in the report server. Use
> as Windows credentials when connecting to the data source. I don't want the
> user to have to enter their windows user name and password for each of the 8
> reports. Does anyone know of a way to enter the credentials for the first
> report and have them persist for the rest of the reports?
> Thanks,
> Jeff
> --
> Thanks,
> Jeff
This article might be helpful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_3d7q.asp?frame=true
Also, you should look into the Reporting Services configuration files
(usually located at: C:\Program Files\Microsoft SQL Server\MSSQL.
2\Reporting Services\ReportServer): web.config and
rsreportserver.config. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique, I'll give it a look.
--
Thanks,
Jeff
"EMartinez" wrote:
> On May 17, 6:07 pm, Jeff Stroope
> <JeffStro...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I'm using SSRS 2005 on SQL Server 2000. I have 8 reports for a particular
> > user. Each report has its own data source. The way our security is set up,
> > I have to use the following configuration in the data source property sheet
> > for each report: Data Source=ServerName;Integrated Security=SSPI;Initial
> > Catalog=databaseName. Credentials stored securely in the report server. Use
> > as Windows credentials when connecting to the data source. I don't want the
> > user to have to enter their windows user name and password for each of the 8
> > reports. Does anyone know of a way to enter the credentials for the first
> > report and have them persist for the rest of the reports?
> >
> > Thanks,
> >
> > Jeff
> > --
> > Thanks,
> >
> > Jeff
> This article might be helpful:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_3d7q.asp?frame=true
> Also, you should look into the Reporting Services configuration files
> (usually located at: C:\Program Files\Microsoft SQL Server\MSSQL.
> 2\Reporting Services\ReportServer): web.config and
> rsreportserver.config. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

How can I open hyperlink in a new window?

I have a number of dynamically configured hyperlinks in my report. Clicking
on them, takes user to a different application. I want for this new app to
open in a different browser window from the report itself, but instead it
opens in the same window and replaces my report data. Any ideas how to force
it to open in a new window?
Thank you!Copied from newsgroup:
You put it in the document map label. This is a standard thing for
hyperlinks. You can also use _self, _top, _parent, etc...
From Books Online,
"Advanced Textbox Properties (Navigation Tab)
Use the Navigation tab of the Advanced Textbox Properties dialog box to
define navigation options for the text box.
Options
Document map label
Type or select a label or an expression that evaluates to a label. Click the
expression (fx) button to edit the expression. The value of the label
appears on the document map."
HTH, Jens Suessmeyer,.
http://www.sqlserver2005.de
--
"Misha_SQL" <Misha_SQL@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
>I have a number of dynamically configured hyperlinks in my report.
>Clicking
> on them, takes user to a different application. I want for this new app
> to
> open in a different browser window from the report itself, but instead it
> opens in the same window and replaces my report data. Any ideas how to
> force
> it to open in a new window?
> Thank you!|||Some more links:
#1:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
#2: Take a look at _LinkTarget device info setting:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
Jens Suessmeyer.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:uv4rf7dRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Copied from newsgroup:
>
> You put it in the document map label. This is a standard thing for
> hyperlinks. You can also use _self, _top, _parent, etc...
> From Books Online,
> "Advanced Textbox Properties (Navigation Tab)
> Use the Navigation tab of the Advanced Textbox Properties dialog box to
> define navigation options for the text box.
> Options
> Document map label
> Type or select a label or an expression that evaluates to a label. Click
> the expression (fx) button to edit the expression. The value of the label
> appears on the document map."
>
> HTH, Jens Suessmeyer,.
>
>
> --
> http://www.sqlserver2005.de
> --
> "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
>>I have a number of dynamically configured hyperlinks in my report.
>>Clicking
>> on them, takes user to a different application. I want for this new app
>> to
>> open in a different browser window from the report itself, but instead it
>> opens in the same window and replaces my report data. Any ideas how to
>> force
>> it to open in a new window?
>> Thank you!
>|||For the following you need SP1 or greater installed.
= "javascript:void(window.open('http://www.google.com','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> I have a number of dynamically configured hyperlinks in my report.
Clicking
> on them, takes user to a different application. I want for this new app
to
> open in a different browser window from the report itself, but instead it
> opens in the same window and replaces my report data. Any ideas how to
force
> it to open in a new window?
> Thank you!|||Thank you, Jens!
These links contained information that solved my problem.
"Jens Sü�meyer" wrote:
> Some more links:
> #1:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
> #2: Take a look at _LinkTarget device info setting:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
>
> Jens Suessmeyer.
> "Jens Sü�meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
> im Newsbeitrag news:uv4rf7dRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> > Copied from newsgroup:
> >
> >
> > You put it in the document map label. This is a standard thing for
> > hyperlinks. You can also use _self, _top, _parent, etc...
> >
> > From Books Online,
> >
> > "Advanced Textbox Properties (Navigation Tab)
> > Use the Navigation tab of the Advanced Textbox Properties dialog box to
> > define navigation options for the text box.
> >
> > Options
> >
> > Document map label
> > Type or select a label or an expression that evaluates to a label. Click
> > the expression (fx) button to edit the expression. The value of the label
> > appears on the document map."
> >
> >
> >
> > HTH, Jens Suessmeyer,.
> >
> >
> >
> >
> >
> > --
> >
> > http://www.sqlserver2005.de
> >
> > --
> >
> > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> >>I have a number of dynamically configured hyperlinks in my report.
> >>Clicking
> >> on them, takes user to a different application. I want for this new app
> >> to
> >> open in a different browser window from the report itself, but instead it
> >> opens in the same window and replaces my report data. Any ideas how to
> >> force
> >> it to open in a new window?
> >>
> >> Thank you!
> >
> >
>
>|||Hi Bruce/Misha,
I tried the solution given below and it opens in the new window with the URL
given,even when i change it to "_Self".
Please note i am trying to open the url in a particular frame and not the
full window.
And also i see that "javascript:void(window.open
'http://www.google.com','_blank'))" gets pasted in the address bar of the IE
, removing all the frames and also the page is not loaded untill i press the
enter key.
Please help me find a solution to this.
Thanks in advance,
Regads,
Archana Kamath
"Bruce L-C [MVP]" wrote:
> For the following you need SP1 or greater installed.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > I have a number of dynamically configured hyperlinks in my report.
> Clicking
> > on them, takes user to a different application. I want for this new app
> to
> > open in a different browser window from the report itself, but instead it
> > opens in the same window and replaces my report data. Any ideas how to
> force
> > it to open in a new window?
> >
> > Thank you!
>
>|||Window.open create a new browser window. You want soemthign like:
(javascript)
Window.parent.FRAMENAME.src = 'http://www.google.com'
or
<a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
"Archana" <Archana@.discussions.microsoft.com> wrote in message
news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> Hi Bruce/Misha,
> I tried the solution given below and it opens in the new window with the
URL
> given,even when i change it to "_Self".
> Please note i am trying to open the url in a particular frame and not the
> full window.
> And also i see that "javascript:void(window.open
> 'http://www.google.com','_blank'))" gets pasted in the address bar of the
IE
> , removing all the frames and also the page is not loaded untill i press
the
> enter key.
> Please help me find a solution to this.
> Thanks in advance,
> Regads,
> Archana Kamath
>
> "Bruce L-C [MVP]" wrote:
> > For the following you need SP1 or greater installed.
> >
> > = "javascript:void(window.open('http://www.google.com','_blank'))"
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> >
> > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > I have a number of dynamically configured hyperlinks in my report.
> > Clicking
> > > on them, takes user to a different application. I want for this new
app
> > to
> > > open in a different browser window from the report itself, but instead
it
> > > opens in the same window and replaces my report data. Any ideas how
to
> > force
> > > it to open in a new window?
> > >
> > > Thank you!
> >
> >
> >|||Thanks Jim for a quick response.
But i am still facing the problem.
The problem is Reporting services report expression doesnot accept "<a href>".
And when i try to give the javascript,it doesnot get exceuted.What it simply
does is opens up a new browser and paste the javascript on the adress bar.It
doesnt not get executed untill we press the enter key.
In most of the solutions for such problems they have suggested to use the
javascript.But in my case, it doesnt get executed at all.
Do i need to set any internet settings so that javascript get executed with
out user pressing the enter key.
Please help me out with this issue.
Many Thanks,
Archana
"Jim Underwood" wrote:
> Window.open create a new browser window. You want soemthign like:
> (javascript)
> Window.parent.FRAMENAME.src = 'http://www.google.com'
> or
> <a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
> "Archana" <Archana@.discussions.microsoft.com> wrote in message
> news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > Hi Bruce/Misha,
> >
> > I tried the solution given below and it opens in the new window with the
> URL
> > given,even when i change it to "_Self".
> >
> > Please note i am trying to open the url in a particular frame and not the
> > full window.
> >
> > And also i see that "javascript:void(window.open
> > 'http://www.google.com','_blank'))" gets pasted in the address bar of the
> IE
> > , removing all the frames and also the page is not loaded untill i press
> the
> > enter key.
> >
> > Please help me find a solution to this.
> >
> > Thanks in advance,
> > Regads,
> > Archana Kamath
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > For the following you need SP1 or greater installed.
> > >
> > > = "javascript:void(window.open('http://www.google.com','_blank'))"
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > >
> > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > I have a number of dynamically configured hyperlinks in my report.
> > > Clicking
> > > > on them, takes user to a different application. I want for this new
> app
> > > to
> > > > open in a different browser window from the report itself, but instead
> it
> > > > opens in the same window and replaces my report data. Any ideas how
> to
> > > force
> > > > it to open in a new window?
> > > >
> > > > Thank you!
> > >
> > >
> > >
>
>|||Hi Jim,
Javascript works fine in the report.That is when i try opening the report
using the report manager /reportserver it works fine.When i click the link,
the page ('google') gets loaded.
But when i include the same report in my sharepoint site ,using the page
viewer webpart and try accessing the link,it throws up an error saying "Page
cannot be displayed" and the javascript gets pasted on the address bar.
Any idea why this behaviour?
Many thanks inadvance,
Regards,
Archana
"Archana" wrote:
> Thanks Jim for a quick response.
> But i am still facing the problem.
> The problem is Reporting services report expression doesnot accept "<a href>".
> And when i try to give the javascript,it doesnot get exceuted.What it simply
> does is opens up a new browser and paste the javascript on the adress bar.It
> doesnt not get executed untill we press the enter key.
> In most of the solutions for such problems they have suggested to use the
> javascript.But in my case, it doesnt get executed at all.
> Do i need to set any internet settings so that javascript get executed with
> out user pressing the enter key.
> Please help me out with this issue.
> Many Thanks,
> Archana
> "Jim Underwood" wrote:
> > Window.open create a new browser window. You want soemthign like:
> >
> > (javascript)
> > Window.parent.FRAMENAME.src = 'http://www.google.com'
> >
> > or
> > <a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
> >
> > "Archana" <Archana@.discussions.microsoft.com> wrote in message
> > news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > > Hi Bruce/Misha,
> > >
> > > I tried the solution given below and it opens in the new window with the
> > URL
> > > given,even when i change it to "_Self".
> > >
> > > Please note i am trying to open the url in a particular frame and not the
> > > full window.
> > >
> > > And also i see that "javascript:void(window.open
> > > 'http://www.google.com','_blank'))" gets pasted in the address bar of the
> > IE
> > > , removing all the frames and also the page is not loaded untill i press
> > the
> > > enter key.
> > >
> > > Please help me find a solution to this.
> > >
> > > Thanks in advance,
> > > Regads,
> > > Archana Kamath
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > For the following you need SP1 or greater installed.
> > > >
> > > > = "javascript:void(window.open('http://www.google.com','_blank'))"
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > >
> > > >
> > > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > > I have a number of dynamically configured hyperlinks in my report.
> > > > Clicking
> > > > > on them, takes user to a different application. I want for this new
> > app
> > > > to
> > > > > open in a different browser window from the report itself, but instead
> > it
> > > > > opens in the same window and replaces my report data. Any ideas how
> > to
> > > > force
> > > > > it to open in a new window?
> > > > >
> > > > > Thank you!
> > > >
> > > >
> > > >
> >
> >
> >|||HI Jim,
Thanks for your help.
The javascript works fine.There was some problem with mysharepoint page.
Anyways Many thanks
Regards,
Archana
"Archana" wrote:
> Hi Jim,
> Javascript works fine in the report.That is when i try opening the report
> using the report manager /reportserver it works fine.When i click the link,
> the page ('google') gets loaded.
> But when i include the same report in my sharepoint site ,using the page
> viewer webpart and try accessing the link,it throws up an error saying "Page
> cannot be displayed" and the javascript gets pasted on the address bar.
> Any idea why this behaviour?
> Many thanks inadvance,
> Regards,
> Archana
> "Archana" wrote:
> > Thanks Jim for a quick response.
> > But i am still facing the problem.
> > The problem is Reporting services report expression doesnot accept "<a href>".
> > And when i try to give the javascript,it doesnot get exceuted.What it simply
> > does is opens up a new browser and paste the javascript on the adress bar.It
> > doesnt not get executed untill we press the enter key.
> >
> > In most of the solutions for such problems they have suggested to use the
> > javascript.But in my case, it doesnt get executed at all.
> >
> > Do i need to set any internet settings so that javascript get executed with
> > out user pressing the enter key.
> >
> > Please help me out with this issue.
> >
> > Many Thanks,
> > Archana
> >
> > "Jim Underwood" wrote:
> >
> > > Window.open create a new browser window. You want soemthign like:
> > >
> > > (javascript)
> > > Window.parent.FRAMENAME.src = 'http://www.google.com'
> > >
> > > or
> > > <a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
> > >
> > > "Archana" <Archana@.discussions.microsoft.com> wrote in message
> > > news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > > > Hi Bruce/Misha,
> > > >
> > > > I tried the solution given below and it opens in the new window with the
> > > URL
> > > > given,even when i change it to "_Self".
> > > >
> > > > Please note i am trying to open the url in a particular frame and not the
> > > > full window.
> > > >
> > > > And also i see that "javascript:void(window.open
> > > > 'http://www.google.com','_blank'))" gets pasted in the address bar of the
> > > IE
> > > > , removing all the frames and also the page is not loaded untill i press
> > > the
> > > > enter key.
> > > >
> > > > Please help me find a solution to this.
> > > >
> > > > Thanks in advance,
> > > > Regads,
> > > > Archana Kamath
> > > >
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > For the following you need SP1 or greater installed.
> > > > >
> > > > > = "javascript:void(window.open('http://www.google.com','_blank'))"
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > >
> > > > >
> > > > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > > > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > > > I have a number of dynamically configured hyperlinks in my report.
> > > > > Clicking
> > > > > > on them, takes user to a different application. I want for this new
> > > app
> > > > > to
> > > > > > open in a different browser window from the report itself, but instead
> > > it
> > > > > > opens in the same window and replaces my report data. Any ideas how
> > > to
> > > > > force
> > > > > > it to open in a new window?
> > > > > >
> > > > > > Thank you!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >|||I am glad to hear you got it working.
"Archana" <Archana@.discussions.microsoft.com> wrote in message
news:95947B79-1C8F-4850-A528-231B95C82451@.microsoft.com...
> HI Jim,
> Thanks for your help.
> The javascript works fine.There was some problem with mysharepoint page.
> Anyways Many thanks
> Regards,
> Archana
> "Archana" wrote:
> > Hi Jim,
> >
> > Javascript works fine in the report.That is when i try opening the
report
> > using the report manager /reportserver it works fine.When i click the
link,
> > the page ('google') gets loaded.
> >
> > But when i include the same report in my sharepoint site ,using the page
> > viewer webpart and try accessing the link,it throws up an error saying
"Page
> > cannot be displayed" and the javascript gets pasted on the address bar.
> >
> > Any idea why this behaviour?
> > Many thanks inadvance,
> > Regards,
> > Archana
> >
> > "Archana" wrote:
> >
> > > Thanks Jim for a quick response.
> > > But i am still facing the problem.
> > > The problem is Reporting services report expression doesnot accept "<a
href>".
> > > And when i try to give the javascript,it doesnot get exceuted.What it
simply
> > > does is opens up a new browser and paste the javascript on the adress
bar.It
> > > doesnt not get executed untill we press the enter key.
> > >
> > > In most of the solutions for such problems they have suggested to use
the
> > > javascript.But in my case, it doesnt get executed at all.
> > >
> > > Do i need to set any internet settings so that javascript get executed
with
> > > out user pressing the enter key.
> > >
> > > Please help me out with this issue.
> > >
> > > Many Thanks,
> > > Archana
> > >
> > > "Jim Underwood" wrote:
> > >
> > > > Window.open create a new browser window. You want soemthign like:
> > > >
> > > > (javascript)
> > > > Window.parent.FRAMENAME.src = 'http://www.google.com'
> > > >
> > > > or
> > > > <a href='http://www.google.com' target='FRAMENAME'>Insert Text
Here</a>
> > > >
> > > > "Archana" <Archana@.discussions.microsoft.com> wrote in message
> > > > news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > > > > Hi Bruce/Misha,
> > > > >
> > > > > I tried the solution given below and it opens in the new window
with the
> > > > URL
> > > > > given,even when i change it to "_Self".
> > > > >
> > > > > Please note i am trying to open the url in a particular frame and
not the
> > > > > full window.
> > > > >
> > > > > And also i see that "javascript:void(window.open
> > > > > 'http://www.google.com','_blank'))" gets pasted in the address bar
of the
> > > > IE
> > > > > , removing all the frames and also the page is not loaded untill i
press
> > > > the
> > > > > enter key.
> > > > >
> > > > > Please help me find a solution to this.
> > > > >
> > > > > Thanks in advance,
> > > > > Regads,
> > > > > Archana Kamath
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > For the following you need SP1 or greater installed.
> > > > > >
> > > > > > ="javascript:void(window.open('http://www.google.com','_blank'))"
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in
message
> > > > > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > > > > I have a number of dynamically configured hyperlinks in my
report.
> > > > > > Clicking
> > > > > > > on them, takes user to a different application. I want for
this new
> > > > app
> > > > > > to
> > > > > > > open in a different browser window from the report itself, but
instead
> > > > it
> > > > > > > opens in the same window and replaces my report data. Any
ideas how
> > > > to
> > > > > > force
> > > > > > > it to open in a new window?
> > > > > > >
> > > > > > > Thank you!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >

How can i only get people which is brithday is today?

Why i got "SqlException was unhandled by user code" "{"Incorrect syntax near '12'."}" ?

How can i only get people which is brithday is today?

The "rc_brithday" datatype is datetime

Thanks you

Code is below:

cmdSelect = New SqlCommand("Select rc_email From recruiters WHERE rc_brithday = " & DateTime.Now(), conPubs)
conPubs.Open()
dtrTitles = cmdSelect.ExecuteReader()

himap:

Code is below:

cmdSelect = New SqlCommand("Select rc_email From recruiters WHERE rc_brithday = " & DateTime.Now(), conPubs)

Change the above line to - (I highlighted the single quote in red to make the line easier to read)

cmdSelect = New SqlCommand("Select rc_email From recruiters WHERE rc_brithday ='" & DateTime.Now() & "'", conPubs)

|||

hoopslife:

himap:

Code is below:

cmdSelect = New SqlCommand("Select rc_email From recruiters WHERE rc_brithday = " & DateTime.Now(), conPubs)

Change the above line to - (I highlighted the single quote in red to make the line easier to read)

cmdSelect = New SqlCommand("Select rc_email From recruiters WHERE rc_brithday ='" & DateTime.Now() & "'", conPubs)

I have no error now, but therc_brithdayinclude the time, so no any data is get from sql server.

How can i except the time that only focue the date find people birthday is today? THANKS YOU

i use follow statement is no error butno any data is get from sql server, why?:

cmdSelect = New SqlCommand("Select rc_email,rc_brithday From recruiters WHERE rc_brithday = '" & DateTime.Now().ToString("d") & "'", conPubs)

|||Use

DateTime.Now.ToShortDateString()

|||cmdSelect = New SqlCommand("Select rc_email,rc_birthday From recruiters WHERE rc_birthday>=dateadd(d,0,datediff(d,0,getdate())) AND rc_birthday<dateadd(d,1,datediff(d,0,getdate()))", conPubs) |||

Motley:

cmdSelect = New SqlCommand("Select rc_email,rc_birthday From recruiters WHERE rc_birthday>=dateadd(d,0,datediff(d,0,getdate())) AND rc_birthday<dateadd(d,1,datediff(d,0,getdate()))", conPubs)

this solution is work!! thanks you very much!!!

I also change to more simple solution, have same work result,

cmdSelect = New SqlCommand("Select rc_email,rc_birthday From recruiters WHERE rc_birthday>=dateadd(d,0,datediff(d,0,getdate())) AND rc_birthday<dateadd(d,1,datediff(d,0,getdate()))", conPubs)

or

cmdSelect = New SqlCommand("Select rc_email,rc_birthday From recruiters WHERE rc_birthday=dateadd(d,0,datediff(d,0,getdate()))", conPubs)

or

cmdSelect = New SqlCommand("Select rc_email,rc_birthday From recruiters WHERE rc_birthday=datediff(d,0,getdate())", conPubs)

How can I obtain the login name of the caller of my UDF written in a CLR language?

I am using MS SQL Server 2005.
Is there a way to obtain the login name of the caller of my user
defined function implement in managed C++?
I know I can get the information using the code segment below but I
want to avoid connecting back to the database for performance reasons.
String^ queryString = "select suser_sname()"
SqlConnection conn = gcnew SqlConnection(connectionString);
SqlCommand command = new SqlCommand(queryString, connection);
conn.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
reader[0];
}
Thanks,
StevenExamine using the Security object.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"smaully" <smauldin@.ingrian.com> wrote in message
news:1160176423.328986.327470@.i42g2000cwa.googlegroups.com...
>I am using MS SQL Server 2005.
> Is there a way to obtain the login name of the caller of my user
> defined function implement in managed C++?
> I know I can get the information using the code segment below but I
> want to avoid connecting back to the database for performance reasons.
> String^ queryString = "select suser_sname()"
> SqlConnection conn = gcnew SqlConnection(connectionString);
> SqlCommand command = new SqlCommand(queryString, connection);
> conn.Open();
> SqlDataReader reader => command.ExecuteReader(CommandBehavior.CloseConnection);
> while (reader.Read())
> {
> reader[0];
> }
> Thanks,
> Steven
>|||Darn Spell Checker.
Look into the SecurityPrinicipal object.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uBNLaka6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Examine using the Security object.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "smaully" <smauldin@.ingrian.com> wrote in message
> news:1160176423.328986.327470@.i42g2000cwa.googlegroups.com...
>>I am using MS SQL Server 2005.
>> Is there a way to obtain the login name of the caller of my user
>> defined function implement in managed C++?
>> I know I can get the information using the code segment below but I
>> want to avoid connecting back to the database for performance reasons.
>> String^ queryString = "select suser_sname()"
>> SqlConnection conn = gcnew SqlConnection(connectionString);
>> SqlCommand command = new SqlCommand(queryString, connection);
>> conn.Open();
>> SqlDataReader reader =>> command.ExecuteReader(CommandBehavior.CloseConnection);
>> while (reader.Read())
>> {
>> reader[0];
>> }
>> Thanks,
>> Steven
>|||Arnie,
That will return the Windows user not the sql login.
-Steve|||I read that that is what you desired; "obtain the login name of the caller".
There was nothing to indicate you were using SQL Logins.
Since that is know to and in SQL Server, and not known to the client
application, the only way is to ask SQL Server to provide it. That may
require a 'wasted' round trip. You might call a stored procedure, have the
stored procedure call the UDF, and pass the SQL Login back as a output
parameter.
Of course, I don't know how you are using the UDF, so that may not work for
you.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"smaully" <smauldin@.ingrian.com> wrote in message
news:1160415121.924478.80610@.m7g2000cwm.googlegroups.com...
> Arnie,
> That will return the Windows user not the sql login.
> -Steve
>

How Can i Now the RecordSource number of records?

Hi...

I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn′t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ?How i can khow the number of records of a RecordSource gets?... and ?How I can store in a variable a field value retrived for a RecordSource?... I′m using VWD and SQL Express that means ADO.NET 2.0...

cheers.

How are you binding to your datasource? A SqlDataSource, ObjectDataSource, or are you binding to the datasource in your code using a DataTable, DataReader, or DataSet?

How can I make reports and hide columns at runtime?

Hello. I did a report that has many many columns. I should give the user the
possibility to choose which columns to use with my asp.net program. Is that
possible to do? The only columns that must be shows are the ones that the
user has choosed.
Is that possible. ! !Please help me with links, code, books anythin
neccesary.Luis,
This is possible, simply create a boolean parameter for each column you want
to show/hide (e.g. ShowOfficeName) then select the column in the datagrid
and set the visibility | Hidden property to an expression of
= Not Parameters!ShowOfficeName.Value
Hope this helps,
Brian
"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in message
news:OLwPSwAeFHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hello. I did a report that has many many columns. I should give the user
> the
> possibility to choose which columns to use with my asp.net program. Is
> that
> possible to do? The only columns that must be shows are the ones that the
> user has choosed.
> Is that possible. ! !Please help me with links, code, books anythin
> neccesary.
>

How can I make reports and hide columns at run time with my aspnet app?

Hello. I did a report that has many many columns. I should give the user the
possibility to choose which columns to use with my asp.net program. Is that
possible to do? The only columns that must be shows are the ones that the
user has choosed.

Is that possible. ! !Please help me with links, code, books anythin
neccesary.You can parameterize the report and set the visibilty property on the column to be dependent on the parameter value. If you want to hide mlutiple columns, you will need multiple parmeters or, in SQL 2005, you can have a multivalued parameted. The only downside to this approach is that the body will not shrink with the hidden columns. This can cause problems when printing / exporting the report.

How can I lock and release table

How can I lock and release table
I need to assign user table to acess table only one time and after access
the table will lock
and how to release the table after lock
Thank you for all thinking shareIt's hard to suggest something without more info. Take a look at lock hints
in the BOL
"Pure_haven" <u32445@.uwe> wrote in message news:6f1b82bbe0020@.uwe...
> How can I lock and release table
> I need to assign user table to acess table only one time and after
> access
> the table will lock
> and how to release the table after lock
> Thank you for all thinking share
>

How can I know who inserted a certain record?

Dears,
I have either a service or even a user who keeps adding records to a certain table. Is there any way to know who added the records? Other than using the SQL Profiler.
Thanks,To collect statistical information about users, use either SQL Profiler or System Monitor (Windows NT Performance Monitor in Windows NT 4.0). Use the SQL Server Enterprise Manager Current Activity window to perform ad hoc monitoring of SQL Server, which allows you to determine user activity on the system.

Also, type this into the Books Online Search "Monitoring with SQL Server Enterprise Manager"

Also, is there a lock for the INSERT?

Hope it helps...|||Just add a column with as default User

how can i know which user is locking a table

how can i know which user is locking a tablesp_who2
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Win" <aaa@.aaa.com> wrote in message
news:uKxIs4vpFHA.3552@.TK2MSFTNGP10.phx.gbl...
>
>|||It just show the database
I want to know which table
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:#UQ836vpFHA.2472@.tk2msftngp13.phx.gbl...
> sp_who2
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Win" <aaa@.aaa.com> wrote in message
> news:uKxIs4vpFHA.3552@.TK2MSFTNGP10.phx.gbl...
>|||and then using sp_locks <spid> you could see what objects are locked.
select object_id()
"Roji. P. Thomas" wrote:

> sp_who2
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Win" <aaa@.aaa.com> wrote in message
> news:uKxIs4vpFHA.3552@.TK2MSFTNGP10.phx.gbl...
>
>|||Look for the spid column in the results of the both
sp_lock and sp_who2.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Win" <aaa@.aaa.com> wrote in message
news:%23KuGJ9vpFHA.2156@.TK2MSFTNGP14.phx.gbl...
> It just show the database
> I want to know which table
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:#UQ836vpFHA.2472@.tk2msftngp13.phx.gbl...
>

How Can i know how many USERS are in agroup.

HI all
I just wana know that is there is any TSQL Statement which will give me
the list of all user groups,names of user in each group and thier
Permssions in any database/server.
Actually I have to create a report in wich i have to show that how
many groups we have any whi is the member of wich group with such
permissions.I ckeck through Interprise Manager but i guess it will take
alot of time to note every user group then users and their permissions.
So please any HELP will be greatly Appriciated.

Thanks

Arman(ch.adilaziz@.gmail.com) writes:

Quote:

Originally Posted by

I just wana know that is there is any TSQL Statement which will give me
the list of all user groups,names of user in each group and thier
Permssions in any database/server.
Actually I have to create a report in wich i have to show that how
many groups we have any whi is the member of wich group with such
permissions.I ckeck through Interprise Manager but i guess it will take
alot of time to note every user group then users and their permissions.
So please any HELP will be greatly Appriciated.

Before I try anything, I would like some clarification. With "groups" do
you mean what is normally called "roles" in SQL Server? That is grouping
of users within a database?

Or do you mean Windows groups as in DOMAIN\OFFICEUSERS?

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Before I try anything, I would like some clarification. With "groups" do
you mean what is normally called "roles" in SQL Server? That is grouping
of users within a database?
>
Or do you mean Windows groups as in DOMAIN\OFFICEUSERS?

Even if Arman meant roles, I'd like to ask you similar question
according to Windows groups. Is it possible to list Windows group
members using T-SQL? Let's assume that I'm a sysadmin, but not domain
admin.

--
Best regards,
Marcin Guzowski
http://guzowski.info|||Marcin A. Guzowski wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

Before I try anything, I would like some clarification. With "groups" do
you mean what is normally called "roles" in SQL Server? That is grouping
of users within a database?

Question #1:

Answer : Yes

sp_helpuser

Quote:

Originally Posted by

Quote:

Originally Posted by

Or do you mean Windows groups as in DOMAIN\OFFICEUSERS?

>
>
Even if Arman meant roles, I'd like to ask you similar question
according to Windows groups. Is it possible to list Windows group
members using T-SQL? Let's assume that I'm a sysadmin, but not domain
admin.
>
>

Question #2:

Answer: Yes, set up a linked server with your pdc server / active
directory and use open query to query this information from the linked
server.

http://www.fits-consulting.de/blog/...2e73b00faa.aspx

Quote:

Originally Posted by

--
Best regards,
Marcin Guzowski
http://guzowski.info

|||jebuskrust@.gmail.com wrote:

Quote:

Originally Posted by

Question #2:
>
Answer: Yes, set up a linked server with your pdc server / active
directory and use open query to query this information from the linked
server.
>
http://www.fits-consulting.de/blog/...2e73b00faa.aspx

I found much easier way:
exec master.dbo.xp_logininfo 'DOMAIN\GROUP','members'

It will list all members of desired Windows group*, without the need
of setting up a linked server.
[*] only groups granted access to SQL Server are concerned

--
Best regards,
Marcin Guzowski
http://guzowski.info

How can I know a user table's size?

I want to check a user table's size, Maybe 20M or more?
what can i do ,pls kindly help me
Regards
Grantexec sp_spaceused ThisTable, @.updateusage = TRUE
Roy Harvey
Beacon Falls, CT
On Wed, 26 Apr 2006 19:41:02 -0700, Grant
<Grant@.discussions.microsoft.com> wrote:

>I want to check a user table's size, Maybe 20M or more?
>what can i do ,pls kindly help me
>
>Regards
>Grant|||try this (untested)
EXEC sp_spaceused [table1]
"Grant" wrote:

> I want to check a user table's size, Maybe 20M or more?
> what can i do ,pls kindly help me
>
> Regards
> Grant
>

how can I insert a semicolon as user data?

I'm working with an existing program that crafts an insert statementfrom user input. The data is machine generated, though, and someof it has semicolons. How can I "escape" those semicolons so thatI can insert them into the database? Does the ESCAPE key workwith the insert statement, too? Can I just replace the ";" withsome other character(s) to escape it? I'm not finding much in theonline help. Thanks!
If by "craft" you mean concatenating strings to create a SQL statement...you should not do that.
If you build a parameterized sql statement, then the semi-colons would not be an issue.|||ie, you mean use a stored procedure, right? yes, they're usingjust a "crafted sql statement right now - "insert into blah blah blah"and then filling in the parameters. its a console app inc#. I was going to try and just do a replace on the insertstatement, but now i think i might just go and redo the whole thing asa stored procedure. but if I were going to replace the ";" withinan insert statement, how would that work, or can't it be done?
|||Can you post how your INSERT statement looks like. This will give a better idea what you are planning to do!
|||If the semi-colon is properly quoted, it should be ok. It's usually the quotes that get you when you concatenate.
As far as creating a stored-proc goes, you can execute parameterized sql statements without taking it all the way to a stored-proc.
for example:
"UPDATE someTable SET someField=@.p1"
Then when you create the command object, you simple need to add a parameter named @.p1 and give it a value.
Since @.p1 is a parameter, even if its string data, it does not need to be quoted.
With parameterized sql, you get the benefit of ad-hoc queries with the safety (from sql injection) of a stored proc.
Also, when parameterized sql statements are sent to sql server, the execution plan can be cached improving performance.
For example: "SELECT someFields FROM someTable WHERE someId=@.p1"
In the previous statement, the same execution plan would be reused if you needed to issue that query multiple times for multiple values of someId.
This - on the other hand: "SELECT someFields FROM someTable WHERE someId=5"
would only get reused if you keep querying for someId=5

Note: The Microsoft DAAB (Data Access Application Block) can be especially usefull for issuing queries.
|||Actually, a parameterized statement was what was being done originally,so I'm not sure now. I changed things to an actual storedprocedure, but the original problem is still occurring, so maybe itsnot the semicolon after all. The error I'm getting is
"System.IndexOutOfRangeException: Index was outside the bounds of the array.
at SmatFileReader.SmatReader.Main(String[] args)"
I'm not fully sure what that means. I'm trying to debug it now...any ideas?

How can I hide mssql server system database?

Dear All,

I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.

Regards,

Ricky

You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu

|||

Hi Laurentiu

Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.

Regards,

Ricky

|||

Hello,

In order to see who all have access to 'View Any Database' Permission, you can use the following query

SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;

and to DENY access to public you can use the following syntax

DENY VIEW ANY DATABASE TO PUBLIC

|||

The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.

If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.

If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:

select name from sys.databases where has_dbaccess(name) = 1

Thanks
Laurentiu

|||

I have the opposite problem. When using SSMS on the server itself, I cannot see the system databases tree at all. I tet only "Database Snapshots" and my own database.

However, when accessing this server over a VPN connection, I can see and access the system databases.

I am a Sysadmin and would expect to be able to see everything.

And since the server is running Windows Authentication only, I would expect that my credential are the same, no matter where I login from.

My first guess is that I inadvertantly hid the System Database tree in SSMS, but I see no way to do/undo anything remotely like that.

Per your comment, I remain confused as to how to make them visible. Note that the databases are listed in various dialogs where one would normally select a database (Maint Wizard, for example).

"You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu"

|||

Can you see the database entries when you query sys.databases? If the catalogs show you the correct information, but SSMS does not, then the issue should be pursued on the Tools forum.

Thanks

Laurentiu

|||

Yes, I can see master, tempdb, model and msdb via "select * from sys.databases".

Will re-post in the Tools forum, thanks.

How can I hide mssql server system database?

Dear All,

I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.

Regards,

Ricky

You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu

|||

Hi Laurentiu

Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.

Regards,

Ricky

|||

Hello,

In order to see who all have access to 'View Any Database' Permission, you can use the following query

SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;

and to DENY access to public you can use the following syntax

DENY VIEW ANY DATABASE TO PUBLIC

|||

The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.

If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.

If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:

select name from sys.databases where has_dbaccess(name) = 1

Thanks
Laurentiu