I have a user defined function in SQL Server 2000 that splits a Name field into First, Last, Middle names. Originally I utilized the CHARINDEX function to find the spaces in the string. Since I then found out that function is by definition Non-deterministic that didn't work for me, so I re-wrote the function a bit differently.
I am using the function as a field's formula in my table structure. I need the ability to create an index on that field so need the function to be deterministic. Apparently there is still something about how I designed the function that causes it to be non-deterministic. The function code is below; if anyone can help point me in the right direction it'd be greatly appreciated.
ALTER function fSplitName_test
(
@.name varchar(100),
@.fname int)
returns varchar(55)
as
begin
declare @.Fname_position int, @.Lname_position int, @.Mname_position int, @.SplitName varchar(55), @.iint int, @.iLen int,
@.sChar char(1), @.sName varchar(55)
set @.name = ltrim(replace(@.name, ' JR', ''))
set @.sName = @.name
set @.ilen = len(rtrim(@.name))
set @.Fname_position = 0
set @.Lname_position = 0
set @.iint = 0
while @.iint <> @.ilen
begin
set @.schar = left(@.sName, 1)
begin
if @.schar = ' '
begin
if @.Fname_position = 0
set @.Fname_position = @.iint
else
set @.Lname_position = @.iint
end
set @.iint = @.iint + 1
set @.sName = right(@.sName, @.ilen - @.iint)
end
end
if @.fname = 1
begin
if @.fname_position <> 0
set @.SplitName = left(@.name, @.Fname_position)
else
set @.splitname = 'unkn'
end
else
begin
if @.fname = 2
begin
if @.lname_position <> 0
set @.SplitName = right(@.name, @.ilen - @.Lname_position-1)
else
set @.SplitName = right(@.name, @.ilen - @.fname_position)
end
else
begin
if @.fname_position + @.lname_position <> @.ilen and @.lname_position <> 0
begin
set @.SplitName = left(@.name, @.lname_position)
set @.splitname = right(@.splitname,len(@.splitname) - @.fname_position - 1)
end
else
set @.splitname = 'unkn'
end
end
return @.SplitName
endYou might get more response if you post this under the "Microsoft SQL Server" forum. This one's for generic SQL questions - I for one know nothing about SQL Server.|||what if a person has a lastname like this "DELA CRUZ"
can you post an example of the name parameter ex: "Michael Angelo Rodriguez" or "Michael Angelo Dela Cruz" coz as you can see on the 2 ex. it is hard to detemine the first name on the first example and last name on the latter the name should have a middle initial or comma perhaps.|||This is probably completely wrong, but I wouldn't put it past SQL Server to think that you were trying to modify the parameters. That would, of course, make the function non-deterministic. Try copying them to local variables.
Otherwise, the way to debug a problem like this is to break your function into small functions and verify that the small functions are all deterministic.sql
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Friday, March 30, 2012
how to make this report?
on a timesheet system, if I want a sum of a user on each project over a given period, how can I do this without creating a view? I mean creating a view will just do the sum for that given period, but not prompting the user for the period.
the table looks like this:
empid, projid, hours,closingDate
now I want the sum of the hours on two closing dates, can crystal report prompt the user for two closing dates then do the sum for each employee on each project?You want to sum the hours on 2 closing dates.
Create 2 parameters for each closing date (in 8.0: Insert | Field Object ... on the menu, then create a new parameter value for each date that the user can enter. Be sure to select Date for data type.)
You want the report to prompt the user, so use the parameters in the report and in the select statement or Select Expert. If you named your parameters Date1 and Date2: ({closingDate} = {?Date1}) or ({closingDate} = {?Date2}).
Insert a Group on the ID.
Insert a Group under that on the projid.
Sum the hours for all groups and you will get a total for each projid and a total for the id.
Format your report to make it look like you want it to.
I hope this helps!
Kymberlie|||thx kstefanski
the table looks like this:
empid, projid, hours,closingDate
now I want the sum of the hours on two closing dates, can crystal report prompt the user for two closing dates then do the sum for each employee on each project?You want to sum the hours on 2 closing dates.
Create 2 parameters for each closing date (in 8.0: Insert | Field Object ... on the menu, then create a new parameter value for each date that the user can enter. Be sure to select Date for data type.)
You want the report to prompt the user, so use the parameters in the report and in the select statement or Select Expert. If you named your parameters Date1 and Date2: ({closingDate} = {?Date1}) or ({closingDate} = {?Date2}).
Insert a Group on the ID.
Insert a Group under that on the projid.
Sum the hours for all groups and you will get a total for each projid and a total for the id.
Format your report to make it look like you want it to.
I hope this helps!
Kymberlie|||thx kstefanski
How to make this join select statement works
Here is a select statement of table A:
select user01_id, user02_id from A where ...
I also need to have the names of users which is in the table user. How to join the table user to have the name field and yeild two fields with the same name?
select a.user01_id,u.name, a.user02_id, u.name from a join user u on (?)
table A
user01_id int,
user02_id int,
...
primary (user01_id, user02_id)
table user
userid int primary key,
name varchar(80),
...
Thans for your advise.select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/|||Originally posted by r937
select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/
Hi, Rudy,
The query works well. I didn't know the alias also can be appled on a field in addition of a table. I guess I can use inner join instead when the both user01_id and user02_id of table A refer to the userid in the table user.
Thanks very much for your help.
Vernon|||yes, you could use inner joins, if you are guaranteed that the userids will exist
i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...
rudy|||Originally posted by r937
yes, you could use inner joins, if you are guaranteed that the userids will exist
i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...
rudy
In fact, the two IDs of table A are foreign keys of the tabe user primary key, userid. So existence of the userid is guaranteed.
A good usage of "defensive". That is the difference between a master and a regular craftsman.
select user01_id, user02_id from A where ...
I also need to have the names of users which is in the table user. How to join the table user to have the name field and yeild two fields with the same name?
select a.user01_id,u.name, a.user02_id, u.name from a join user u on (?)
table A
user01_id int,
user02_id int,
...
primary (user01_id, user02_id)
table user
userid int primary key,
name varchar(80),
...
Thans for your advise.select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/|||Originally posted by r937
select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/
Hi, Rudy,
The query works well. I didn't know the alias also can be appled on a field in addition of a table. I guess I can use inner join instead when the both user01_id and user02_id of table A refer to the userid in the table user.
Thanks very much for your help.
Vernon|||yes, you could use inner joins, if you are guaranteed that the userids will exist
i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...
rudy|||Originally posted by r937
yes, you could use inner joins, if you are guaranteed that the userids will exist
i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...
rudy
In fact, the two IDs of table A are foreign keys of the tabe user primary key, userid. So existence of the userid is guaranteed.
A good usage of "defensive". That is the difference between a master and a regular craftsman.
Monday, March 26, 2012
how to make Enforce Password Policy unchecked by default
I am trying to create a login using my application.
The application creates the user and password itself .
when working with sql server 2000 it works fine.
when i try to install my application with sql server 2005 it is displaying the error .
'Password does not meet windows policy requirements
because it is not complex enough'
I want to keep the same password.
Is there any way to disable/uncheck this option by default?
Prashant
You could modify the login creation script to specify CHECK_POLICY=OFF
USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
|||This is good but for this i need to make changes in my code .My application integrates with sql server 2000,MSAccess ,Oracle.
the code for creating login is same .So can it be possible at sql server level to make Check_policy =OFF as default option.
|||I would be very interested to know this as well. I have a similar issue. If your SQL 2005 server is not part of a domain that enforces group security policies, one option you do have is to relax the default Local Security Policy on the 2003 server upon which your SQL 2005 is running. Using the "Local Security Policy" editor found in Administive Tools you can select "Password Policy" under Account Policies. From there you can change the settings for history, age, length, complexity requirements, and encryption. You will want to turn off the "Password must meet complexity requirements". You might have to change some of the other settings to meet your specific application needs.|||I would be interested in this as well.
We have a third party Application that the client says works with SQL Server 2005, but the user logon creation they have built in dos not pass a correct password, and turning the poicy off may fix the problem.
|||No, many features which are implemeted in the engine cannot be changed by default:CREATE LOGIN(...)
- CHECK_POLICY = { ON | OFF }
Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.
Maybe you post a bug / feature request on the connect forums to make it happen in further versions.
Jens K. Suessmeyer.
-
http://www.sqlserver2005.de
-
How to make Application.LoadPackage() and Package.Execute() to run asynchroniously?
Hi,
I am trying to execute a SSIS package programmatically. When a user drops a file in a shared folder, we execute the package based on that file. I am using SqlServer.DTs.Runtime.Application.LoadPackage() and SqlServer.DTs.Runtime.Package.Execute() functions each time to do this.
The problem is, when, say two people drop a file, the second one will not execute untill the first one is completed. I also tried only calling LoadPackage() a single time, and then storing the instance and calling Execute() in a different thread on each file drop; although the blocking still occurs. I assume the Package object is the one doing the blocking then behind the scenes.
Is there any built in functionality to make these functions (Application.LoadPackage() and Package.Execute()) run async? If not, has anyone had much success sticking these calls into a thread? I tried sticking these calls into a thread using System.Threading.ThreadPool.QueueUserWorkItem(), and this appears to work, however it randomly crashes the program when I drop multiple files one after the other. The exception also isn't too helpfull (pasted below):
"The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails."
So, is what I am trying to do possible? I know it must be, because when I was using .NET 1.X, I was just calling dtexec via the command line, and dtexec was able to execute many packages simultaneously....
Thanks for any help,
DrewThe calls are synchronous, but each package object is independent - so if you create a thread per incoming file, create a new package object and do LoadPackage/Execute you should get the behavior you need.
The random errors you see are probably caused by your code trying to load the DTSX file before it was fully copied. So SSIS tries to read partial file and throws the error reporting it is not a valid XML file. You need some way to ensure you only load a file when it is ready, e.g. try opening it exclusively until you succeed (it will fail if someone is still writing to the file).
Friday, March 23, 2012
how to make a textbox invisible
hi
how can i make a textbox invisible ?
here is the scenario.
i have a textbox to remind user to select some parameters to view the graph.
once the user selects the paramteters - the graph shows up.
once the graph shows up -the textbox should disappear.
how can i do thatright click the text box, select Properties
click Advanced >>
in the Advanced Textbox Properties
click the Visibility tab
in the Initial visibility radio button group check the Visible radio button
and below check the Visibility can be toggled by another report item
in the Report item drop down list pick your chart
i haven't tried this, it's off the top of my head, but i hope it's a good
first step,
regards,
kowlasky
"RP" wrote:
> hi
> how can i make a textbox invisible ?
> here is the scenario.
> i have a textbox to remind user to select some parameters to view the graph.
> once the user selects the paramteters - the graph shows up.
> once the graph shows up -the textbox should disappear.
> how can i do that|||But we can only see textboxes in the dropdown of "visibility can be toggled
by another report item"
:-( any other suggestions ''''
Thanks
"kowalsky" wrote:
> right click the text box, select Properties
> click Advanced >>
> in the Advanced Textbox Properties
> click the Visibility tab
> in the Initial visibility radio button group check the Visible radio button
> and below check the Visibility can be toggled by another report item
> in the Report item drop down list pick your chart
> i haven't tried this, it's off the top of my head, but i hope it's a good
> first step,
> regards,
> kowlasky
>
> "RP" wrote:
> > hi
> > how can i make a textbox invisible ?
> > here is the scenario.
> > i have a textbox to remind user to select some parameters to view the graph.
> > once the user selects the paramteters - the graph shows up.
> > once the graph shows up -the textbox should disappear.
> > how can i do thatsql
how can i make a textbox invisible ?
here is the scenario.
i have a textbox to remind user to select some parameters to view the graph.
once the user selects the paramteters - the graph shows up.
once the graph shows up -the textbox should disappear.
how can i do thatright click the text box, select Properties
click Advanced >>
in the Advanced Textbox Properties
click the Visibility tab
in the Initial visibility radio button group check the Visible radio button
and below check the Visibility can be toggled by another report item
in the Report item drop down list pick your chart
i haven't tried this, it's off the top of my head, but i hope it's a good
first step,
regards,
kowlasky
"RP" wrote:
> hi
> how can i make a textbox invisible ?
> here is the scenario.
> i have a textbox to remind user to select some parameters to view the graph.
> once the user selects the paramteters - the graph shows up.
> once the graph shows up -the textbox should disappear.
> how can i do that|||But we can only see textboxes in the dropdown of "visibility can be toggled
by another report item"
:-( any other suggestions ''''
Thanks
"kowalsky" wrote:
> right click the text box, select Properties
> click Advanced >>
> in the Advanced Textbox Properties
> click the Visibility tab
> in the Initial visibility radio button group check the Visible radio button
> and below check the Visibility can be toggled by another report item
> in the Report item drop down list pick your chart
> i haven't tried this, it's off the top of my head, but i hope it's a good
> first step,
> regards,
> kowlasky
>
> "RP" wrote:
> > hi
> > how can i make a textbox invisible ?
> > here is the scenario.
> > i have a textbox to remind user to select some parameters to view the graph.
> > once the user selects the paramteters - the graph shows up.
> > once the graph shows up -the textbox should disappear.
> > how can i do thatsql
How to make a report with parameters?
For example I have a database with employees with their cities. I need that
the report ask the user which CITY to show on the report?
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 2.
MIEMBRO ACTIVO DE ALIANZADEVGo to the Reporting Services Books Online (Start > Microsoft SQL Server >
Reporting Services) and search for the word "parameters". It should give
you everything you need.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:OqlLjA62EHA.208@.TK2MSFTNGP12.phx.gbl...
> For example I have a database with employees with their cities. I need
> that
> the report ask the user which CITY to show on the report?
>
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 2.
> MIEMBRO ACTIVO DE ALIANZADEV
>
the report ask the user which CITY to show on the report?
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 2.
MIEMBRO ACTIVO DE ALIANZADEVGo to the Reporting Services Books Online (Start > Microsoft SQL Server >
Reporting Services) and search for the word "parameters". It should give
you everything you need.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:OqlLjA62EHA.208@.TK2MSFTNGP12.phx.gbl...
> For example I have a database with employees with their cities. I need
> that
> the report ask the user which CITY to show on the report?
>
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 2.
> MIEMBRO ACTIVO DE ALIANZADEV
>
how to make a 'my reports' folder
Hi,
I am having some trouble getting the 'My Reports' folder to appear for all
users. The docs just say that as long as the user exists as a windows user
and the 'My Reports' option is ticked in 'Site Settings', ReportServer
should just create a 'My Reports' folder the first time a user logs onto the
ReportServer. But this is not working for all users. About half of the
users have the 'My Reports' folder created. So I need to know how to create
them manually, or at least some workaround to get each user a 'My Reports'
folder!
I don't see any difference between the user accts (other than the names).
Thanks,
LanceLance,
Have you checked to ensure all users are are included in the "My Reports"
role - or in some custom role that includes the required permissions.
Regards,
Rob Labbé, MCP, MCAD, MCSD, MCT
Lead Architect/Trainer
Fidelis
Blog: http://spaces.msn.com/members/roblabbe
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:11849vnplj1n02@.corp.supernews.com...
> Hi,
> I am having some trouble getting the 'My Reports' folder to appear for all
> users. The docs just say that as long as the user exists as a windows
> user
> and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> should just create a 'My Reports' folder the first time a user logs onto
> the
> ReportServer. But this is not working for all users. About half of the
> users have the 'My Reports' folder created. So I need to know how to
> create
> them manually, or at least some workaround to get each user a 'My Reports'
> folder!
> I don't see any difference between the user accts (other than the names).
> Thanks,
> Lance
>|||Hi,
Thanks for the reply, but I am a bit confused about it! Maybe if I explain
a bit more about the setup: The ReportServer is set up with no common
reports. Each user can *only* view the reports in their own 'My Reports'
folder. As such each user *must* have a 'My Reports' folder! (obviously)
Here is how the server is set up:
* There are the standard 'Item-level' roles and an additional one,
'Viewer' - which is a limited acct, that just allows the user to 'view
folders' and 'view reports'. None of the non-admin users can create
reports.
* The 'System Roles' are the standard 'System Administrator' and 'System
User' - the 'System User' has the 'View report server properties' and 'View
shared schedules' tasks.
* The 'Enable My Reports to support user-owned folders for publishing and
running personalized reports.' checkbox is checked in the 'Reporst Server'
'Site Settings'. The 'Choose the role to apply to each user's My Reports
folder' dropdown is 'Viewer'
To set up for a new user:
* Each new user is added to the 'Users' group on the server (windows 2000
server).
* The user logs in, and a 'My Reports' folder should be created.
But mostly, a 'My Reports' folder is *not* created. Or perhaps just not
shown. I don't know how to check whether there is a 'My Reports' folder for
the user - I am thinking that there is a value in the DB somewhere that
tells me...
I was experimenting with a dozen new users, and the docs say that as soon as
a windows user logs in, a 'My Reports' folder should be created. I tried
both creating a 'System Role' for the user and *not* creating a system role,
but the creation of the 'My Reports' folder seems to occur randomly. I say
*seem*, as I am sure that I am missing a step or something, as I confess
that I find the role assignment somewhat confusing. But I'm new, and I am
sure I'll get used to it...
In response to your kindly answer:
So, from my limited understanding, the 'My Reports' role is an 'item-level'
role; I can only seem to assign to this at the report level (ie an 'item')
*not* to a user. But if the 'My Reports' folder is not being created, I
can't assign this 'tiem-level' role to it!
In short: I am still stuck with some users having a 'My Reports' folder and
most users *not* having one!
Thanks,
Lance
> Lance,
> Have you checked to ensure all users are are included in the "My Reports"
> role - or in some custom role that includes the required permissions.
> Regards,
>
> --
> Rob Labbé, MCP, MCAD, MCSD, MCT
> Lead Architect/Trainer
> Fidelis
> Blog: http://spaces.msn.com/members/roblabbe
> "Lance" <lance@.[nospam]keayweb.com> wrote in message
> news:11849vnplj1n02@.corp.supernews.com...
> > Hi,
> > I am having some trouble getting the 'My Reports' folder to appear for
all
> > users. The docs just say that as long as the user exists as a windows
> > user
> > and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> > should just create a 'My Reports' folder the first time a user logs onto
> > the
> > ReportServer. But this is not working for all users. About half of the
> > users have the 'My Reports' folder created. So I need to know how to
> > create
> > them manually, or at least some workaround to get each user a 'My
Reports'
> > folder!
> >
> > I don't see any difference between the user accts (other than the
names).
> >
> > Thanks,
> > Lance
> >
> >
>|||Hi,
I was just nosing around the DB, and I noticed that all the users are in the
'users' table, but the user accounts don't appear in the 'PolicyUserRole'
table, and hence there are not any policies for them.
I don't know if this helps any or just confuses...
And there aren't any application events in the log on the server, either.
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:11869vtmjhs8i08@.corp.supernews.com...
> Hi,
> Thanks for the reply, but I am a bit confused about it! Maybe if I
explain
> a bit more about the setup: The ReportServer is set up with no common
> reports. Each user can *only* view the reports in their own 'My Reports'
> folder. As such each user *must* have a 'My Reports' folder! (obviously)
> Here is how the server is set up:
> * There are the standard 'Item-level' roles and an additional one,
> 'Viewer' - which is a limited acct, that just allows the user to 'view
> folders' and 'view reports'. None of the non-admin users can create
> reports.
> * The 'System Roles' are the standard 'System Administrator' and 'System
> User' - the 'System User' has the 'View report server properties' and
'View
> shared schedules' tasks.
> * The 'Enable My Reports to support user-owned folders for publishing and
> running personalized reports.' checkbox is checked in the 'Reporst Server'
> 'Site Settings'. The 'Choose the role to apply to each user's My Reports
> folder' dropdown is 'Viewer'
> To set up for a new user:
> * Each new user is added to the 'Users' group on the server (windows 2000
> server).
> * The user logs in, and a 'My Reports' folder should be created.
> But mostly, a 'My Reports' folder is *not* created. Or perhaps just not
> shown. I don't know how to check whether there is a 'My Reports' folder
for
> the user - I am thinking that there is a value in the DB somewhere that
> tells me...
> I was experimenting with a dozen new users, and the docs say that as soon
as
> a windows user logs in, a 'My Reports' folder should be created. I tried
> both creating a 'System Role' for the user and *not* creating a system
role,
> but the creation of the 'My Reports' folder seems to occur randomly. I
say
> *seem*, as I am sure that I am missing a step or something, as I confess
> that I find the role assignment somewhat confusing. But I'm new, and I am
> sure I'll get used to it...
>
> In response to your kindly answer:
> So, from my limited understanding, the 'My Reports' role is an
'item-level'
> role; I can only seem to assign to this at the report level (ie an 'item')
> *not* to a user. But if the 'My Reports' folder is not being created, I
> can't assign this 'tiem-level' role to it!
>
> In short: I am still stuck with some users having a 'My Reports' folder
and
> most users *not* having one!
> Thanks,
> Lance
>
>
> > Lance,
> >
> > Have you checked to ensure all users are are included in the "My
Reports"
> > role - or in some custom role that includes the required permissions.
> >
> > Regards,
> >
> >
> > --
> > Rob Labbé, MCP, MCAD, MCSD, MCT
> > Lead Architect/Trainer
> > Fidelis
> >
> > Blog: http://spaces.msn.com/members/roblabbe
> >
> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
> > news:11849vnplj1n02@.corp.supernews.com...
> > > Hi,
> > > I am having some trouble getting the 'My Reports' folder to appear for
> all
> > > users. The docs just say that as long as the user exists as a windows
> > > user
> > > and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> > > should just create a 'My Reports' folder the first time a user logs
onto
> > > the
> > > ReportServer. But this is not working for all users. About half of
the
> > > users have the 'My Reports' folder created. So I need to know how to
> > > create
> > > them manually, or at least some workaround to get each user a 'My
> Reports'
> > > folder!
> > >
> > > I don't see any difference between the user accts (other than the
> names).
> > >
> > > Thanks,
> > > Lance
> > >
> > >
> >
> >
>sql
I am having some trouble getting the 'My Reports' folder to appear for all
users. The docs just say that as long as the user exists as a windows user
and the 'My Reports' option is ticked in 'Site Settings', ReportServer
should just create a 'My Reports' folder the first time a user logs onto the
ReportServer. But this is not working for all users. About half of the
users have the 'My Reports' folder created. So I need to know how to create
them manually, or at least some workaround to get each user a 'My Reports'
folder!
I don't see any difference between the user accts (other than the names).
Thanks,
LanceLance,
Have you checked to ensure all users are are included in the "My Reports"
role - or in some custom role that includes the required permissions.
Regards,
Rob Labbé, MCP, MCAD, MCSD, MCT
Lead Architect/Trainer
Fidelis
Blog: http://spaces.msn.com/members/roblabbe
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:11849vnplj1n02@.corp.supernews.com...
> Hi,
> I am having some trouble getting the 'My Reports' folder to appear for all
> users. The docs just say that as long as the user exists as a windows
> user
> and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> should just create a 'My Reports' folder the first time a user logs onto
> the
> ReportServer. But this is not working for all users. About half of the
> users have the 'My Reports' folder created. So I need to know how to
> create
> them manually, or at least some workaround to get each user a 'My Reports'
> folder!
> I don't see any difference between the user accts (other than the names).
> Thanks,
> Lance
>|||Hi,
Thanks for the reply, but I am a bit confused about it! Maybe if I explain
a bit more about the setup: The ReportServer is set up with no common
reports. Each user can *only* view the reports in their own 'My Reports'
folder. As such each user *must* have a 'My Reports' folder! (obviously)
Here is how the server is set up:
* There are the standard 'Item-level' roles and an additional one,
'Viewer' - which is a limited acct, that just allows the user to 'view
folders' and 'view reports'. None of the non-admin users can create
reports.
* The 'System Roles' are the standard 'System Administrator' and 'System
User' - the 'System User' has the 'View report server properties' and 'View
shared schedules' tasks.
* The 'Enable My Reports to support user-owned folders for publishing and
running personalized reports.' checkbox is checked in the 'Reporst Server'
'Site Settings'. The 'Choose the role to apply to each user's My Reports
folder' dropdown is 'Viewer'
To set up for a new user:
* Each new user is added to the 'Users' group on the server (windows 2000
server).
* The user logs in, and a 'My Reports' folder should be created.
But mostly, a 'My Reports' folder is *not* created. Or perhaps just not
shown. I don't know how to check whether there is a 'My Reports' folder for
the user - I am thinking that there is a value in the DB somewhere that
tells me...
I was experimenting with a dozen new users, and the docs say that as soon as
a windows user logs in, a 'My Reports' folder should be created. I tried
both creating a 'System Role' for the user and *not* creating a system role,
but the creation of the 'My Reports' folder seems to occur randomly. I say
*seem*, as I am sure that I am missing a step or something, as I confess
that I find the role assignment somewhat confusing. But I'm new, and I am
sure I'll get used to it...
In response to your kindly answer:
So, from my limited understanding, the 'My Reports' role is an 'item-level'
role; I can only seem to assign to this at the report level (ie an 'item')
*not* to a user. But if the 'My Reports' folder is not being created, I
can't assign this 'tiem-level' role to it!
In short: I am still stuck with some users having a 'My Reports' folder and
most users *not* having one!
Thanks,
Lance
> Lance,
> Have you checked to ensure all users are are included in the "My Reports"
> role - or in some custom role that includes the required permissions.
> Regards,
>
> --
> Rob Labbé, MCP, MCAD, MCSD, MCT
> Lead Architect/Trainer
> Fidelis
> Blog: http://spaces.msn.com/members/roblabbe
> "Lance" <lance@.[nospam]keayweb.com> wrote in message
> news:11849vnplj1n02@.corp.supernews.com...
> > Hi,
> > I am having some trouble getting the 'My Reports' folder to appear for
all
> > users. The docs just say that as long as the user exists as a windows
> > user
> > and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> > should just create a 'My Reports' folder the first time a user logs onto
> > the
> > ReportServer. But this is not working for all users. About half of the
> > users have the 'My Reports' folder created. So I need to know how to
> > create
> > them manually, or at least some workaround to get each user a 'My
Reports'
> > folder!
> >
> > I don't see any difference between the user accts (other than the
names).
> >
> > Thanks,
> > Lance
> >
> >
>|||Hi,
I was just nosing around the DB, and I noticed that all the users are in the
'users' table, but the user accounts don't appear in the 'PolicyUserRole'
table, and hence there are not any policies for them.
I don't know if this helps any or just confuses...
And there aren't any application events in the log on the server, either.
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:11869vtmjhs8i08@.corp.supernews.com...
> Hi,
> Thanks for the reply, but I am a bit confused about it! Maybe if I
explain
> a bit more about the setup: The ReportServer is set up with no common
> reports. Each user can *only* view the reports in their own 'My Reports'
> folder. As such each user *must* have a 'My Reports' folder! (obviously)
> Here is how the server is set up:
> * There are the standard 'Item-level' roles and an additional one,
> 'Viewer' - which is a limited acct, that just allows the user to 'view
> folders' and 'view reports'. None of the non-admin users can create
> reports.
> * The 'System Roles' are the standard 'System Administrator' and 'System
> User' - the 'System User' has the 'View report server properties' and
'View
> shared schedules' tasks.
> * The 'Enable My Reports to support user-owned folders for publishing and
> running personalized reports.' checkbox is checked in the 'Reporst Server'
> 'Site Settings'. The 'Choose the role to apply to each user's My Reports
> folder' dropdown is 'Viewer'
> To set up for a new user:
> * Each new user is added to the 'Users' group on the server (windows 2000
> server).
> * The user logs in, and a 'My Reports' folder should be created.
> But mostly, a 'My Reports' folder is *not* created. Or perhaps just not
> shown. I don't know how to check whether there is a 'My Reports' folder
for
> the user - I am thinking that there is a value in the DB somewhere that
> tells me...
> I was experimenting with a dozen new users, and the docs say that as soon
as
> a windows user logs in, a 'My Reports' folder should be created. I tried
> both creating a 'System Role' for the user and *not* creating a system
role,
> but the creation of the 'My Reports' folder seems to occur randomly. I
say
> *seem*, as I am sure that I am missing a step or something, as I confess
> that I find the role assignment somewhat confusing. But I'm new, and I am
> sure I'll get used to it...
>
> In response to your kindly answer:
> So, from my limited understanding, the 'My Reports' role is an
'item-level'
> role; I can only seem to assign to this at the report level (ie an 'item')
> *not* to a user. But if the 'My Reports' folder is not being created, I
> can't assign this 'tiem-level' role to it!
>
> In short: I am still stuck with some users having a 'My Reports' folder
and
> most users *not* having one!
> Thanks,
> Lance
>
>
> > Lance,
> >
> > Have you checked to ensure all users are are included in the "My
Reports"
> > role - or in some custom role that includes the required permissions.
> >
> > Regards,
> >
> >
> > --
> > Rob Labbé, MCP, MCAD, MCSD, MCT
> > Lead Architect/Trainer
> > Fidelis
> >
> > Blog: http://spaces.msn.com/members/roblabbe
> >
> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
> > news:11849vnplj1n02@.corp.supernews.com...
> > > Hi,
> > > I am having some trouble getting the 'My Reports' folder to appear for
> all
> > > users. The docs just say that as long as the user exists as a windows
> > > user
> > > and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> > > should just create a 'My Reports' folder the first time a user logs
onto
> > > the
> > > ReportServer. But this is not working for all users. About half of
the
> > > users have the 'My Reports' folder created. So I need to know how to
> > > create
> > > them manually, or at least some workaround to get each user a 'My
> Reports'
> > > folder!
> > >
> > > I don't see any difference between the user accts (other than the
> names).
> > >
> > > Thanks,
> > > Lance
> > >
> > >
> >
> >
>sql
Wednesday, March 21, 2012
How to MAKE @@LOCK_TIMEOUT effect to every session
Example,I want to let any lock be cancel if the lock keep 2 minute.
Or is there has any other parametes about user's lock and how to use?
thanksI'm afraid that there is no such setting. you have to set it from the client applications.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fx_man" <welcome_you@.sina.com> wrote in message news:ejbP7$KQEHA.3532@.TK2MSFTNGP12.phx.gbl...
> Example,I want to let any lock be cancel if the lock keep 2 minute.
> Or is there has any other parametes about user's lock and how to use?
> thanks
>sql
Or is there has any other parametes about user's lock and how to use?
thanksI'm afraid that there is no such setting. you have to set it from the client applications.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fx_man" <welcome_you@.sina.com> wrote in message news:ejbP7$KQEHA.3532@.TK2MSFTNGP12.phx.gbl...
> Example,I want to let any lock be cancel if the lock keep 2 minute.
> Or is there has any other parametes about user's lock and how to use?
> thanks
>sql
How to MAKE @@LOCK_TIMEOUT effect to every session
Example,I want to let any lock be cancel if the lock keep 2 minute.
Or is there has any other parametes about user's lock and how to use?
thanksI'm afraid that there is no such setting. you have to set it from the client
applications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fx_man" <welcome_you@.sina.com> wrote in message news:ejbP7$KQEHA.3532@.TK2MSFTNGP12.phx.gbl.
.
> Example,I want to let any lock be cancel if the lock keep 2 minute.
> Or is there has any other parametes about user's lock and how to use?
> thanks
>
Or is there has any other parametes about user's lock and how to use?
thanksI'm afraid that there is no such setting. you have to set it from the client
applications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fx_man" <welcome_you@.sina.com> wrote in message news:ejbP7$KQEHA.3532@.TK2MSFTNGP12.phx.gbl.
.
> Example,I want to let any lock be cancel if the lock keep 2 minute.
> Or is there has any other parametes about user's lock and how to use?
> thanks
>
How to loop through in sql server 2000
I have a scenario where I have to keep track of users last three passwords,
if the new password entered by user matches with the last three passwords in the PasswordHistory Table then I will display message to user that "you can not repeat any of your last three passwords.."
Basically I can do : select top 3 passwords from MyTable order by createdDate desc
But how to do comparision ?
I was googling for if anything like array exist in sql server 2000?
Please suggest, any pointer......
Thanks
You can use the IN keyword to see if a scalar value is within a set of values.
Here's how I would do this. I'd have a stored procedure that accepts the @.NewPassword and @.UserID as input parameters and then returns either a 1 or a 0, and in .NET code I'd do an ExecuteScalar() and cast the result to a Boolean.
The T-SQL in the stored procedure would look something like:
IF @.NewPassword IN (SELECT TOP 3 Passwords FROM PasswordHistory WHERE UserID = @.UserID ORDER BY CreatedDate DESC)
SELECT 1
ELSE
SELECT 0
|||
Hi Scott,
Thanks for your reply, its surely something which i wanted , let me try it .
will let you know how it works out.
|||
It does work !!
Thanks a lot for your time.
|||
gridview:
It does work !!
Thanks a lot for your time.
Great! Happy Programming!
It does work !!
Thanks a lot for your time.
Great! Happy Programming!
How to log in to SQL with a user other than sa
my connection string is
"Data Source=w2k3-std;Initial Catalog=CTrack;User Id=sa;Password=********;"
I have created extra users in both my database and the master database but if I change the userid and password as above they fail to connect.
Why?
Cheers
PaulHi, the answer to your question is going to depend upon the errormessage you are seeing. Please be sure to post exact errormessages. This will help others to help you.
For new users, you need to assign them permission to log in to theserver, and you need to grant them permission to access your Ctrackdatabase. If you are having trouble doing this, let us know ifyou are using osql, Enterprise Manager, or some other tool.
And, BTW, you are making an excellent decision to stop using the saaccount for your application. This account should never be usedto access the database from any application.
|||
|||yep, I have reset it twice now just to be absolutely sure, where should I create the user though in the master db or my db?|||Well, you're going to actually *create* the user for that instance ofSQL Server, but you'll need to give it permissions to yourdatabase. I'd say that the easiest way to test accounts onceyou've initially created them, is to use them to log into QueryAnalyzer and try to run SQL statements directly. That makes iteasy to see what permissions they do and don't have, without beingbound to only the SQL statements that you application needs to run, andit also rules out potential connection string problems.
|||
|||
"Data Source=w2k3-std;Initial Catalog=CTrack;User Id=sa;Password=********;"
I have created extra users in both my database and the master database but if I change the userid and password as above they fail to connect.
Why?
Cheers
PaulHi, the answer to your question is going to depend upon the errormessage you are seeing. Please be sure to post exact errormessages. This will help others to help you.
For new users, you need to assign them permission to log in to theserver, and you need to grant them permission to access your Ctrackdatabase. If you are having trouble doing this, let us know ifyou are using osql, Enterprise Manager, or some other tool.
And, BTW, you are making an excellent decision to stop using the saaccount for your application. This account should never be usedto access the database from any application.
|||
here is teh error i get when i try to login as a user called paul, i have created the user via enterprise manager both in the master database and my ctrack database. authentication is set to sql & windows (is this the best?) and what hav eI d one wrong or more likely not done at all.
Paul
Login failed for user 'paul'.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'paul'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. |
Stack Trace:
[SqlException: Login failed for user 'paul'.] System.Data.SqlClient.ConnectionPool.CreateConnection() +402 System.Data.SqlClient.ConnectionPool.UserCreateRequest() +147 System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +392 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 Web1.login.Button1_Click(Object sender, EventArgs e) +232 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292 |
|||yep, I have reset it twice now just to be absolutely sure, where should I create the user though in the master db or my db?|||Well, you're going to actually *create* the user for that instance ofSQL Server, but you'll need to give it permissions to yourdatabase. I'd say that the easiest way to test accounts onceyou've initially created them, is to use them to log into QueryAnalyzer and try to run SQL statements directly. That makes iteasy to see what permissions they do and don't have, without beingbound to only the SQL statements that you application needs to run, andit also rules out potential connection string problems.
|||
i logged into the server as user paul and ran query analyser and quiereied (?) my database without any trouble, authentication is set to sql server and windows any more ideas anyone?
|||
I deleted the user and recreated it using sql authentication rather than windows authentication and now I can connect, what is the differenc and does anyone why I could not connect with windows auth?
Paul
|||Because the User ID and Password attributes of the connection string are specific to SQL authentication.|||
I knew it had to be me doing something stupid....
thanks all
Monday, March 19, 2012
How to log in to SQL with a user other than sa
my connection string is
"Data Source=w2k3-std;Initial Catalog=CTrack;User Id=sa;Password=********;"
I have created extra users in both my database and the master database but if I change the userid and password as above they fail to connect.
Why?
Cheers
PaulHi, the answer to your question is going to depend upon the errormessage you are seeing. Please be sure to post exact errormessages. This will help others to help you.
For new users, you need to assign them permission to log in to theserver, and you need to grant them permission to access your Ctrackdatabase. If you are having trouble doing this, let us know ifyou are using osql, Enterprise Manager, or some other tool.
And, BTW, you are making an excellent decision to stop using the saaccount for your application. This account should never be usedto access the database from any application.
|||
|||yep, I have reset it twice now just to be absolutely sure, where should I create the user though in the master db or my db?|||Well, you're going to actually *create* the user for that instance ofSQL Server, but you'll need to give it permissions to yourdatabase. I'd say that the easiest way to test accounts onceyou've initially created them, is to use them to log into QueryAnalyzer and try to run SQL statements directly. That makes iteasy to see what permissions they do and don't have, without beingbound to only the SQL statements that you application needs to run, andit also rules out potential connection string problems.
|||
|||
"Data Source=w2k3-std;Initial Catalog=CTrack;User Id=sa;Password=********;"
I have created extra users in both my database and the master database but if I change the userid and password as above they fail to connect.
Why?
Cheers
PaulHi, the answer to your question is going to depend upon the errormessage you are seeing. Please be sure to post exact errormessages. This will help others to help you.
For new users, you need to assign them permission to log in to theserver, and you need to grant them permission to access your Ctrackdatabase. If you are having trouble doing this, let us know ifyou are using osql, Enterprise Manager, or some other tool.
And, BTW, you are making an excellent decision to stop using the saaccount for your application. This account should never be usedto access the database from any application.
|||
here is teh error i get when i try to login as a user called paul, i have created the user via enterprise manager both in the master database and my ctrack database. authentication is set to sql & windows (is this the best?) and what hav eI d one wrong or more likely not done at all.
Paul
Login failed for user 'paul'.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'paul'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. |
Stack Trace:
[SqlException: Login failed for user 'paul'.] System.Data.SqlClient.ConnectionPool.CreateConnection() +402 System.Data.SqlClient.ConnectionPool.UserCreateRequest() +147 System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +392 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 Web1.login.Button1_Click(Object sender, EventArgs e) +232 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292 |
|||yep, I have reset it twice now just to be absolutely sure, where should I create the user though in the master db or my db?|||Well, you're going to actually *create* the user for that instance ofSQL Server, but you'll need to give it permissions to yourdatabase. I'd say that the easiest way to test accounts onceyou've initially created them, is to use them to log into QueryAnalyzer and try to run SQL statements directly. That makes iteasy to see what permissions they do and don't have, without beingbound to only the SQL statements that you application needs to run, andit also rules out potential connection string problems.
|||
i logged into the server as user paul and ran query analyser and quiereied (?) my database without any trouble, authentication is set to sql server and windows any more ideas anyone?
|||
I deleted the user and recreated it using sql authentication rather than windows authentication and now I can connect, what is the differenc and does anyone why I could not connect with windows auth?
Paul
|||Because the User ID and Password attributes of the connection string are specific to SQL authentication.|||
I knew it had to be me doing something stupid....
thanks all
How to log disconnects?
I need to log user disconnects for a specific DB. I do not know how to
do this relabily. The application owner is under the impression that
our SQL cluster is disconnecting their users. Which I am fairly
confident is not the case.
Any ideas on how to do this? Specifically disconnects that are not
'requested' but either forced or lost.
Thanks!Check the app logs first, are they logging query/conection
errors,timeouts,etc?
And look for specific errors (ADO,OLE-DB,etc)
Will wrote:
> I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!|||That kind of logging 'should' happen on the client. For a limited time span,
you may wish to use Profiler.
Application timeout is the first thing to explore. Check the ADO connection
timeout (I think the default is 60 seconds -but I don't remember with
certainty.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Will" <wfj5444@.gmail.com> wrote in message
news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...
>I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!
>|||Right,
That is the issue only one application out of many is seeing the issue.
It does not sound like an SQL Server issue. They are seeing the
disconnects on their side. We have no other users reporting any other
issues or symptoms.
Thanks,
Will
Arnie Rowland wrote:
> That kind of logging 'should' happen on the client. For a limited time span,
> you may wish to use Profiler.
> Application timeout is the first thing to explore. Check the ADO connection
> timeout (I think the default is 60 seconds -but I don't remember with
> certainty.)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...
> >I need to log user disconnects for a specific DB. I do not know how to
> > do this relabily. The application owner is under the impression that
> > our SQL cluster is disconnecting their users. Which I am fairly
> > confident is not the case.
> >
> > Any ideas on how to do this? Specifically disconnects that are not
> > 'requested' but either forced or lost.
> >
> > Thanks!
> >
do this relabily. The application owner is under the impression that
our SQL cluster is disconnecting their users. Which I am fairly
confident is not the case.
Any ideas on how to do this? Specifically disconnects that are not
'requested' but either forced or lost.
Thanks!Check the app logs first, are they logging query/conection
errors,timeouts,etc?
And look for specific errors (ADO,OLE-DB,etc)
Will wrote:
> I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!|||That kind of logging 'should' happen on the client. For a limited time span,
you may wish to use Profiler.
Application timeout is the first thing to explore. Check the ADO connection
timeout (I think the default is 60 seconds -but I don't remember with
certainty.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Will" <wfj5444@.gmail.com> wrote in message
news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...
>I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!
>|||Right,
That is the issue only one application out of many is seeing the issue.
It does not sound like an SQL Server issue. They are seeing the
disconnects on their side. We have no other users reporting any other
issues or symptoms.
Thanks,
Will
Arnie Rowland wrote:
> That kind of logging 'should' happen on the client. For a limited time span,
> you may wish to use Profiler.
> Application timeout is the first thing to explore. Check the ADO connection
> timeout (I think the default is 60 seconds -but I don't remember with
> certainty.)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...
> >I need to log user disconnects for a specific DB. I do not know how to
> > do this relabily. The application owner is under the impression that
> > our SQL cluster is disconnecting their users. Which I am fairly
> > confident is not the case.
> >
> > Any ideas on how to do this? Specifically disconnects that are not
> > 'requested' but either forced or lost.
> >
> > Thanks!
> >
How to lock out everyone but me?
I'm working with SQL Server Express, and I want to configure a named instance so that only the 'sa' user and a specified SQL Server user with a specified password have access. In particular, I'm trying to lock out BUILTIN\Administrators. Furthermore, I need to be able to do this from a command-line, since I want to configure it in a script. Nothing I do seems to work.
I've attempted to use sqlcmd and the T-SQL call ALTER LOGIN [BUILTIN\Administrators] DISABLE, but that returns the error "Cannot alter the login 'BUILTIN\Administrators' because it does not exist or you do not have permission."
What I can (apparently) successfully do is run DENY CONTROL TO [BUILTIN\Administrators]. This runs without reporting an error. However, after running it against the 'master' database and the specific database in my named instance I care about, I can still run the following:
sqlcmd -S (local)\MyInstance -d MyDB -Q "select * from my_table"
and see the contents of my_table.
What do I need to do to restrict access exclusively to 'sa' and other SQL users I designate?
Have you tried simply dropping the builtin\administrators login?
"Drop login [builtin\administrators]"
You can check sys.server_principals for a list of all logins. This seemed to work for me, although I tested this on Developer Edition and not Express Edition.
Sung
|||I think the right expression would be to deny connection to builtin administrator under normal circumstances.
I want to clarify that actually stopping a box administrator from accessing SQL Server instance (and any other service for that matter) is practically impossible as your adversary has control over the OS and most likely the hardware.
You need to trust the box admin to some extent, and a proper channel and policies to monitor unauthorized activities (i.e. company policies, box auditing, etc.) should be in place to keep a box administrator honest. If you are hosting any data on a machine where you don’t trust the box administrator, make sure you don’t store (and I would even say don’t manipulate) any valuable information in clear text on the system you don’t trust.
If your objective is to keep an honest box administrator honest and deter her from accessing SQL Server, revoking access as Sung mentioned should be enough. In SQL Express you additionally would have to drop the [Builtin\users] account. SQL Express is designed OOB to grant access to the instance (with low privileges) to any user in the system in order to take advantage of some SQL Express-specific features.
BTW. Keep in mind that in SQL Server 2005 it is sufficient to start the server in single user mode. The reason for this is to allow a box admin to recover the system in case of emergency.
I hope this information will be useful.
-Raul Garcia
SDE/T
SQL Server Engine
how to locate the exact user name who owns a db object
hi all,
In sql 2000, how can you find out the exact user name who owns/created a db
object? They are generally recorded as 'dbo' in the sysobjects table, how
can we find out the specific user login name behind the 'dbo' entry?
many thanks,
JJThe 'dbo' user in a database is a special user that maps to the login
who owns the database (usually the login who initially created the
database). The system stored proc "exec sp_helpdb '<dbname>'" will tell
you who the owner of the database is and that login will be the one that
maps to the dbo user in the database.
Other, less Microsoft approved, ways of finding this info would be:
* "select * from master.dbo.sysdatabases" (the sid column is the
login that owns a given database, ie. that maps to the dbo user in
that database, and you can join that to master.dbo.syslogins to
get more info about that login)
* "select * from <dbname>.dbo.sysusers" (the dbo user in the
database is always uid 1; the sid column in that table will map
back to the master.dbo.syslogins table to tell you who owns the
database...unless the database user is an orphaned user (the sid
doesn't map back to any row in master.dbo.syslogins) which often
happens when you restore DBs from other servers because the other
server has different data in its master.dbo.syslogins table; this
can be corrected with sp_change_users_login)
* You could use the SUSER_SNAME() function with the sysusers table
like this:
select SUSER_SNAME(sid) from <dbname>.dbo.sysusers where uid = 1
Bear in mind, not every object in a DB has to be owned by the dbo user,
although this is quite normal. To find out which DB user owns a
specific object in the database you can use the OBJECTPROPERTY()
function like this:
select USER_NAME(OBJECTPROPERTY(OBJECT_ID('MyTa
ble'),'OwnerId'))
However, sp_helpdb is probably the easiest. Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
JJ Wang wrote:
>hi all,
>In sql 2000, how can you find out the exact user name who owns/created a db
>object? They are generally recorded as 'dbo' in the sysobjects table, how
>can we find out the specific user login name behind the 'dbo' entry?
>many thanks,
>JJ
>|||JJ,
I think that would need to be accomplished by using a source control system
like Visual SourceSafe as many logins may have the ability to have dbo be
the owner of an object.
HTH
Jerry
"JJ Wang" <JJWang@.discussions.microsoft.com> wrote in message
news:73FC551C-3600-4E8F-8B37-B2BB9E890879@.microsoft.com...
> hi all,
> In sql 2000, how can you find out the exact user name who owns/created a
> db
> object? They are generally recorded as 'dbo' in the sysobjects table, how
> can we find out the specific user login name behind the 'dbo' entry?
> many thanks,
> JJ|||Hi,
the only wat i think for that is too tell your developers/DBAs to use full
name while creating db / objects .
Regards
In sql 2000, how can you find out the exact user name who owns/created a db
object? They are generally recorded as 'dbo' in the sysobjects table, how
can we find out the specific user login name behind the 'dbo' entry?
many thanks,
JJThe 'dbo' user in a database is a special user that maps to the login
who owns the database (usually the login who initially created the
database). The system stored proc "exec sp_helpdb '<dbname>'" will tell
you who the owner of the database is and that login will be the one that
maps to the dbo user in the database.
Other, less Microsoft approved, ways of finding this info would be:
* "select * from master.dbo.sysdatabases" (the sid column is the
login that owns a given database, ie. that maps to the dbo user in
that database, and you can join that to master.dbo.syslogins to
get more info about that login)
* "select * from <dbname>.dbo.sysusers" (the dbo user in the
database is always uid 1; the sid column in that table will map
back to the master.dbo.syslogins table to tell you who owns the
database...unless the database user is an orphaned user (the sid
doesn't map back to any row in master.dbo.syslogins) which often
happens when you restore DBs from other servers because the other
server has different data in its master.dbo.syslogins table; this
can be corrected with sp_change_users_login)
* You could use the SUSER_SNAME() function with the sysusers table
like this:
select SUSER_SNAME(sid) from <dbname>.dbo.sysusers where uid = 1
Bear in mind, not every object in a DB has to be owned by the dbo user,
although this is quite normal. To find out which DB user owns a
specific object in the database you can use the OBJECTPROPERTY()
function like this:
select USER_NAME(OBJECTPROPERTY(OBJECT_ID('MyTa
ble'),'OwnerId'))
However, sp_helpdb is probably the easiest. Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
JJ Wang wrote:
>hi all,
>In sql 2000, how can you find out the exact user name who owns/created a db
>object? They are generally recorded as 'dbo' in the sysobjects table, how
>can we find out the specific user login name behind the 'dbo' entry?
>many thanks,
>JJ
>|||JJ,
I think that would need to be accomplished by using a source control system
like Visual SourceSafe as many logins may have the ability to have dbo be
the owner of an object.
HTH
Jerry
"JJ Wang" <JJWang@.discussions.microsoft.com> wrote in message
news:73FC551C-3600-4E8F-8B37-B2BB9E890879@.microsoft.com...
> hi all,
> In sql 2000, how can you find out the exact user name who owns/created a
> db
> object? They are generally recorded as 'dbo' in the sysobjects table, how
> can we find out the specific user login name behind the 'dbo' entry?
> many thanks,
> JJ|||Hi,
the only wat i think for that is too tell your developers/DBAs to use full
name while creating db / objects .
Regards
Monday, March 12, 2012
How to list all user that have permission on database?
How to list all user that have permission on each
database?
Mesak,
you need to cycle through all your user databases with
select * -- look up in syslogins and sysusers to see what for columns you
need
from master.dbo.syslogins l
inner join YourDBName.dbo.sysusers u
on l.sid = u.sid
hth
Quentin
"Mesak" <anonymous@.discussions.microsoft.com> wrote in message
news:38e801c4a083$a4ce25f0$a501280a@.phx.gbl...
> How to list all user that have permission on each
> database?
database?
Mesak,
you need to cycle through all your user databases with
select * -- look up in syslogins and sysusers to see what for columns you
need
from master.dbo.syslogins l
inner join YourDBName.dbo.sysusers u
on l.sid = u.sid
hth
Quentin
"Mesak" <anonymous@.discussions.microsoft.com> wrote in message
news:38e801c4a083$a4ce25f0$a501280a@.phx.gbl...
> How to list all user that have permission on each
> database?
How to list all user that have permission on database?
How to list all user that have permission on each
database?Mesak,
you need to cycle through all your user databases with
select * -- look up in syslogins and sysusers to see what for columns you
need
from master.dbo.syslogins l
inner join YourDBName.dbo.sysusers u
on l.sid = u.sid
hth
Quentin
"Mesak" <anonymous@.discussions.microsoft.com> wrote in message
news:38e801c4a083$a4ce25f0$a501280a@.phx.gbl...
> How to list all user that have permission on each
> database?
database?Mesak,
you need to cycle through all your user databases with
select * -- look up in syslogins and sysusers to see what for columns you
need
from master.dbo.syslogins l
inner join YourDBName.dbo.sysusers u
on l.sid = u.sid
hth
Quentin
"Mesak" <anonymous@.discussions.microsoft.com> wrote in message
news:38e801c4a083$a4ce25f0$a501280a@.phx.gbl...
> How to list all user that have permission on each
> database?
How to list (Export to txt) the Built-In and User Store Procedures
Good afternoon,
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
Luis
Hi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:
> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
Luis
Hi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:
> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis
How to list (Export to txt) the Built-In and User Store Procedures
Good afternoon,
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
LuisHi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:
> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
LuisHi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:
> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis
Subscribe to:
Posts (Atom)