We're developing an application request/packaging/rollout worflow
application for our 50 site, 40,000 user company. There is a request
table, an engineering table, a distribution table, etc. etc. But, the
company has a designated "Application Owner" at each site, and each
person who will use the application must also be listed in the workflow
application. So, we need a lookup table for the owners and users:
CREATE TABLE REQUEST (
RQ_ID INTEGER NOT NULL,
RQ_BY_ID INTEGER NOT NULL,
RQ_FOR_ID INTEGER NOT NULL,
ASSIGNED_ENGINEER_ID INTEGER NOT NULL,
OTHER INFO...
);
CREATE TABLE APP_OWNERS (
RQ_ID INTEGER NOT NULL,
OWNER_ID INTEGER NOT NULL
);
CREATE TABLE APP_USERS (
RQ_ID INTEGER NOT NULL,
USER_ID INTEGER NOT NULL
);
There are many other tables, of course, some with single person ID
fields and addititional lookup tables where there are multiple people
involved like testers, package distributors, etc. I began to wonder,
why not just a single table to cover ALL the people involved:
CREATE TABLE RQ_WORKFLOW_PEOPLE (
RQ_ID INTEGER NOT NULL,
PERSON_ROLE VARCHAR(20) NOT NULL,
PERSON_ID INTEGER NOT NULL
);
The real question I have is how does one evaluate options like this?
The good news, I think, is that where I simply must have cross
reference tables because of multiple values (application owners, users,
testers, etc.) I've reduced the number of those tables to one by
specifying a single table by role. Is that a good thing or a bad thing?
I've also removed similar data from several other tables where only a
single column was needed in those tables, i.e. the requested by and
requested for fields, the assigned engineer, and several others. There
is one and only one of each for each request but the type of data, that
is an employee ID is exactly the same, so does it make more "sense" to
keep the data with the request table or the engineering table or
consolidate all ID data in an ID table?
Any thoughts on this woudl be appreciated.
Randy
I am trying to grant solely the SELECT privilige to a particular user and to a particular database.I am using Sql server management studio express to do this and SQL Server 2005.
I have created a new login and set the server roles to public, then on the user mapping page, I have checked the box next to the table I wish the user to access and have selected the database membership role - public. Next, On the Table Properties, I have added the user on the Permissions Page and Selected "Select" under the grant column
When I click on effective permissions, the select command does not show and when I try to login with that user, the table does not display.
I'm having problems with a stored procedure, that i'm hoping someone can help me with.
I have a table with 2 columns - Username (varchar), LastAllocation (datetime)
The Username column will always have values, LastAllocation may have NULL values. Example
Username | LastAllocation ------------------------ Greg | 02 October 2005 15:30 John | 02 October 2005 18:00 Mike | <NULL>
My stored procedure needs to pull back a user name with the following criteria:
If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation
Then update the LastAllocation column with GETDate() for that username.
This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln? Thanks Greg ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ CREATE PROCEDURE STP_GetNextSalesPerson AS DECLARE @NextSalesPerson varchar(100)
BEGIN TRAN
IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0 BEGIN SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC END ELSE BEGIN SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC END
SELECT @NextSalesPerson UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson
Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
I have given a user db_owner role in a database. When he creates a table using Enterprise manager the table owner is dbo. When he creates a table using Query Analyzer the table owner is the user. eg
Enterprise Manager = dbo.Table1
Query Analyer = username.Table1
This causes a problem when the user is writing web applications. Is this an error in the way i have set up permissions ? How can i make them behave the same way?
I am quite newbie, really grateful for some help how to create a sql sentence in Reporting Services that would return the best sales person from each shop.. The following returns all the sales persons from each shop
So how to select the top sales person from each shop? SELECT TOP (10) shop.name, SUM(Cd.Price) AS Sales, Personal.Name AS Salesperson FROM Purchase INNER JOIN Personal ON Purchase.Salesperson_id = Personal.Personal_id RIGHT OUTER JOIN shop ON Personal.work_id = shop.shop_id FULL OUTER JOIN Cd ON Purchase.Cd_id = Cd.Cd_id GROUP BY Shop.Name, Personal.Name ORDER BY Sales DESC
Or something like this? But how in Rep.Services???
...LEFT OUTER JOIN ( SELECT P.work_id, P.Name, SUM(C.Price) AS TotalSale FROM @Personal P INNER JOIN @Purchase B ON B.Salesperson_id = P.Personal_id INNER JOIN @Cd C ON C.Cd_id = B.Cd_id GROUP BY P.Work_id, P.Name ) D ON D.Work_id = S.Shop_id
Can anyone help me with this? The objective is to allow an application user (with db_datareader & db_datawriter database roles assigned) to be able to create tables in the assigned schema (dbo) via a new role.
-- Create User
use master
go
create login DBA with password='xx', CHECK_EXPIRATION=ON, CHECK_POLICY=ON
use AdventureWorks
go
create user dba from login DBA alter user dba with DEFAULT_SCHEMA=dbo
go
-- Create Role
use AdventureWorks
go
create role sp_ddl_role AUTHORIZATION dbo
grant CREATE TABLE to sp_ddl_role
-- assign user to Role
use AdventureWorks
go
exec sp_addrolemember sp_ddl_role, dba
go
-- Create Table statement then run with following error Error:
Msg 2760, Level 16, State 1, Line 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.
Remedy: Grant ALTER on schema::dbo to sp_ddl_role
My problem is that I want to restrict user permissions via this role to just the CREATE TABLE and granting ALTER on a SCHEMA will open up a whole lot more permissions from a security standpoint.
Question: How do I restirct this role to just the CREATE TABLE within a SCHEMA?
Hi everybody. I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1 I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME()
I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:
The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work?
The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated.
Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania
I am gonig into interview for a junior developer position. The role involves a lot of SQL based work. Training is on the job, and they know I am new to this, but they want to know what I can do with SQL server by wednesday, and obviosuly I stand a better chance if I can do a reasonable amount by then.I am assuming I can practise with offline databases, so I would like to do that. Also I was wondering if there were any simple example databases I can load up.
I have downloaded and installed the software, and would like to know how to connect and create a test database. There will be a small test in the interview and the questions are: 1. SQL Management and Data Extraction For this task you will need to be familiar with database tables, data types and constraints. There will be some administration work using SQL Management Studio along with some T-SQL queries. You will need to show use of the SELECT, INSERT, UPDATE and DELETE statements. If you do not have SQL Server, you can download the express edition for free at the following URL. http://msdn2.microsoft.com/en-us/express/bb410791.aspx
2. XSLT For this task I will be asking you to produce some HTML output displaying the data from an XML file. The concept is similar to ASP.
3. Database Design You will be given a scenario for a company that requires some database software for the smooth running of their organisation. You will need to plan and design a data structure to accommodate these requirements. You will be allowed to spend as much time on this part of the test as you wish. Key information here is going to be database normalization.
These questions dont make a whole lot of sense to be at the moment, so would appreciate a breakdown in simpler terms.
This job will be a fantastic opportunity for me to get into development, and would appreaciate any help that you guys have to offer, thanks in advance.
In a transactions table, I need to get the record for the earliest transaction date for each person or account. So far I've been doing it in 2 steps (below). Is there an easier way to do this?
Thanks
Dannie
--------------------------
SELECT
Person_ID Earliest_Transaction_Date = MIN( Transaction_Date ) INTO
Earliest_Transaction_Dates FROM
All_Transactions GROUP BY
Person_ID
SELECT
b.* FROM
Earliest_Transaction_Dates a LEFT OUTER JOIN All_Transactions b WHERE
a.Person_ID = b.Person_ID and b.Transaction_Date = a.Earliest_Transaction_Date
I have a query that pulls information on people, courses they have taken, their score and so on. I have a date parameter setup so I can run it by year. Everything works ok on the query side, I get all the information I need on all the people and the courses they have taken. However, when I run the report, I do not get a separate page for each person and their relative information. The first page shows the first name and the rest of the 700 pages list all the courses and other information, with no break. How do I render the report so that I can get a separate page(s) for each person and their specific info? I can glady provide more info/code if need be.
I have about 8 databases to integrate. All of the databases have ssno, address city...ect. I need to create a DW table with one unique record for each actual person. In other words,
Joe Smith,123 Main St, Anytown, State,....+ssno
goes into the DW table and is the same person as Joseph S. Smith,123 Main Street... and any other versions.
Could someone point me to a reference or give me an outline of how to do this in and SSIS package?
Is fuzzy logic used here?
Do I need to deduplicate the feeder systems first??
It needs to handle a situation in, for example, the Bronx New York where there could be an apartment buiding with 7 people named Jose Sanchez .
I hope I've been clear, I'm a newbie at this DW stuff, but it's fascinating. Any help would be appreciated. Thanks
I have written a trigger for audit trail of one table in SQL and it has to get the user name who is doing the changes from the application(.net) I have used the SQL user name and password while connecting the .net application to the database, and because of this whoever log in to the application and make changes on that table data, in the audit trail it still shows as the database user name.
I want to display the user name of the person logged in to the application
In the query i am using "System_user" for getting the user name. And the connection string in my application has a default uid and pswd.
I want to retain uid and pswd in the connection string but still want the query to get the name of the correct logged in user to the application.
I'm trying to do some basic stuff in SQL Server 2005 that I could do in Oracle. I'm sure there's a way to do these things, I just don't know how. My most immediate question is this: Is there any easy way to run a SQL script and have it spool to HTML tables to a file? The 'results to text' from SSMS is not adequate for display purposes. I'm trying to display table/column descriptions. Beyond that, does anyone know of any good resources that provide an 'Oracle to SQL Server' matrix so I can help figure out how to do some of these things in SQL Server? Thanks in advance, Mike
Please help me in sorting out my Problem Providing me the solution . My Problem is I have a master table with Primary key on ID field (PatientID-(Patient-Table)) and it is an Identity field And My child table has the same ID field(PatientID-(PatientDetails-Table)) and it has the relationship set And the child table has its own Primary key of its own ID field(PatientdetailsID). What I want is as soon as enter row of data into the master table (Patient-Table)and click save on my front end application(Which is ASP.Net web application) I want to update Child Table’S (PatientDetails)ID field ( ie.,PaientID in the PatientDetailsTable) in the Child Table which relates the parent table ,by doing so I want to update the Primary key field (ie.,PatientDetailsID) & ForeignKey Field (PatientID)of the child table and to create row in the child table with two columns .(PatientID,&PatientDetailsID) What I want to achieve is in my ASP.net Application as soon as I enter Master table I want to Edit Child tables (about 15) one by one like a Wizard pages which will have The ID Field(PatientID) same in all my wizard pages . I want to know whether I can incorporate triggers if so in which table (is it in Patient or PatientDetails) and I will be grateful If anyone gives the Script to-do so.I am also providing my two table sripts. Sripts:CREATE TABLE [dbo].[Patient]( [PatientID] [int] IDENTITY(1,1) NOT NULL, [Date] [smalldatetime] NULL, [UserID] [int] NULL, [FirstName] [varchar](40) NOT NULL, [Surname] [varchar](30) NOT NULL, [DOB] [datetime] NULL, [Age] AS (floor(datediff(day,[DOB],getdate())/(365.25))), [Sex] [varchar](10) NULL, [Occupation] [varchar](30) NULL, [Ethinicity] [varchar](60) NULL, [HomeTel] [varchar](15) NULL, [Mobile] [varchar](15) NULL, [varchar](40) NULL, [AddressLine1] [varchar](30) NULL, [Line2] [varchar](30) NULL, [Line3] [varchar](30) NULL, [City] [varchar](20) NULL, [PostCode] [varchar](15) NULL, CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED ( [PatientID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_User] FOREIGN KEY([UserID])REFERENCES [dbo].[User] ([UserID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_User] CREATE TABLE [dbo].[PatientDetails]( [PatientID] [int] NOT NULL, [PatientDetID] [int] IDENTITY(1,1) NOT NULL, [Date] [smalldatetime] NULL, [NHSNumber] [varchar](12) NULL, [HospitalRefID] [varchar](10) NULL, [Ovaries] [varchar](15) NULL, [ReportFromGP] [image] NULL, [LMP] [datetime] NULL, [DateStopped] [datetime] NULL, [Comment] [varchar](150) NULL, CONSTRAINT [PK_PatientDetails_1] PRIMARY KEY CLUSTERED ( [PatientDetID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[PatientDetails] WITH CHECK ADD CONSTRAINT [FK_PatientDetails_Patient] FOREIGN KEY([PatientID])REFERENCES [dbo].[Patient] ([PatientID])GOALTER TABLE [dbo].[PatientDetails] CHECK CONSTRAINT [FK_PatientDetails_Patient] I want to incorporate this through database level . I am using SQL Server2005-Express Although Iam using ASP.net C# I am new and I will not be able to do this in my front end. Please help me wth the solution. thanks rameshs_2000
When assigning permission to an authentication user to connect to a server database, if I want the user to be able to insert / update / delete data on db objects specifically tables, what permission should be assigned to that user?
My thoughts were Insert / Update / Delete; however, someone suggested that the Execute permission would do this ...
Can someone help me rewrite this query? Basically I need to check if the name stored in "CustomerName" already exist in the table "Renter". If it does not then I ncan insert the new customer name into the data table "Renter". If the value in "CustomerName" already exists in the database, then I need to bypass the "INSERT" and somehow return a value indicating that the insert was not performed. How can I do this. Here is the query I currently have for performing the insert. CREATE PROCEDURE [dbo].[VacancyGet] ( @CustomerName nvarchar(100), @ClientCode nvarchar(10) ) AS INSERT INTO Renter ( CustomerName, ClientCode, ) VALUES ( @CustomerName, @ClientCode, )
I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.
I am about at the end of my rope. My ISP (MaximumASP) has recently upgraded to Windows 2003. I have an application written in .Net / C# that has been working for almost a year now with no problems. The day they upgraded to Windows 2003, that all changed. Upon accessing a page (ANY page) that opens the database (SQL Server) an error occurs INTERMITTENTLY. The error is:
Specified SQL Server not Found
Go to this link for the full error and Stack Trace: http://www.acob.com/SQLError2.gif
Keep in mind this NEVER happened before 2003 (same exact code, no changes) AND it will NOT happen for hours at a time (same page, same code), then BOOM! If we wait about 10-15 minutes or so, the page (EVERY page that accesses the database) works again and no error. This happens about 2-3 times within a 24 hour period. I can also RESET the web application (server) and the error disappears immediately.
I have gone through ALL of my code to make sure I am EXPLICITLY closing my data connections. This problem was MUCH worse when we were using the .Net 1.1 Framework AND Windows 2003. Reverting BACK to .Net 1.0 has alleviated the problem somewhat - but not entirely.
Does ANYONE know of any bugs, problems, whatever in Windows 2003 that may cause this. It seems as though there is a Connection Pooling leak of some sort. But I cannot fix, what I cannot find. My ISP simply keeps saying - it's a coding problem. If I have code that works for over a year and then suddenly it doesn't - is it a coding problem??
I DISPOSE of the DataSet. I DISPOSE of my Connection. I CLOSE my Connection. I DISPOSE of my SQLAdapter.
I do this cleanup everywhere I initiate a connection (all in one codebehind). Additionally, the code is very simplistic: Connect, Open, Select, Bind, Dispose, Close.
Does ANYONE know what this can be??
$100 to the person who solves this problem! (Server must run for one week after solution is implemented to collect reward).
I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75
The Table:
/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/ IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL DROP TABLE [dbo].[#TestProject2] GO CREATE TABLE [dbo].[#TestProject2]( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Code] ....
The data:
--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #TestProject2 ON INSERT INTO #TestProject2 ("ID","PersonID", "PercentLoad","MonthID") SELECT 1,123456,0,1 UNION ALL
One employee has two pay rates for two different jobs:
Job A: Rate $10.00 Job B: Rate $15.00
I will be updating their record so that they only have one job going forward, Job C. I need Job C to equal their HIGHER of the two existing jobs.
I have a select statement to find what the higher rate is. However, I am not sure how I can apply the rate to be the new job's rate. Here's what I used to find the highest rate for one single person:
SELECT max(rate), employeeID FROM JobsTable inner join IDTable on JobsID2 = IDID2
WHERE JobCode in ('JOBA','JOBB') and EmployeeID = '12345' GROUP BY EmployeeID
(this returns the employee ID from one table, and the highest rate from Jobs A and B from another table)
I can get it to update to add JobC -- how can I get it to assign the result from the above query to be the rate used for Job C?
How can I identify the computer of the person running an SRS report? If I query HOST_NAME() it gives me the host of the reporting server, not the person browsing.So, for example, Melissa opens a browser from computer named AAA to the SRS and pulls up her report. How do I get that report to show that the user is browsing from AAA?
In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?
I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.
Below is the schema structure:
Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names Patient_Info: PatientID, table contains patient ID and other information Patient_PersonRelation: Person_ID, patientID, RelationType Address: Contains address of all person and patient (key PersonID) Phone: Contains phone # of everyone (key is personID)
The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).
Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) for the given connection string Dim connstring As String = "Data Source=Northwind;Initial Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"
I have SQL server 2005 (Developer edition) installed and I want to add a database role to a database role. It is working on my SQL 2000 server,however, when I tried to do the same thing on SQL2005, It didn't allowed me.
When I go to the Add role and then adding members to the role, the browse screen does not allow me the choice of object type Roles. It only shows me "Users".
Can someone please help me with this and provide me some information of how / what should I set to get the Roles in object types list so I can add a role to a role.
Hi , I'm looking for a way that SQL-S7 tells me, what db-role I have. I want to avoid that the server generates an error message when I'm not authorized to access an information. So I could give the users only that information they are authorized for.
May be there is a stored procedure or a template ?
Or is there an other way for controlled checking whether I have the rights or not ?
When I assign a role to a user, the user is not actually getting the permissions defined in the role. I am forced to give the permissions directly to the user..any idea why it's ignoring the role which is assigned?
Hello: I have read that giving a User the DB_DDLAdmin role in SQL might causes problems with ownership chains in the future. Since the User will have ownership to all objects created, what preventive measures can one take to help avoid any problems which might loom in the distant future due to ownership chains? Thank you, -H
I am new at this and we encountered a problem. Can names in the public role be deleted? We have some names that need to go - however the delete option does not high light?