Stored Procedure To Get All Users Or All Users Of A Specific Country
Apr 30, 2008
hi all
i'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
}
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
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
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 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.
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:
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,
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.
[dbo].[PortalUser]( [PortalUserID] [bigint] IDENTITY(1,1) NOT NULL,
...
[dbo].[Role]( [RoleID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](250) NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_Roles_IsActive] DEFAULT ((1)) ...
[dbo].[PortalUserRole]( [PortalUserRoleID] [bigint] IDENTITY(1,1) NOT NULL, [PortalUserID] [bigint] NOT NULL, [RoleID] [bigint] NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_PortalUserRole_IsActive] DEFAULT ((1))
I'm asking to get a list of portalUsers that do not have a PortalUserRole records for the rolename I'm checking against. And don't ask me why the person who coded this is searching on rolename not ID. But this is how we're doing it for now.
I have an archival process on a large database that runs once a month.At the beginning of the process the triggers and indexes on thetables whose data is moved are dropped, the data is moved and then thetriggers and indexes are recreated at the end. This produces amassive improvement in performance.The problem is the process is supposed to run on users accounts (thatsthe way the front-end is set up) and they don't have the neccessarypermissions to drop & create triggers & indexes. I can't see any wayto give them permissions only on specific tables or triggers/indexes.Nor does giving them permissions to the stored procedures that do thedropping & re-creating work, DDL permissions don't seem to beinherited the way they are with tables.Is blanket rights to drop & create objects through the db_ddladminrole the only way users can get rights?Thanks,K Finegan
Hello, I was wondering if someone could point me in the right direction on how to do this....I have a table that is a many to many relationship between userID's and courseID's. for instance (example data for each row - dont let the spaces between rows throw you off I am just adding them to seperate the different users)userID = 1, courseID = 1userID = 1, courseID = 2userID = 1, courseID = 3userID = 2, courseID = 1userID = 2, courseID = 3userID = 3, courseID = 1userID = 3, courseID = 2userID = 3, courseID = 3I want to be able to check for a number so in this case "2" which I would like to return all users who do not have a courseID = 2. In the example above the query would return the user with the userID of 2.Any help in how I should go about doing this would be greatly appreciated. Is there a function I can use? I apologize I am extremely new to SQL
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
hi..i need help on how to create user logs with a specific transaction he made. for example, he added new item in the database, the grid wud contain user id, date, time and transaction. help po.. tnx!
Currently I am using SQL server 2012 and would like to implement database audit specification on specific users in my database. These are the users in my database name Payroll :-
In my database audit specification settings, I would like to capture any SELECT,UPDATE,DELETE and EXECUTE command for users PayrollAndy.Bred & PayrollArpit.Shah only since they owned db_owner access. However, I am unable to capture any single command from both users. I do not want to put 'Principal' as public since I just want to capture both users activity.
Is it I miss out anything? Is it because of windows login account?
Now, I know that I can access each folder (f.e. Sales) by directly putting it into the URL. Is there a possibility to enable every user to the root folder and then denying access on specific folders? F.e.: Our Head of Sales should be able to navigate to the root folder and then dcecide whether he wants to enter the Sales or the Sales Export folder.
Is this possible? I tried a few things, but nothing works.
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
I am new to SSRS. I am doing some report models for Ad-hoc reports. Im my database i have some users and their roles according to my requirement. User should only see the particular reports if he have privilege. how to customize...
User will access Report Builder to do adhoc report.
For example I have 5 report Models rptModel1 rptModel2 rptModel3 rptModel4 rptModel5
In my data base there are 3 users. user1 have privilege to view rptModel1, rptModel3 user2 have privilege to view rptModel4, rptModel5 user3 have privilege to view rptModel2, rptModel5
When Particular user access the report builder, There i want to show only that particular Report models... is there any posibilities to do this please Help me.
I am in a project where we are using a vanillla Reporting Services 2005 with the builtin report portal. No sharepoint integration yet.
We have successful deployments where we limit access to different folders based on AD-accounts and groups. In this particular case I have a folder for which I would like to allow access to all AD-users within the entire company.
So basically I know how to limit access but I don't know how to enable access for everyone. Is there a simple way to do it? I have googled and search mshelp but I couldn't find anything. I will admit to the search being quite quick but as usual time is short.
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....
i have a report that runs on a huge table rpt.AgentMeasures , it has 10 months worth of data (150 million records as of today and will keep increasing). i have pasted my proc below , the other tables that are joined to this huge table do not have more then 3k records.This report will be accessed by multiple users (expecting 20 ppl). as of now this reports runs for 5 mins if i pull for 1 month worth of data. if it is wise to use temp tables.
Hi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel.
I have created a stored procedure in the master database that can be run against any database on the server. I call this procedure from a sql server agent job. In this job I specify the database I want the procedure to run against but it always seems to run against the master database (presumably because the procedure is in this database). I can't switch database inside the procedure so what can I do other than creating a copy of the procedure in every database?
I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.
FYI: All columns in the table design are set to null by default.
The sql listed above does execute the stored procedure called [dbo].[sproom] successfully and returns all the data all the rows from the stored procedure multiple times. However can you tell me the following:
1. How can I have the stored procedure return distinct rows? 2. I want the stored procedure to return selected columns. I tried using the OUTPUT parameter for some of the columns, but I got the error message, "Procedure or function spHomeroom has too many arguments specified.".
A solution might be to change the stored procedure, but I would prefer not to since this is a generic stored procedure that I believe a lot of t-sqls and stored procedures will use.
I have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.
Currently, my block looks like this: IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL BEGIN
I can get the current minutes of the current time by using:
Select DATEPART(MINUTE,GetDate())
I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there.
I have moved several SQL 7 databases from one server to another. The databases are attached, and I can see all the tables,etc. However, I can only see 1 user, that being dbo, therefore users cannot log into the db. HELP