How To Sort This Mess Out (owners And Permissions)
Feb 6, 2007
Hi,
I've been asked to sort out a mess as follows:
two dbs on one server...
first db has a user called 'bob'.. that's the login on the server and also the login name on the db in question.
this db has several SP's owned by 'bob' which need to copied to the second db.
Here's where the problem starts.
Somehow, this second db has the standard users it needs, however the db User has a LoginName of 'bob'..
So basically if I try and create a user of 'bob' on the second server which will then allow me to copy these SP's across it says it already exists.. but if I just go ahead and try and copy the SP's it says the User does not exist.
Just to clarify what I mean..
I go into the 2nd db using EM.
go to users:
the visual that appears shows me several users in order with their
Name | Login Name | DB Access
the dbo user is set as:
dbo | bob | 2nd db
basically what I need (I think) is for that login Name to be removed from the dbo user and applied to specific user called bob...
how do I do that?
View 6 Replies
ADVERTISEMENT
Nov 11, 2007
Hi
I had sql server 2005 standard edition and express edition and sql 2000 desktop edition all installed on my xp notebook.
Whe i tried to create a maintenance plan in sql 2005 standard edition i got an error saying that it couldn't load something (which looked like it might be dll) so I tried to reinstall sql 2005 standard edition over the top to replace the dlls. However the install wizard told me that it can't do anything as all components are already installed!
So i decided to remove SQL 2005 and start again.
In add remove programs i removed various components (like vss writer, and one or two others) then when i clicked on SQL 2005 to remove it I got a message saying that it had already been removed and would i like to remove it from the add remove programs list.. so i said yes.
It actuallly appears that it is installed (after a fashion) although i can't connect to it anymore in ssms. If i put the cd in to try to reinstall it still tells me that it's already installed....i'm stuck now!
So.. i need to do something drastic to remove sql 2005 (as i can't remove it from the add remove programs and the CD won't let me reinstall over the top)
Can anyone help me with any suggestions to physicall remove it
View 1 Replies
View Related
Mar 20, 2008
I am trying to verify and create new user based on Billing Statement information. The user may not be the ID of the statement but has a
relationship to that ID.
Here is the data I will be passing:
Statement PrepDate
Statement PatBal
BillTo Address
BillTo Name
Patient DateofBirth
Patient Name
relevant table info:
BILTOS
BillToID, Name (user match), Address1(user match)
STMTHIST
BillToID, PrepDate (user match), PatBal (user match)
PATIENTS
BillToID, Name (user match), BirthDate (user match), Email, PatientsID
PATWEB
PatientID, UserName (user input), PIN (user input), Question (user input), Answer (user input)
I also check to make sure the username doesn't already exist in the PATWEB table
here is what I have so far that is not working (table reference not unique - BILTOS)
INSERT INTO PATWEB (PatientID, UserName, PIN, Question, Answer, LastAccess)
SELECT PATIENTS.PatientID, 'bcolladay@pdsmed.com', 'osx5ief7*', 'What', 'green', CurDate()
FROM PATIENTS, BILLTOS
JOIN PATIENTS BILLTOS ON PATIENTS.BillToID = BILLTOS.BillToID
WHERE not exists (select * from PATWEB Where PATWEB.PatientID = PATIENTS.PatientID)
AND PATIENTS.BirthDate = '1971-05-03'
AND PATIENTS.Name LIKE 'lui%'
AND STMTHIST.BillToID = PATIENTS.BillToID
AND STMTHIST.PatBal = '215.00'
AND STMTHIST.PrepDate = '2008-03-05'
AND BILLTOS.Address1 = '987 Worley St'
AND BILLTOS.Name LIKE 'Lui@'
so I am checking to see if the billing statement and other info they supplied matches before I create the account in PATWEB.
I appreciate any help, thanks
View 2 Replies
View Related
May 24, 2007
Say for example I have the following 2 database tables, the first one contains the old employee data, and has the fields shown below:
oldEmployeeID
FirstName
LastName
DateOfBirth
HiringDate
TerminationDate
and another one containing the new employee data with similar fields but instead of oldEmployeeID, it is showing the newEmployeeID.
During the conversion process, something were messed up and instead of putting in the original hiring date of the workers into the new employee database, the conversion date was put in, which, depending on the mood of HR ladies, could be any date, and at the same time, of course, new employee join the company, and we assume their hiring dates were entered correctly. On top of that, there are some employee who were terminated before the conversion took place but we still need to keep a record of that.
And I created a third table, say, emplyeeAll with similar fields to the employee data tables.
So here is what I need to do: if the firstName, lastName and DateOfBirth in the old employee data table and the new employee data table matches, I would assume they are the same employee, hence I would put the information for the employee obtained from the new employee data table to the employeeAll table, with the Hiring Date changed to the Hiring Date of the old employee data table (and do not copy the record from the old employee table to prevent duplicates), otherwise, I would simply copy and paste the data in new and old employee table to my employeeAll table.
I know this is really confusing, but...well...hope you know what I am saying...
Is it possible to have a SQL statement for all these? If so, how should the statement looks like?
Thanks a lot!
Regards,
Anyi
View 6 Replies
View Related
May 7, 2008
I'm trying to see the properties of a database and I'm getting the following error, even when trying to connect as sa.
Property Owner is not available for Database '[EPro]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
any ideas? thanks in advance
View 1 Replies
View Related
Jul 23, 2005
It has been suggested by our DBA group that all developers whenworking on objects within a database create objects not under the DBOowner but under individual owners eginstead ofdbo.sp_getUsernamethe stored procedure would besmithJ.sp_getUserNameI have never seen this used before in sequel development although Ibelieve it is a method used by other database ingress/oracle ?I have never seen any one do this and can't see any benefit accept inmaking the developers life more complex as the stored procedures,tables etc would need to be renamed to belong to the dbo group oncethey were moved onto our production server. In addtion code callingthem would need to be modified after testing as we prefix our .Netcalling with the dbo prefixeg command.CommandText = "dbo.[usp_UpdateSavedSearchId]"Has any one followed this owner convention and are their any pros andcon's with it.
View 2 Replies
View Related
Mar 15, 2006
"" Then MM_LoginAction = MM_LoginAction + "?" + Request.QueryString
MM_valUsername=CStr(Request.Form("userId"))
If MM_valUsername "" Then
MM_fldUserAuthorization=""
MM_redirectLoginSuccess="/private/private.asp"
MM_redirectLoginFailed="/private/privatelogin.asp"
MM_flag="ADODB.Recordset"
set MM_rsUser = Server.CreateObject(MM_flag)
MM_rsUser.ActiveConnection = MM_conConsumer_STRING
MM_rsUser.Source = "SELECT LastName, Password"
If MM_fldUserAuthorization "" Then MM_rsUser.Source = MM_rsUser.Source & "," & MM_fldUserAuthorization
MM_rsUser.Source = MM_rsUser.Source & " FROM dbo.PLStaff WHERE supervisor != 570 and LOWER( left(firstname,1)) + LOWER(lastname) ='" & Replace(MM_valUsername,"'","''") &"' AND Password='" & Replace(Request.Form("password"),"'","''") & "' and Emp_state = 1 "
MM_rsUser.CursorType = 0
MM_rsUser.CursorLocation = 2
MM_rsUser.LockType = 3
'response.Write(MM_rsUser.Source)
MM_rsUser.Open
If Not MM_rsUser.EOF Or Not MM_rsUser.BOF Then
' username and password match - this is a valid user
Session("MM_Username") = MM_valUsername
session.Timeout = 30
If (MM_fldUserAuthorization "") Then
Session("MM_UserAuthorization") = CStr(MM_rsUser.Fields.Item(MM_fldUserAuthorization).Value)
Session("manager") = 1
Else
Session("MM_UserAuthorization") = ""
End If
if CStr(Request.QueryString("accessdenied")) "" And false Then
MM_redirectLoginSuccess = Request.QueryString("accessdenied")
End If
MM_rsUser.Close
Response.Redirect(MM_redirectLoginSuccess)
End If
MM_rsUser.Close
Response.Redirect(MM_redirectLoginFailed)
End If
%>
Where do I find out the name of the table being accessed to authenticate the user?
View 2 Replies
View Related
Sep 5, 2007
Hi Forum,
Can a DataBase have more than one DBOwner?
If so, is there a query to return the list of DBOwners(dbo)?
Regards,
Naveen
View 1 Replies
View Related
Mar 25, 2008
We recently had an issue where reports deployed under an account that was modified would no longer run subscriptions without deleting the reports and redeploying under a different account.
Is there a way to change the owner of all reports on the server? scripting or web service tips would be fine.
Thanks
View 3 Replies
View Related
Apr 9, 2001
Fairly new to DBA work. In error log receiving message Error 1105 Can't allocate space for object 'Syslogs' in database 'msdb' because the logsegment is full.
I've looked in books and is this a serious problem, as I've tried to increase the size of the MSBD device but it will not allow me it says the Max size allowed is -1197MB...Is this right
Any help would be good...
View 1 Replies
View Related
Mar 2, 2007
hi
i have a SSRS report where matrix is embedded into table.
when i view the deployed report in IE, it works fine
But when i view the report in FIREFOX browser.. the report for toss..
the alignment of the report is completely screwed up :-(
My client badly requires Firefox support for all their reports
Any suggestion and help would be appreciated
Thanks
View 5 Replies
View Related
Jun 29, 2004
Hello,
I once bookmarked an EXCELLENT script, using SQL-DMO to monitor disk space on this site. I unfortunately didn't cut and paste it, and when I went back to it, the link comes up blank.
Is there anyway to reactivate it? Or if anyone has it, please, PLEASE, post it! It was better than xp_fixeddrives, and I need it asap on a project.
Here is the link under SCRIPTS on this site, DISK SPACE ALERT:
http://www.databasejournal.com/scripts/article.php/1470811
Thank you much!
View 3 Replies
View Related
Apr 28, 2006
Hi everybody!
I create my database by restore from server’s backup. Every object there is owned by dbo77 user. I want to execute stored procedure in following way: exec sp1 and not – exec dbo77.sp1, when I connect with the same user. I create new Login – dbo77 in Securities folder of my server (LOCAL). And when I connect by this user to that database, I still can’t use syntax: exec sp1.
An error says that my login dbo77 does not have an associated user name. When I try to mark that database in dbo77 ->property->Database_Access it writes an error that dbo77 already exists.
How can I make this db work with syntax exec sp1, if I login with same login name as the owner of sp1?
View 11 Replies
View Related
Jul 23, 2013
I want to get all databases with it's owner Name ,I think I need to join following script result with sys.databases or sys.sysdatabases.I want a result set  like this:
DatabaseName   db_Owner
master          sa
pubs           valia
pubs           eli
pubs           maryam
AdentureWork    eli
this script get all owners of the database that is in use:
Use DatabaseName
GO
SELECT  members.name MemberName, roles.name RoleName,roles.type_desc,members.type_desc
View 10 Replies
View Related
Aug 7, 2007
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
View 1 Replies
View Related
Aug 2, 2006
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
View 5 Replies
View Related
Oct 26, 2006
i encounter this error..
Cannot sort a row of size 8107, which is greater than the allowable maximum
of 8094.?
why this error occur? can someone explain? how to avoid this? thanks!!
View 1 Replies
View Related
Dec 10, 2007
We are using a modeling technique called Anchor Modeling in our data warehouses. You can read more about the technique itself at our homepage http://www.intellibis.se, where we have published a fact sheet and a recently held presentation (TDWI European conference). One of the features with this technique is its simple way to historize data. This is done by having a fromDate column which together with the surrogate key will yield a unique combination. On the tables that has this kind of historization we add a primary key, which in turn will create a clustered index, with the following specification (surrogateKey asc, fromDate desc). This will physically order data on the storage media according to the specificed columns and ordering. Now I move on to create a "latest view" of this table which does a subselect to find the latest version for every surrogateKey using max(fromDate). Should not the optimizer now figure out that data is ordered so that the latest version always comes first for every surrogateKey, hence any sorting would be unneccessary? If I look at the actual execution plan after running a query that uses the view there is a sort in the plan, but the cost is always 0%. Does this mean that it did not sort the data, or that it did call a sorting routine, but it actually took very little time to do the sorting? If so, is there a reason that is has to do the sorting or could it have been left out by an even smarter optimizer?
I would also like to applaud the people behind the optimizer, since it will figure out which tables are in fact necessary to query and eliminate others, even if I have left joined them into the view I am using. This speeds up performance and makes anchor modeling feasible. Unfortunately optimizers from other vendors seem to have trouble doing this...
Regards,
Lars
View 2 Replies
View Related
Mar 11, 2008
I've been racking my brain all day and I finally decided to ask for help. I've got two tables with rows from the first that need to be sorted by the second. The problem is that the rows don't always exist in the second table. I've tried various forms of INNER, LEFT, RIGHT, OUTER, LEFT OUTER, CROSS, etc., etc., etc. and nothing (oh yeah UNION too). Every time I get close, I lose the records that don't have matches.
Something close-
SELECT A.IDDoc, B.First
FROM A
LEFT JOIN B
ON A.IDDoc = B.IDDoc
WHERE B.Dept = 'A'
ORDER BY B.First
Example Data
Table A
IDDoc Document
---------------------------
1 1467.doc
2 8722.doc
3 A47F.doc
4 88DQ.doc
5 ABCD.doc
Table B
IDDoc Dept First
----------------------------
1 A John
2 A Bob
3 A Ralph
4 A Diane
Results I Want
IDDoc First
-------------------
5 NULL
2 Bob
4 Diane
1 John
3 Ralph
Any help is appreciated. If I've posted in the wrong forum, please feel free to direct me to a better one.
Thanks in advance!
Jim
View 3 Replies
View Related
Oct 12, 2006
SELECT
LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' +
RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date,SUM(CASE a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming,SUM(CASE a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design,SUM(CASE a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload,SUM(CASE a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing,SUM(CASE a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting,SUM(CASE a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as OthersFrom
task_table a,act_table b where a.status_id=b.act_id and
a.user_id=(select user_id from user_table where user_name='Raghu') and
a.task_date like '%/%/2006' GROUP BYLEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4)Output :Aug 2006 294 0 0 80 0 0 Jan 2006 14 0 0 0 0 0 Oct 2006 336 0 0 0 0 0 Sep 2006 3262 20 24 8 16 0 How to sort the date in ascending Order ?Jan 2006Aug 2006Sep 2006Oct 2006
View 2 Replies
View Related
Feb 27, 2008
I have: 4 tables and 1 table variable.
CCenters (ID, Name)
Campaigns (ID, Name)
Rel (ID, CCenterID, CampaignID) - [many to many]
and @SCampaigns (ID, CampaignID) - represents the selected campaigns by the user
performing the commands below I would get the centers associated with the campaigns selected.SELECT CCenterID
FROM Rel
INNER JOIN @Campaigns ON @SCampaigns.CampaignID = Rel.CampaignID
But what I really want are the common centers to the selected campaigns.
Thanks
View 1 Replies
View Related
Mar 18, 2004
I am trying to select a record from a table where it has the smallest priority
how would you go about doing this
is there a cool sort command or is there a select command syntax that can do this
thanks
View 3 Replies
View Related
Aug 31, 2005
I've made this example and it loads a picture into a database. (MsSql )Take a look at the code, it works just fine however it leaves a process in sleeping mode "avaiting command" in Enterprise manager under "Management/current Activity/Process Info"Is it supposed to be like this or is it supposed to be reemoved after .net is finished??Code snip_______________________________________________________
Dim conn As New SqlConnection("Data Source = (local);Initial Catalog = " & "test;User ID = NAME; Password=PASSWORD;")
Dim cmd As New SqlCommand("Select * from tab_bild", cnn)
Try
conn.Open()
Dim myDatareader As SqlDataReader
myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Do While (myDatareader.Read())
Response.ContentType = myDatareader.Item("PersonImageType")
Response.BinaryWrite(myDatareader.Item("PersonImage"))
Loop
conn.Close()
Response.Write("Picture info succesfully retrieved")
Catch SQLexc As SqlException
Response.Write("Read failed, Reason: " & SQLexc.ToString())
End Try
End Sub________________________________________________________________Please can someone explain this for me or sort this out for me.All help is welcome even if its only points me too a direction.RegardsTombola
View 3 Replies
View Related
Mar 29, 2001
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
View 2 Replies
View Related
Mar 29, 2001
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
View 1 Replies
View Related
Jul 20, 2000
Hi,
I am trying to restore .DAT file from dump. Its giving me error ..saying that the sort order id used for dumping was 42 not the default value 52.
How can i change the sort order to 42.
I am using sql server 6.5
Thanks
Srinivas
View 4 Replies
View Related
Jan 13, 2000
We have a vendor who insists that sql server 7 be set to a binary sort order. Is there any real advantage to this as opposed to a dictionary sort?
View 2 Replies
View Related
Sep 21, 2000
I have a table that most of the data has the same value, but there are only a few that do not match that value. I want to populate a listbox with all values from the table, but I'd like to have the majority listed first, followed by the others (the few that don't matach). What's the best way to approach this with SQL?
Thanks.
View 3 Replies
View Related
Sep 20, 2000
HOW CAN I CHECK THE SORT ORDER OF MY OLD SERVER?
HELP
View 1 Replies
View Related
Oct 5, 1999
Is there a query you can run against a 7.0 server to return the chracter set and sort order.
View 1 Replies
View Related
Oct 18, 1999
I'm trying to setup a duplicate of an old SQL Server 4.2 server to put in place while we upgrade the server, but I can't get the sort-order right. I know the existing server uses sort order id 40, but I can't find which sort-order that corresponds to during the install process. If anyone can give me a system table that lists all the sort orders names and id's, or can tell me what the text name for sort order 40 is, I would be very grateful.
Thanks,
Rob.
View 2 Replies
View Related
Jul 13, 2004
Hi All,
I have a table with 3 columns. Product, Location and Value. The data looks like this:
NULL NULL 100
Atlanta NULL 50
Atlanta Cookie1 30
Atlanta Cookie2 20
Dallas NULL 120
Dallas Cookie1 80
Dallas Cookie2 40
This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:
Atlanta Cookie1 30
Atlanta Cookie2 20
Atlanta NULL 50
Dallas Cookie1 80
Dallas Cookie2 40
Dallas NULL 120
NULL NULL 100
Thanks,
Shab
View 4 Replies
View Related
Jul 17, 1998
I need to copy the structure and data of an existing SQL 6.5 server to one with a different sort order. Normally, I would use the transfer tool to accomplish this, but the servers are on different networks. My question is, is BCP the answer? In other words, will the data copied via BCP from the sending server be able to be copied on the recieiving server. Also, is there a way to automatically generate the BCP statements for all tables? What I would really like is to be able to get at the scripts and data files created by the transfer tool.
View 1 Replies
View Related
