I know how to look at active users from Enterprise Manager, but how can I query out the information - what table is that info stored in ??? I don't want a list of all the logins, I just want the list of active users....
Help... thanks in advance,
Nancy
In desperate need of implementing a solution where the customer has purchased a CMS to replace their corporate site and wants to use MSSQL as the DB server type. I have 3 servers allocated to me to complete this and I could use some advice on the best setup.
THey're running windows 2003 standard server along with sql 2000 standard server. The intended plan is for the 2 servers to become web servers with the last server becoming the sql server. The CMS will reside soley on the sql server and the content for the web site on the seb server(s).
What I need to know is if it's possible to set up an active/passive node to accommadate this using the items mentioned above? From what I've been reading sql 2000 enterprise does clustering but I'm hoping this version of sql can be used for something.
Any responses are appreciated.
I know you can access LDAP via ADO.NET and the ADsDSOObject Provider.
Is there away to do it within a SQL Server Stored Procedure?
Thanks,
Tim
I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.
TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime.
OpDatesessionStartTimesessionCloseTime
2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166
2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442
2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853
2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707
[code]....
can see how many sessions logged in per hour with the following statement:
SELECT
opDate,
FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour,
Count(*) AS Total
FROM #empSessionLog
Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00')
Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults:
opDateopHourTotal
2015-01-20041
[code]....
Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.
In master.dbo.sysprocesses I can filter for blocked users (Blocked >0)and I can create a self join(ON SPID = Blocked) to see what userLoginame is causing the block. In the column [cmd] I can see thecommand that the offending blocker is running to cause the block, butit only says "SELECT" etc. with no details about the stored procedurethat is causing the block.If I am running a Trace, I can see the exact stored proceduresincluding the parameters that every cmd is running.Is there a way to see that same Trace information when looking forblocked users in master.dbo.sysprocesses, or in some other place?Ideally what I want is a list of blocked users, who is causing theblocks and the stored procedure name (or other mischief) causing theblock.Any help is appreciated.lq
View 17 Replies View RelatedHi,
The Report Manager portal has many folders. For each folder there are specific users with different roles.
I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.
Use ReportServer
SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r
WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID
The above query fetches all the users and their roles.
The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query.
TIA
I neet save user login form active direcotry to databases. How I can make that?
View 1 Replies View RelatedI am trying to find a select on sysprocesses that would list all the activelogins. An active login is a login that has a TSQL statment being executedon the server,This didnt work to well! Any ideas. Thanks in advance.select sp.loginame,-- more columnsfrom master..sysprocesses spwhere sp.status not in ('sleeping','background' )order by 1
View 2 Replies View RelatedMy insert statement for #Data - I only need to process each @EmployeeID one time, which is why I thought a loop would be sufficient, but I let this process run for 2 hrs and it still had not completed, so I feel I must have set-up something incorrectly!
This is my syntax, I am creating a table of active users, then wanting to get data for each of those active users. But only get the data for each active user 1 time.
Declare @EmployeeID varchar(50)
CREATE TABLE #ActiveUsers
(
ID INT IDENTITY NOT NULL
,EmployeeID varchar(50)
,processed int
)
Create Table #Data
[Code] ....
Is there anyway to disable this functionality?
View 7 Replies View RelatedI am looking to write a script that would give the list of all active users in a database with their privileges.
Sample would look something like this.
Login User Schema IsdbOwner canWrite canRead
xyz xyz dbo N Y Y
How does one Determine the number of Active users logged into a SQL Server Database?
I want to use the info to control concurrent licensing for my Application
Hi,
I'm trying to extract all the users and their membership to groups, and the membership of groups to groups from active directory though a link to server.
I can get the users. I can get the groups.... individually.
I can't get the info of what user is a member of or who are members of a group.
Anyone know how to do this or am I going to have to right a vb app? (Anyone already got the code...)
I want to load this data into tables for reporting in my Data Warehouse.
Cheers
Chris
I am working on the security model for an application that will be used by 100s of users with a dedicated SQL 2005 database for this application and access via SQL XML Web Services.
The client has asked to make it "open" during alpha testing such that anyone can access the web services without having to set them up first. Is there a way to do this? The best I can figure is to use mixed mode security and hard code a login and password. Any method using Windows authentication would require that I add every user at a minimum to the database.
In production, all users will have an active directory role specified that determines if they should have access to the web services or not. However, it is my understanding that to use Windows authentication, I would still need to add each individual user at a minimum as a Login to the SQL Server, and under best practices also as database users with permissions granted to the endpoint.
Am I correct in the above, or is there a more efficient way to achieve these results?
Thanks
-L
I invoke xp_cmdshell proc from inside a stored procedure on a 2-node active/passive SQL 2005 SP2 Standard cluster. Depending on which server the xp_cmdshell gets executed on I need to pass different arguments in the shell command. I thought I could use host_name() function to get the runtime process server, however, I am finding that it's not behaving correctly. In one example I know my active node is server2, but the host_name() function is returning server1. The only thing that I could possible explain this is that the MSDTC cluster group is not always on the same active node as the SQL server group and in the case I am talking about the cluster groups are in this mode (differnet nodes). Does the xp_cmdshell get executed by the SQL active node or the MDTC active node? And what is the best way to find out which server is going to run my xp_cmdshell?
Thanks.
Edit:
Perhaps another by product of this is that if I run select host_name() from the Studio Management query window i get different results depending on which server I am running the Studio Management on. On server1 I get server1 and on server 2 I get server 2, all the while server2 is the active node. I need a different function that will always let me determine the correct server that'll be running the xp_cmdshell...
Edit 2: I guess I could determine the running host inside the command shell itself, but I am curious to see if i can do it (cleaner) from SQL.
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
How does SQL Server store Security Information. Accounts, Users, etc... Is there a particular table, like Master Table?
View 4 Replies View RelatedHi there :)
I was hoping someone could help me figure out why this doesn't work....
I have a class called ProductsDB with a method called AddProduct that looks like this:
public String AddProduct(int categoryID, int makeID, string name, double price, double saleprice, double offerprice, int homepage, string thumbpath, string imagepath, string description)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("ProductsAdd", myConnection);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
parameterCategoryID.Value = categoryID;
myCommand.Parameters.Add(parameterCategoryID);
SqlParameter parameterMakeID = new SqlParameter("@MakeID", SqlDbType.Int, 4);
parameterMakeID.Value = makeID;
myCommand.Parameters.Add(parameterMakeID);
SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
parameterName.Value = name;
myCommand.Parameters.Add(parameterName);
SqlParameter parameterPrice = new SqlParameter("@Price", SqlDbType.Money, 8);
parameterPrice.Value = price;
myCommand.Parameters.Add(parameterPrice);
SqlParameter parameterSalePrice = new SqlParameter("@SalePrice", SqlDbType.Money, 8);
parameterSalePrice.Value = saleprice;
myCommand.Parameters.Add(parameterSalePrice);
SqlParameter parameterOfferPrice = new SqlParameter("@OfferPrice", SqlDbType.Money, 8);
parameterOfferPrice.Value = offerprice;
myCommand.Parameters.Add(parameterOfferPrice);
SqlParameter parameterHomepage = new SqlParameter("@Homepage", SqlDbType.Bit, 1);
parameterHomepage.Value = homepage;
myCommand.Parameters.Add(parameterHomepage);
SqlParameter parameterThumbnail = new SqlParameter("@Thumbnail", SqlDbType.NVarChar, 50);
parameterThumbnail.Value = thumbpath;
myCommand.Parameters.Add(parameterThumbnail);
SqlParameter parameterImage = new SqlParameter("@Image", SqlDbType.NVarChar, 50);
parameterImage.Value = imagepath;
myCommand.Parameters.Add(parameterImage);
SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
parameterProductID.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterProductID);
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
// Calculate the ProductID using Output Param from SPROC
int productId = (int)parameterProductID.Value;
return productId.ToString();
}
catch
{
return String.Empty;
}
}
And then in another cs file for my aspx page I have an event handler for an add button in a webform, which looks like this:
// Add New Product to ProductDB database
bikescene.Components.ProductsDB accountSystem = new bikescene.Components.ProductsDB();
String productId = accountSystem.AddProduct(Int32.Parse(DDLproductcategory.SelectedValue), Int32.Parse(DDLproductmanufacturer.SelectedValue), TBproductname.Text, Double.Parse(TBproductprice.Text), Double.Parse(TBproductsaleprice.Text), Double.Parse(TBproductofferprice.Text), Int32.Parse(DDLproducthomepage.SelectedValue), thumbnailpath, imagepath, TBproductdescription.Text);
And finally, here is my stored procedure:
CREATE Procedure ProductsAdd
(
@ProductID int,
@CategoryID int,
@MakeID int,
@Name nvarchar(50),
@Price money,
@SalePrice money,
@OfferPrice money,
@Homepage int,
@Thumbnail nvarchar(50),
@Image nvarchar(50),
@Description nvarchar(500)
)
AS
INSERT INTO Products
(
productID,
categoryID,
makeID,
name,
price,
saleprice,
offerprice,
homepage,
thumbpath,
imagepath,
description
)
VALUES
(
@ProductID,
@CategoryID,
@MakeID,
@Name,
@Price,
@SalePrice,
@OfferPrice,
@Homepage,
@Thumbnail,
@Image,
@Description
)
SELECT
@ProductID = @@Identity
GO
Basically, my images are uploaded to the server just fine but nothing is being added to the database + I don't get any error messages.
Any ideas?
Many thanks indeed :)
How does one get the names and data types of the parameters of a SQL 2005 stored procedure? There does not seem to be a refresh for a command parameters object, and the syntax for the get() in I don't seem to have right in c++.
View 1 Replies View RelatedIn the web site that I am building ( in C# language ), a hypothetic customer who would buy something would then be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.
My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.
So I want a SQL procedure where it takes the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info or not and then activate ( or not ) some related tasks.
I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?
~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50))
AS
SELECT COUNT(*) FROM IPN_received
WHERE Invoice = @Invoice
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
i want to have a user defined function or stored procedure(i don't know which one should be used, anyway!) which returns false if the table has no records, with the table name comes with a parameter.
could you give a little code sample?
thanx
Dear GroupI have found that table A had SELECT permissions for 'Public' but not tableB.Giving 'Public' SELECT permissions on table B did the trick.HOWEVER, I don't want anyone to be able to do a direct SELECT on table A orB but only give them access to the data by using the stored procedures. Isthere any way this can be set up?Thanks for your efforts!Have a nice day!Martin"Martin Feuersteiner" <theintrepidfox@hotmail.com> wrote in message news:...[color=blue]> Dear Group>> I'm having two stored procedures, sp_a and sp_b>> Content of stored procedure A:> CREATE PROCEDURE dbo.sp_a> SELECT * FROM a> GO>> Content of stored procedure B:> CREATE PROCEDURE dbo.sp_b> SELECT * FROM b> GO>> I have created a user that has execute permissions for both procedures.> When I run procedure A, all works fine but when running procedure B I'm> getting an error saying that the user must have SELECT permissions on> table B.>> Both tables are owned by dbo, and the security role for the user doesn't> has any SELECT permission on table a and b.> I'd be grateful if anyone could point me in a direction why this error> might come up for procedure B but not for A,> with a possible solution without giving the user SELECT permissions.>> Thanks very much for your help!>> Martin>[/color]
View 6 Replies View RelatedI have a need to access the active directory from a stored
procedure. I am certain that CLR is the answer but I am having a
hard time finding and example and I am a true newbie to AD and CLR.
Has anyone ever attempted to do this? I would love to see an example in VB if possible.
suppose i get date from MSsql how do i pass that to dos command is there a way so i can maipulate date and pass on to dos command to run at certain time
View 14 Replies View RelatedHi All,
I have thsi list of record
Chasis Status Date
pl1 sold 10/20/2004
pl1 return 10/21/2004
pl2 sold 10/24/2004
pl2 return 10/25/2004
pl3 sold 11/01/2004
pl4 sold 11/03/2004
pl4 return 11/04/2004
pl4 sold 11/06/2004
sp i want to list out cars that status solid has been sold
so in this case only pl3 and pl4 can be display. So anyone can advise me on this. thanks
Regards,
Shaffiq
I would like to do a query that would give me the info twice, with just a small difference.
Let's say I have a table with elements A, B and C. Whatever it is, it does not matter.
I would like to get all the data so I just do that:
SELECT A, B, C
Then I would like to add a bit of text, so I'll add:
SELECT A, B, C, 'Text1' as D
The thing is that I would like every single elements (A,B,C) to be repeated twice: Once with Text1, once with Text2.
Let's say my data looks like that:
1 2 3
2 1 3
4 1 2
Then my query would give me this:
1 2 3 Text1
2 1 3 Text1
4 1 2 Text1
1 2 3 Text2
2 1 3 Text2
4 1 2 Text2
(in no particular order)
Is there a way I can do this in just one query?
I have 2 stored proc's that i want to run an append query every 4 or 8 hours.
how does a person do this in ms sql 7?
Hello,I need a permanent synchronization between the "Active Directory" and the UserTable in my WebApp.I think a good solution for my interface can I realize with the "Stored Procedures" in SQL Server 2005. Is this the right way?I can create a Stored Procedure and a AD-Reader separately, that's no problem. But how I can import the "System.DirectoryServices"-Assembly in my Stored Procedure?Or is there an other solution?Thanks a lot!NussyStar*
View 2 Replies View RelatedHi,
I am trying to display a report from a bug tracker that shows the number of issues logged per month, per year and how many issues have been closed in that month. I have no difficulty displaying the number of issues that have been logged within a particular months, but when I need to display the number of issues closed - I get stuck. I am calling from one table 'bugs', which contains date_reported, status_id. I can do a count on the number of bugs between certain dates but I cannot list the number of closed issues in that exact time. I get the feeling I need a select statement within a select statement? If anyone can help. Thanks
I used to use this code to extract coupon usage from two tables orders and coupons
select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
[code]....
Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage.. The link between the two tables are
ID in Promotions
and
PromotionID in PromotionUsage
Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this?
I am trying to test sp in Query Analyser as below but get the error
Server: Msg 8114, Level 16, State 4, Procedure spRMU_GetChaseFiles, Line 0
Error converting data type varchar to datetime.
How do I put the date as a parameter ? I think it may be to do with the fact that MaxDateOut is an expression in my view that the sp is based on. It is taking this as char when it should be datetime. So, how do I make this expression type date ?
EXEC spRMU_GetChaseFiles
@strStartDate ='01/01/05',
@strEndDate = '21/04/06',
@strDepartment = 'Central Units',
@strDivision = 'Personnel',
@strSection = ''
Hi
I am having some teething problems while installing SQL on a 3 node cluster. Within the Cluster configuration I have 3 Cluster Groups with each of them having their associated disk resources. All these disk resources physically exist on a SAN.
The actual cluster is running absolutely fine and I can access all the disks from their respective owner node. The problem only starts when I start installing SQL Server 2005 on this cluster. I specify the Cluster group from the Cluster Group Selection and choose the desired partition and then the error message pops up
"There is not enough diskspace on the destination disk for the current SQL Server data files. To proceed, free up disk space to make room for data files, or install the data files to a different drive"
But the disk I am trying to install it on is 264Gb and none of it is used. I have also tried to change it to a different disk within the same Cluster group but to no avail. I have even tried to install it in a different cluster group all together but I get the same error message.
I have googled around havent found anything so far. The disks have got full permissions for the account I am installing SQL with.
Any help will be much appreciated.
Regards
Adnan
How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?
View 3 Replies View Related