I am trying to find a select on sysprocesses that would list all the active
logins. An active login is a login that has a TSQL statment being executed
on the server,
This didnt work to well! Any ideas. Thanks in advance.
select sp.loginame,
-- more columns
from master..sysprocesses sp
where sp.status not in ('sleeping','background' )
order by 1
Hi,
I have a legacy sql 2000 database with numerous stored procedure and tables.
I need to find out the top 20 most used/accessed tables in the database
Any one know how i could do this type of trace with profiler?
Hi,
I am in the eary stages of planning a big reporting services roll out, and I have a problem. I want to be able to filter what is show in a parameter list, preferably based on a antive directory group the user is in.
As an example i could have a parameter called States that under normal circumstances would contain a list of states. I would want a sales person to see only there states, a regional manager to see only states in there region and board member to be able to see all the states. They wont nesseseraly be any information in the database about the users runing the reports.
The only way i can think of doing it is using active directory groups. Is this something i can do in reporting services, and if so has anyone any examples or pointers
Cheers
Luke
--
I am trying to find the list of active connections on SQL SERVER , more and more granular way ...like below , I know a way using active monitor or Profiler or some DMVs give me some list , but is there a way to find what is the server name ,instance name connectedc to it , who is the clinet connected to it , whats the application being used now?
what database it is connected to it ? what login being used to connect ? what table and column being used right now and what kind of DML or DDL are happeming ?
I want to create a report and find Dependancay evaluation so that we will re architech or re design the existing databases ...
ServerName InstanceName Client Application Database Login Table Column Select Insert Update Delete Truncate Alter
I neet save user login form active direcotry to databases. How I can make that?
View 1 Replies View RelatedI 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
My 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] ....
I have a program which will show you users logged into a session. It's designed in MS Access and will also record citrix/terminal users. If you would like a copy of the program, download the attachment or email me at paulk@weccusa.org.
Paul Kohn
Database Administrator
Is there anyway to disable this functionality?
View 7 Replies View RelatedHi,
Imagine that I have a select something like this
select * from T where T.Name Like '%Maria%'
"Maria" would be what the user what to find. But for example in spanish we use í, ó, ú, ü... The same happens in french, german...
The user want to find Maria and does not care if another user (or himself) has inserted the client as Maria, María, Marïa or whatever.
Of course one solution is putting in the DB only the simple characters (a,e,i,o,u) and look for these, replacing the "strange" chars in the application to the "normal" ones. But if we want to be professionals we would need to insert in the DB the name in their original spelling (the image is the most important!)
Any idea, help or reference?
Thx in advance and happy new year.
David
Is there a query that I can run which will give me a list of users that have tried to use SQL resources for which they do not have permissions?
View 1 Replies View RelatedI know sysprocesses has the information, but I can't seem to find how this is done using the dmv's. sessions doesn have a db context, and requests is only active while activity is occurring.
Thanks!
Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.
I 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
Hi everyone,I'm a bit new til ms sql server and hope that anyone here can answer aquestion I have. I'm running a ms sql server 7.0. The server isaccessed by a application written in Access (help..) and sometimes Iexperience that the server performance is slowing down. When I do asp_who I find alot of the processes (or sids) with the statussleeping? Can anyone please tell med what this means, and is it ok forme to kill these processes? Since this is happening quite often Iwould like to make a script that automatically kills all sleepingprocesses, is this ok?Looking forward to hearing from anyone!B'regSql server newbee
View 2 Replies View RelatedHi,
The column waittype of master..sysprocesses table contains binary numbers.
I am looking for their codes. I know that Microsoft has published these codes for SQL Server 7 (here: http://support.microsoft.com/default.aspx?scid=kb;EN-US;244455 ), but I need the codes of SQL Server 2000.
And yes, I've checked spt_values - they are not there.
Any help will be appreciated,
Thanks,
kukuk.
My application was written in SQL 2000 where a non-dbo account could see all logged in users. Is there some way I can do this in SQL 2005?
Here is the query I run in SQL 2000:
SELECT spid
FROM master.dbo.sysprocesses, master.dbo.syslogins
WHERE master.dbo.sysprocesses.sid = master.dbo.syslogins.sid
AND master.dbo.sysprocesses.dbid = db_id()
AND LOWER(master.dbo.syslogins.name) IN ('grnl_security','log_security')
Right now, my application (where many users log in with gnrl_security) can only see thier own SPID; but I need to see all logged on SPIDS.
I have tried adding a SELECT permission to the new sysprocesses view for user grnl_security.
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.
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
On SQL Server 6.5, we tried to track down the owner of a process,which was hogging the CPU. Using Currently Activity Detail, we went to the hostname that seemed to be the culprit, but the users had not accessed SQL Server in a few days. Where is SQL Server get the hostname from? We are on an NT4.0 network, using DHCP.
View 1 Replies View RelatedI'm doing an update on a table with about 113m rows, the update-statement is fairly simple: update tab set col = null where col is not null.
The col column is mostly null.
Sysprocesses shows three rows for this statement: 1 CXPACKET (its a dual processor, 2000 box with sp3 installed), 2 PAGEIOLATCH_SH (waitresource is filled). My guess would be that the where-clause is executed in a seperate process blocking the update.
I changed the statement into update [...] set col = null; sysprocesses shows one row with PAGEIOLATCH_SH. Executing forever.
I checked other processes including those outside sqlserver but none are using the db, let alone accessing the table involved. Even restarted sqlserver to be sure there's no dead process blocking the update. Didn't help.
So I added a search condition to the where-clause, involving a clustered index in order to reduce the rowcount. The execution plan shows a 97% hit on the clustered index, but sysprocesses shows the three rows again...
So far the profiler didn't help me out either: there's a SP: CacheInsert on the update-statement... then nothing.
What should I do?
ok, I have a requirement where I need to get a list of sql commands currently being blocked.
This is very easy to do via stored procedure, and I have it working well using a vb.net console app to fire it off.
Trouble is, I need to ship it to different offices on an ad hoc basis. I don't want to install a stored procedure on each site because it'll be a one off job and there is not likely to be anyone available who would know how to even install a new sp. So, I thought I'd try and pull back the sql commands via a select statement, joining together sysprocesses and the fn_get_sql udf. This returns a table, so I presumed I could join the two together using a subquery via the sql_handle with something like this :
SELECT sql_handle,
(
SELECT top 1 [text] FROM ::fn_get_sql(sysprocesses.sql_handle)
) as sqlcommand
FROM master..sysprocesses
The error back is incorrect syntax near 'sysprocesses'. I can't see if I'm doing anything obvious wrong.
Anyone any ideas? I'm using SQL Server 2000 sp3.
Hi,
We have written various applications that have a connection table for managing users logins, we have a connect stored proc that inserts the SIPD and the login time from sysprocesses.
From time to time we clean up our connections table by queryng against the SPID and login_time in sysprocesses and clear it if these don't match. This has been fine for a few years, howver we have found recently with a client that with some SPID's in sysprocesses that SQL server is changing the login time, this is unexpected behaviour.
The client is using SQL server 2005 and this has caused us problems, we have worked around it for now but wondered if anyone new why on this particular setup why the login time would change even though they have not disconnected.
We have other clients using SQL 2005 but have never seen this issue before, I wondered if there was a setting we have not seen that can cause this.
Thanks
I was trying to find the bottle neck on our server while we wererunning a couple very large updates.I ran the following query and noticed there were a couple sleepingspid's that were really eating up the memory. One had a value of16000.I guess my question is was this query still holding on to that memoryeven though the query had finished? Why didn't SQL Server clean it up?select spid,substring(Loginame,1,10) Login,memusage,substring(hostname,1,15) Host,program_name,cmd,statusfrom master..sysprocessesorder by memusage desc
View 1 Replies View RelatedIn SQL Server 2000 you can monitor active processes by looking at the sql_handle column in sysprocesses. In SQL Server 2005 BOL says sql_handle ...
Represents the currently executing batch or object.
In SQL 2000 a non-zero value in sql_handle indicated an active process. You could not simply rely on the status column to check for an actively running process. This is not the case in 2005. I ran a profiler trace to confirm that just because sql_handle reports a non-zero value does not mean the corresponding process is active. Does anyone know why this has changed and how we are now to interpret sql_handle in 2005? Why are non-zero entries reported for processes that are not currently running?
Thanks, Dave
Hi
I am newbie in SQL Clustering. I have set up a Windows Server Cluster with 2 nodes and am having the following problem with Physical Disk resource for cluster groups:
My Default Cluster Group (named Cluster Group) has IP Address, Network Name, Physical Disk and MSDTC resources. In addition to that my Default SQL Server instance resources are also in this group. I had this initial set up for Active/Passive mode.
Now I am trying to set up a SQL Cluster in Active/Active mode. For this I have to install another instance of SQL Server in the existing cluster and make a separate cluster group for its resources. I made a new cluster group (SQL Instance Group) with an IP Address and a Network Name resource for this new instance but I dont have any Physical Disk resource to allocate to it. As such while installing the SQL Server Instance I get stuck when I'm asked to select the quorum disk to be used.
Is it possible to configure two quorum disks, one for each group?
What's the concept of dedicated disks resource for each sql instance in a group? Is this same as the quorum disk? If this is not a shared disk how do I configure a dedicated disk resource for my second cluster group (SQL Instance Group)?
Anyone could please help me out with this?
Hi folks,
We are going thru the process of scoping an active/active cluster at one site.
I was wondering whether there will be any issues with mirroring (DB by DB) off the cluster into non clustered server at an alternate DRP site.
Regards, Brian
Sutherland
Hi all, My aim to install active/active 2 node SQL 2005 cluster. I have installed sql cluster on one mode, which automatically installed on node 2.
it works great. However When i tried to install second virtual server, it is not allowing me to install.
it says already install, can anyone tell me how to install active active cluster
Hello friends,
Despiste what it is mentioned in B.O.L., in the Table SYSPROCESSES, the column KPID has almost always the value 0 !!!
If anyone knows how I can find the right link between a SPID and the corresponding KPID, I shoud be very happy to know to.
It concerns SQL SERVER 2000 under WINDOWS 2000 SP3.
Thanks in advance for any help about that ...
Does anyone knows how the field NET_ADDRESS in the master.dbo.sysprocesses table is encoded ?
May I extract the IP address or MAC address from that field ?