See Application Users (View Server State) Within A Stored Procedure
Mar 17, 2008
Hello, i have a problem regarding stored procedures and view server state.
I have an application with a lot of stored procedures, one of them checks data of the connected users.
In SQL 2000 i had no problem getting this information, but in SQL server 2005 i do.
my stored procedure looks like this:
ALTER PROCEDURE [dba].[applsp_GetConnectionInfo]
(
@DBName varchar(100)
)
WITH EXECUTE AS OWNER AS
BEGIN
SET NOCOUNT ON
DECLARE @sCollationMaster VARCHAR(128);
DECLARE @sSqlString VARCHAR(900);
-- Determine collation from master database because collation from master and ultimo database may differ
SELECT @sCollationMaster = CAST(databasepropertyex('master', 'Collation') AS VARCHAR);
SET @sSqlString =
'SELECT max(status) AS Status, max(isnull(SCISUSENAME, ''ULTIMOLOGIN'')) AS Login
, MAX(Rtrim(Rtrim(convert(varchar(255), nt_domain)) + nt_username)) AS NTUser
, max(Rtrim(hostname)) AS Host, MAX(Rtrim(program_name)) AS Program
FROM master.dbo.sysprocesses JOIN dba.SCONNECTIONINFO on SCISPID = CAST(spid AS VARCHAR)
AND ( SCISUSENAME = ISNULL(loginame, '''') COLLATE ' + @sCollationMaster + ' OR ISNULL(loginame, '''') = ''ULTIMOLOGIN'')
WHERE ...... AND DB_NAME(dbid) = ''' + @DBName + '''
GROUP BY hostprocess
ORDER BY Login
';
EXEC(@sSqlString);
END
I've granted view server state permissions to my user 'dba' which is the db_owner.
When i execute the query in the stored procedure seperatly as dba i get all the info i need, but when i execute the stored procedure i don't see anything.
I seem to have the same problem with sp_who2
Executing it gives me information about everyone but when i put in a stored procedure like this:
In SQL 2005 (we use Enterprise 64-bit SP2), the users cannot see thetext of the stored procedures, functions, etc.This is a production database, so I cannot give them rights to modifythem, but they need to be able to see what the procs are doing.I didn't have this problem in 2000 - how can I adjust the privilegesto allow them to view the contents of the Programmability objects?thanks for any insight!!Tracy
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?
hi, Can someone tell me when to use SQL Server View as oppose to Stored Porcedure? Currently we do everything with SQL Server stored procedure. I mean, even if we have to display some report, we use Stored Procedure. In what situations and senarios views are better and one should consider them over Stored Procedure?
New SQL User...v2005. I have been directed to Grant "View Server State" permissions to a user. However, I don't see it in the list of choices I can grant access to in Permissions, nor is there a visible hyperlink in an area that it is expected to be. I have also tried setting that permission in a query window to no avail as well. I can provide additional info if someone would like to work with me on this issue. Thanks!
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I have read the various posts regarding the thread abort issue. I have a stored proc (CLR-C#) which makes and ldap call to a novell NDS tree and attempts to pull about 40k+ records which I would then insert into a sql server database.
I have set up all the requrements in terms of clr enabled etc...
I have written my class and I can run the sp when I limit the records returned. However when I go to grab all the records, (which takes some time), the sp craps out with the error message in the title of this post.
The assembly is running under unsafe permissions.
If I run the sp once, it works, then again and I may or may not get an 6522 (memory error). I added the search parameter to break down the records returned (so I return a's then b's etc...) . BTW: This works fine ouside SQL Server, I can run this as an ASP.Net app (I prototyped it with a test harness aspx page).
Basically what the class does is connect to novel nds directorypull records based on search criteria (blank for all)dump records into datatableserialize datatable to xmlcall another sp to take xml and insert into tabledisconnect from treeAny comments/suggestions apreciated.
Thanks,
Stephen
Here is the class (editied to protect privacy):
using System; using System.IO; using System.Text; using System.Collections.Generic; using System.Data; using System.Data.SqlTypes; using System.Data.SqlClient; using Microsoft.SqlServer.Server; using Novell.Directory.Ldap;
namespace EDSPullInSQLStoreProc { public class EDSPROC { [Microsoft.SqlServer.Server.SqlProcedure] public static void EDSPull(bool IsProd, bool IsSecure, string searchParam) { //variable declarations string __LADPHost_DEV = "xx.xx.xx.xx"; string __LADPHost_PROD = "xx.xx.xx.xx"; int __LADPPort_NonSecure = xxx; int __LADPPort_Secure = xxx; int __LDAPPort; string __LDAPUserDN_DEV = "some string"; string __LDAPUserDN_PROD = "somestring"; string __LDAPUserDNPwd_DEV = "apassword"; string __LDAPUserDNPwd_PROD = "apassword"; LdapConnection oLDAPConn; DataTable oDTEDSData = new DataTable("EDSUSER"); string strErrResponseMsg = ""; SqlPipe oSQLPipe = SqlContext.Pipe;
oDTEDSData.Columns.Add("LastName"); ...a bunch more columns
DataRow oRow;
//make the ldap query filling the datatable LdapSearchConstraints oEDSSearchConstriants = new LdapSearchConstraints(); oEDSSearchConstriants.MaxResults = 0;
I am maintaining an application where most of the business rules are inTriggers, Stored Procedures and User Defined Functions. When a bugarises, it can get very tedious to debug. Today for example, I wantedto modify a function that was being called by a trigger. The problemis that I don't want to change the function, for fear that it is beingcalled by one of the other SP's or triggers in the database (there arehundreds of them)Essentially, I need a tool that allows me to view where functions andsp's are being referenced from. At the very least, I'd like to performa "full text search" in the database objects, so that let's say I havea function named "fn_doSomething", I can search the schema for thisstring and get all the places where it appears.As you can see, I'm in the dark here. I've never worked on a systemwhere all business rules are at the database level. If you know of atool that does what I describe above, or anything else that wouldfacilitate my life, please let me know!Thanks for your help,Marc
Hello, without going into the politics of why I'm asking, does anyone have a compiled list of functionality that only sysadmin's could do in 2000 that VIEW SERVER STATE permission opens up in 2005?
From the documentation, so far I've found fn_get_sql (and it's dm view) but I'd really like a complete list.
The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
I have a permission problems with user create for service.
I created an user without login in a database. I use this user for service broker activation procedure.So, this user have a certificate to exports in other database.
To generalize activation procedure of many service broker queue I try to use this piece of code :
DECLARE @QueueName nvarchar(MAX) DECLARE @queue_id int
SELECT @queue_id = queue_id FROM sys.dm_broker_activated_tasks WHERE spid = @@SPID
SELECT @QueueName = [name] FROM sys.service_queues WHERE object_id = @queue_id
To use this DMV user need VIEW SERVER STATE grant but this is an user DB and I can't give this grant to user.
I am trying to come up with a stored procedure to grant all usersdb_owner role (rather than have to manually check the users eachtime)Thanks in advance for your helpTY
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
I need to create a stored procedure for total count of the user's. If User from front end invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."
But now we have changed the concept to get the ISID (name of the user) and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .
After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.
if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.
Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).
Here is my sample code:
I need to sum it up to get the total user's from both the table but I should get 1 instead of 2
SELECT (select distinct COUNT(*) from dbo.test where New_Values like '%invited%' and Created_By= 'sam' + (select distinct count (*) from dbo.test1 where invited_by ='sam'
Situation: If possible, create one stored procedure for Crystal Reports(CR). For any CR users ou there, looking for coding suggestion also. Thank you for your assistance.
Currently, CR has a header(Report Header) coming from 1 to 1 tables, there is a parameter which is passed in, allowing it to retrieve one record for the header(report header or RH).
The CR then has 4 subreports in which each has its own stored procedure. This I believe happens because the Report Header records relationship to the subreport is 1 to many. The 3 remaining subreports relationship to the Report Header is also a 1 to many. The main problem is the subreports is that there may or may not have any records based on this, the subreport is suppressed within CR and thus there can be alot of unused white space on the 1st page and one of the subreports prints on a 2nd page when it could have been on the 1st page.
Example:
Main Stored Procedure(sp) RH Tables: aaa, bbb, ccc, ddd are 1 to 1 record tables and have a @xyz parameter.
Results of subreports and their associated procedure are varchar(8000) decriptions. Each line should be counted in some manner in the stored procedure(sp) and then should be counted in CR to avoid excess white space. To complicate matters subreport 2 to has font, bold, showbox but can have different font sizes. These variations could cause different line space requirements. Any ideas?
Each line should be counted in some manner in the stored procedure and then should be counted in CR. There is a count of records for each the main stored procedure.
RH has 1 record to many records in subreport 1 with same @xyz parameter. RH has 1 record to many records in subreport 2 with same @xyz parameter. RH has 1 record to many records in subreport 3 with same @xyz parameter. RH has 1 record to many reords in subreport 4 with same @xyz parameter.
Because of the relationships, its seems impossible to create one stored procedure which give in one select statement with all the 1 to 1, 1 to many relationsips, as stated above. I thought concatinating i.e. 3 records together and then parsing it out some how in CR, along with utilizing the i.e. 3 record count to help count lines. Thought of some how creating a temporary table matrix for 8pt - 28pt for line and spacing considerations.
I have an ODBC/C program and I want to call the stored proceduressp_addlogin and sp_adduser in it. Can someone please provide me withsome sample code showing the best way to do this.
I am trying to setup a stored procedure that runs through a Reminders table and sends an email to users based on DateSent field being smaller than todays date. I have already setup the stored procedure to send the email, just having trouble looping through the recordset.
Code Snippet
CREATE PROCEDURE [dbo].[hrDB_SendEmail]
AS
BEGIN
DECLARE @FirstName nvarchar(256),
@LastName nvarchar(256),
@To nvarchar(256),
@ToMgr nvarchar(256),
@Subject nvarchar(256),
@Msg nvarchar(256),
@DateToSend datetime,
@Sent nvarchar(256),
@ReminderID int,
@RowCount int,
@Today datetime,
@Result nvarchar(256)
-- Get the reminders to send
SELECT
@ReminderID = r.intReminderID,
@DateToSend = r.datDateToSend,
@FirstName = e.txtFirstName,
@LastName = e.txtLastName,
@To = e.txtEmail,
@Subject = t.txtReminderSubject,
@Sent = r.txtSent
FROM
(auto_reminders r INNER JOIN employee e ON r.intEmployeeID = e.intEmployeeID) INNER JOIN ref_reminders t ON r.intReminderType = t.intReminderTempID
From the code you can probably tell I am new to writing stored procedures, so I apologise for any obvious errors. My major problems are :-
how to loop through each record
how to get todays date
whether the struture of the procedure is correct Also, if you think there is an easier way or a better method, please suggest it. I am open to any suggestions you may have,
Hi,I am developing application in asp.net 2.0 using C#. My back end is sql server 2005. I also use microsoft enterprise library 2006.My problem is:First i pass three parameters to stored procedure from the application. After i increase one more parameter in both application as well as stored procedure, the application gives an error that parameter does not match with the stored procedures parameters.after couple of hours when i restart the machine and again run the application it works fine with four parameters.does sql server 2005 stores the parameters in cache??
Hey all, I've got a question and after doing some research I've found only a vague reference but no clear answer.
I have a java app that will be passing parameters to my stored procedure. I'll grab the requested info from the tables but instead of sending it back to the java app that sent the request, I need to send it to a "different" java app (the second java app will not be running at the time).
Can someone point me to a good source for executing java applications from a stored procedure?
Hi,In SQL Books Online in the section on @@Error it gives the followingexample:-- Execute the INSERT statement.INSERT INTO authors(au_id, au_lname, au_fname, phone, address,city, state, zip, contract) values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)-- Test the error value.IF @@ERROR <> 0BEGIN-- Return 99 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"RETURN(99)ENDELSEBEGIN-- Return 0 to the calling program to indicate success.PRINT "The new author information has been loaded"RETURN(0)ENDGOHow do I access the value returned by the RETURN statement (i.e. 99 or0) in my asp application that called the stored proc.Sometimes rather than just return an integer signifying success orfailure I've seen examples where the id of the newly added item isreturned on success and perhaps -1 if the operation fails. Theseexamples make use of ouput parameters to achieve this. If theoperation succeeds then then the output parameters value is set to thenew id and this is accessed from the calling application.E.g.IF @@ERROR <> 0BEGIN-- Return -1 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"SELECT @MyOuptputParameter = -1ENDELSEBEGIN-- Return id to the calling program to indicate success.PRINT "The new author information has been loaded"SELECT @MyOuptputParameter = @@IDENTITYENDWhy go to this trouble if you can use the RETURN statement?
I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master] GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/ SET ANSI_NULLS ON GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
Hi, I have a simple web application which calls a stored procedure. The stored procedure operates as a transaction and runs for several minutes. I've created a partial class to set the SQLcommand timeout property to avoid any timeouts, which works fine. Unfortunately though, when the application is run in the production environment, it ends in an error after a certain amount of time (maybe a couple of minutes - not exactly sure), which seems to be the same each run. It doesn't appear to end the stored procedure though, which results in locking the tables. It runs fine in the development environment, and it doesn't appear as though any error information is provided when the application crashes. I'm assuming that the ASP.NET application is timing out for some reason, but the stored procedure itself is fine. I can run it directly from SQL server without any dramas. In the Virtual Directory configuration within IIS, I have the script timeout period set to 1200 seconds. The Default Web-Site timeout property is set to 120 seconds, but I'm assuming that this is only for internet connection timeout, not database transaction timeouts. Any information as to what may be causing this is appreciated. Thanks
I have a stored procedure that works when executed in query analyzer. (It is also way too long to post here) When called from my application ado.net returns the error:
Invalid object name #idTable
If I run the proc in query analyzer using the same parameters (copied from quickwatch while debugging) there is no error.
While very complicated, this procedure runs quickly so timing out is not an issue.
Does anyone know why a proc would run in query analyzer and not in an asp.net/c# application?
I am searching for some information on achieving performance improvement by spawning multiple threads in single Stored procedure or rather say within Single Database connection. We have a batch process that updates around 200 tables and each table update takes around 2 mnts. I am trying to optimize this by running these updates in parallel rather than sequential. These all tables are mutually exclusive. I have written a stored procedure which updates these tables in loop. Concern is that every update statement waits for other to get over. I am calling this Sp from Java application. One crude way will be opening multiple connections to database each running separate T-SQL statement. It comes with lot of overhead in opening connections .Is there any way I can force explicitly in T-SQL stored procedure to spawn a new thread for every Update statement. In case I try to do same process from a Java connection.. is there a way I can open multiple threads for each statement under same database connection.
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
I'm modifying a pretty big web application and the programmer who built it used all stored procedures and no views. Does anyone know why someone would do this? I realize that you can't pass parameters with views and insert/update/delete records with views, but he even used stored procedures for queries like: SELECT * FROM myTable WHERE myVal > 0 ORDER BY myVal Is it more efficient to put this in a stored procedure compared a view?
Are there performace benefits to using a select from a View instead of a stored procedure that returns the same dataset? I am concerned about when we ramp up to 100's of users.
I like the security of using stored procedures. It seems I am able to do anything with it that I can with a view. Why would I choose a view over a sproc?
Hello all I am not quite a beginner but not an expert at SQL. I'm kind of in a bind and need some help. I have a table that shows me statuses of tickets (open, pending, closed), some tickets could have as much as 25 rows/ticket. I want to try to avoid that but at the same time keep track of the time. Here's what I need to happen...
with the data example below I need to take the ((closed date - first open date) - total of Waiting time). This will give me total time duration of the ticket. I'd like to either write a stored procedure or create a view that would do this for me. Any one have ideas?
