Modifying User Security
I have an asp page that currently is creating a database and a user
login for that database. After everything successfully (I thought)
executed, I tried to change my connection properties for the server
and then login as this new user. It wouldn't allow me to, so I logged
back in as the administrator and looked at the properties for the new
login. On the general tab, it had the user's default database
specified as the new database that I had created in the asp page, but
when I went to the database access tab, the database was not selected.
So, I'm not sure how to set that in my script. I've done some
searching in BOL, but I can't figure it out. Also, if there's a way
to do this in a query, or stored procedure, will it also specify what
type of role the user has (public, db_owner, etc.)? Thanks.
View Complete Forum Thread with Replies
Related Forum Messages:
Name Of Modifying User In Trigger ?
I have a trigger that emails me each time an order is modified by a manager. I can't recall how to find out which user is performing the modification. Is there a global variable such as @@username? Thanks, -Darin.
View Replies !
SQL Server 2005: User Security Management: User Ch
How do you handle user level security with SQL Server 2005? Say I have an HR database. In Active Directory I have two groups: Managers, Employees. Now in this HR Database I want to setup permissions in such a way that Managers can see all employees under them (but not other managers) and the employees can only see themselves. (I'd have various levels of management defined in a table somewhere, so that each employee has a manager ID that links to another employee so that the CEO would be manager of everyone by working down the chain). What I'm trying to understand is the best way to handle the permissions. I'm not entirely clear on how to deal with that. Would I use user chaining to do that, I wouldn't need impersonation (that's just for instances where you want dynamic SQL and it won't execute with user chaining, correct?) Anyway, just looking for some general direction on this (obviously I need to get a good book it would seem). Would I create a stored procedure that runs with EXECUTE AS permissions so that I'd have a non-interactive login it uses that has table access then all the other users have permission to execute the sproc? So that sproc runs, pulls back a SELECT * FROM tbl_HRINFO and using a WHERE constraint limits who is returned WHERE SupervisorID = CurrentLoggedInEmployeeID ? Also: How can I determine who is logged in and running the procedure, would the sproc use the SELECT USER_NAME command to see who was running it? As you can see, I'm working from square one on all of this. Not sure if my posting entirely made sense, but hopefully someone can get me pointed in the right direction, thanks!
View Replies !
User Security
Hi all, I want to create a user with the following criteria: The user can able to "CREATE","ALTER" the stored proceudres but not "DROP" them. So for this, I did like this: --Schema Level grant control on SCHEMA::dbo TO username --Procudure level GRANT CREATE PROCEDURE TO username --Denying on schema level Deny alter on schema::dbo TO username --------- But, if i do this, then the user cannot able to create anything in the schema. Can anyone give the workaround so that the user can able to "Create" as well "Alter" the stored procedure but not able to "Drop" the stored procedure. Thanks.
View Replies !
User Security For Internet
Hello, Ok I am a newbie to sql 2000. For my company I am developing an internet site which uses sql as the database backend. In the past (for much smaller projects) I just used MS ACCESS. For this project, I have already designed the site with appropriate tables (so I hope). and it all works dandy. My problem is this. I have created a table called exUSER (this table contains the info of the users who subcribe to the site) I do not know if this is a good thing or a bad thing, since SQL has its own users. If this is fine, then how do I apply security to this table so the passwords and usernames are secure? As I said above, I am VERY new to SQL (and for that matter relatively new to all databases including MS ACCESS, which I used to develop for) so I would appreciate any help at all. What would be immensly helpful also, is if someone has links to any sites which have sql 2000 examples for use on websites, and what are the best books with examples for sql? thanks in advance
View Replies !
DTS And NT Integrated Security User?
We have a a DTS package set up to run against another SQL Server. Using an integrated login is there a way to map an NT Authenticated users is the sql server login id mapping to this attached server. The DB we are going against only uses NT authentication to attach to.
View Replies !
User Security Audit
What is the easiest way to find out what objects a security login has mapped to it? Something that would show all the explicit grants a specific user has.
View Replies !
User Dependent Security
I was just analysing the security which can be given to different users to access respective databases. So i tried, on my local server, to deny permission to myself to access Model database. After this i am not able to connect to my local server at all. Error: Permission denied. I am using windows authentication mode. I have also deleted the local server registration, and re-registered it, but still the condition is same. Do i need to uninstall SQL Server completly to get rid of this prob.? I also registered a new data server, and there every thing is going fine. So now what do i do to get connected to my local server.
View Replies !
Help With The Security/Login/User Area Of Operations
Currently studying for 70-229.I'm trying to understand how security for users is managed in SQLServer. I've been using SQL Server for a few years now, but withoutinvestigating the bits that "just work".So, here's the scenario. This is more or less how I create all myapplications (which these days are all ASP.NET).I have a database called "TESTDB" (original, huh?)Now, I want to create a method for users to access this database, so Iopen EM, locate and expand the node for the server containing thedatabase, and open the "Security" node. Click on the "Logins" leaf,and in the pane right-click and select "New Login.."On the "General" tab I enter "TESTDBLOGIN" as the Name.Select "SQL Server Authentication", and put in a password.In the drop-down list of databases select "TESTDBLOGIN"Ignoring the "Server Roles" tab, I go to the "Database Access" tab andscroll down until I can see the "TESTDB" database. Check the "Permit"checkbox, and lo!, the "Database Roles for TESTDB" list is populated,with "public" already ticked. I check the "db_owner" box, and press"OK". It asks me to confirm the password, and once that's done there'sa new login called TESTDBLOGIN. What's more in "Users" leaf in the"TESTDB" database node there's a new user called "TESTDBLOGIN".All well and good. I can now create a connection using this login anddo more or less what I want in the TESTDB database.But.If I click the "Users" leaf in the "TESTDB" node, there are two users -"dbo" and "TESTDBLOGIN". If I right-click the "TESTDBLOGIN" user, Ican see that it has Database Role Memberships for the "public" and the"db_owner" roles. But if I examine the permissions on these two roles(by selecting the row, and then pressing the "Permissions" button) Ifind that there are permissions set for EITHER role - all the checkboxes are blank!So, how is it that I can do SELECT, UPDATE, INSERT and DELETEoperations via this login/user?Sorry it all took so long - I just wanted to get it right.ThanksEdward--The reading group's reading group:http://www.bookgroup.org.uk
View Replies !
Security Issue : How To Hide SDF File From The End-user ?
Hi There, Do we have any way of hiding SDF file from end-user? When we are installing our application on user's machine, we have to keep SDF file somewhere but anyone who knows about SDF file can open that file in management studio and that exposes our database structure. Is there a workaround for this ? How can we prevent visibility of SDF file ? Please help. Thanks in Advance. Rumba.
View Replies !
Auditing User And Security Related Activities In SQLServer
On the other database types, there is an audit capability in that yourecord such items asfailed login attemptsattemtped access to tables user is not authroized tochanges to databse schemachanges to permissionschanges to logins (add, delete, lock, unlock, passwrod reset)All I can find in the SQLServer documentation is the reference totracking failed logins when you set up a database, plus the Profiler'sactivities.Yes, I'm taking voer my first SQLServer database and have been asked tomake sure that this database is closely monitored for inappripriateactivity.Questions:1) Does SQLServer have this capability? (Sybase has this, which iswhere I'm coming from)2) Does SQLServer do this automatically or do I have to set up theevents to be tracked as happens with Sybase?3) What commands are there for setting up these events to be tracked?Thanks in advance!
View Replies !
Security For Row Level But Not Based On Database User's Login
HiI need to set security for row level but not based on Database user'slogin. It should be based on the user table login. For the particularuser I need to allow only the particular records to access insert,update delete and select.Let me explain clearlyFor example think we are using asp/asp.net websiteEg:www.test.comSo take this is our website and if you try this URL then you will get awindow for Login name and password.For example the Login name is windows user name (Here windows usermeans server windows user and not client) and windows password. So ifyou have login user id you can able to login in our site and we haveanother check. We have our own usertable this table consist all theuser login names and user rights. We will check the windows user whologin in our site has rights in the usertable I mean he is present inthe usertable if he is not present then we will display a message youhave no rights to access this site.If he has login id in our usertable then he allowed viewing ourpages. Still if he has the login id we will check the user who loginhas how much right to access to each page and the records of each tableits all depend on the user rights.So, here I need the row level security. For each and every table weneed to check the corresponding user and executing the record producelot of business logic problem for us.So after the user login we need automatically to set row levelsecurity for all the tables. Based on the user who login.So from there if we try select * from <tablename> then we can only ableto get the allowed records to select, insert, update, delete.Please can some one help how to solve this?Note:For some help you can refer the below URL (See in that they only givenabout the row level and column level security for each database usersnot for our required concept)http://www.microsoft.com/technet/pr...5/multisec.mspxThanks in advanceRams
View Replies !
Integrated Security: Login Failed For User '[null]'
I'm having trouble with some of my client connections where I'm getting the following error message: Login failed for user '[null]'. Reason: Not associated with a trusted SQL Server connection. This particular occurrence was with an attempt to us MS Query. I'm running SQL 7.0 on NT 4.0 Server with Integrated Security. The client is running NT 4.0 Workstation and has been given an appropriate login and access to the database and is trying to connect using the SQL ODBC Driver in the MDAC 2.1 Data Access Pack. I can't figure out why it seems to be passing a 'null' login when we are using integrated security. Any similar experiences and/or resolutions? Thanks, Eric
View Replies !
Share A RSS File For Applying AD-USER To Folder SECURITY?
Has anyone written a RSS file that is used to add new Active Directory User/Group to SSRS, and applying security ROLES such as BROWSER? I have new IT AD group that I need to apply to ALL folders on my PROD, QA and DEV servers. Way too many folders to do via SSMS or Web interface. Would you be willing to share the RSS file?
View Replies !
Login Failed For The User (using Windows Integrated Security)
Hello Everyone, I am trying to connect SQL database through code but I get this error,I have spent days to solve it but unfortunately I couldn't...Here is my code in VB express(OP:windows 2000 with SP4)I am new to VB,so it makes the situation more difficult for me. my code is: Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim conn1 As SqlConnection = _ New SqlConnection("Data Source=.SQLEXPRESS;" & _ "Integrated Security=SSPI;" & _ "database=deneme3_database;") 'Open connection conn1.Open() MessageBox.Show("Connection succeeded.") 'Close connection conn1.Close() MessageBox.Show("Connection closed.") End Sub The error message is,it is in german but it is clear I guess:) " Die von der Anmeldung angeforderte "deneme3_database"-Datenbank kann nicht geöffnet werden. Fehler bei der Anmeldung. Fehler bei der Anmeldung für den Benutzer 'ADPLANatalay'. " Thank you in advance! Can
View Replies !
How To Setup User Security For Reporting Services Used Inside ASP.NET Web Applications?
I'm working on a project that requires integration of SQL Server Reporting Services with ASP.NET 3.0 Web Applications. I'm working on Visual Studio 2005, with SQL Server 2005 on an XP development workstation. SQL Server, Reporting Services, and IIS are all running on my local machine. I'm trying to prototype two approaches, one using the Report Viewer control, and the second using the Reporting Services Web Service. I have the two examples setup in projects in Visual Studio. The sample reports and data sources work fine in Visual Studio. I can access the report using the Reporting Services URL like this: http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fBTT_BDS_DEV%2fCustomers; Report works fine. My problem is, that when I try and access the report using the Report Viewer inside an ASP.NET page or from the Web Serivce hooked up inside an ASP.NET Page I get a security errors. I have chosen Windows Security for the Datasource, and ASP.NET pages. In the case of the Web Service, I'm passing in my local domain user name as the credentials. I'm prototyping this on my local workstation, but I need to design this to be used on our corporate Intranet using Windows Security. My questions are: 1. How do I need to setup users on my local development workstation to get this to work. 2. How should I plan for user security for enterprise deployment, i.e. using Reporting Services inside a large ASP.NET Web Application? 3. Can anybody give me some links to some good developer type working examples of doing this. I've looked but have not found the answers to the "how do I setup users" part of the question specifcally related to ASP.NET apps? Below is the code example of the Web Services example app I'm working with which came out of a book I have on Reporting Services. This example compiles and seems like it would work but doesn't. Also following are a few of the error messages I get when experimenting with the example apps: Errors: 1. The permissions granted to user 'LocalMachineNameASPNET' are insufficient for performing this operation. (rsAccessDenied) 2. System.Web.Services.Protocols.SoapException was unhandled by user code Message="System.Web.Services.Protocols.SoapException: The permissions granted to user 'LocalMachineName\ASPNET' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'WCRBUSCNC2830B\ASPNET' are insufficient for performing this operation. at Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionFromCatalog(CatalogItemContext reportContext, String historyID, Boolean forRendering, Guid& reportID, Int32& executionOption, String& savedParametersXml, ReportSnapshot& compiledDefinition, ReportSnapshot& snapshotData, Guid& linkID, DateTime& historyOrSnapshotDate, Byte[]& secDesc) at Microsoft.ReportingServices.Library.GetDataForExecutionAction._GetDataForExecution(CatalogItemContext reportContext, ClientRequest session, String historyID, DataSourcePromptCollection& prompts, ExecutionSettingEnum& execSetting, DateTime& snapshotExecutionDate, ReportSnapshot& snapshotData, Int32& pageCount, Boolean& hasDocMap, PageSettings& reportPageSettings) at Microsoft.ReportingServices.Library.GetDataForExecutionAction.ExecuteStep(CatalogItemContext reportContext, ClientRequest session, DataSourcePromptCollection& prompts, ExecutionSettingEnum& execSetting, DateTime& executionDateTime, ReportSnapshot& snapshotData, Int32& pageCount, Boolean& hasDocMap, PageSettings& reportPageSettings) at Microsoft.ReportingServices.Library.CreateNewSessionAction.Save() at Microsoft.ReportingServices.WebServer.ReportExecution2005Impl.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo) --- End of inner exception stack trace --- at Microsoft.ReportingServices.WebServer.ReportExecution2005Impl.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo) at Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo)" Source="System.Web.Services" Actor="http://localhost/ReportServer/ReportExecution2005.asmx" Lang="" Node="http://localhost/ReportServer/ReportExecution2005.asmx" Role="" StackTrace: at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at WebReportTester.localhost.ReportExecutionService.LoadReport(String Report, String HistoryID) in C:Documents and SettingsconbcxMy DocumentsVisual Studio 2005ProjectsBTT_BDS_DEVWebReportTesterWeb ReferenceslocalhostReference.cs:line 242 at WebReportTester._Default.btnExecuteReport_Click(Object sender, EventArgs e) in C:Documents and SettingsconbcxMy DocumentsVisual Studio 2005ProjectsBTT_BDS_DEVWebReportTesterDefault.aspx.cs:line 82 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Web Service Code Example: protected void btnExecuteReport_Click(object sender, EventArgs e) { byte[] report; //Create an instance of the Reporting Services Web Reference localhost.ReportExecutionService rsv = new localhost.ReportExecutionService(); //Create the credentials that will be used when accessing Reporting Services //This must be a logon that has rights to the Customers Report rsv.Credentials = System.Net.CredentialCache.DefaultCredentials; rsv.PreAuthenticate = true; //The Reporting Services virtual path to the report. string reportPath = @"/ReportFolder/Customers"; //The rendering format for the report string reportFormat = "HTML4.0"; //The devInfo string tells the report viewer how to display with the report StringBuilder deviceInfoBuilder = new StringBuilder(); deviceInfoBuilder.Append(@"<DeviceInfo>"); deviceInfoBuilder.Append(@"<Toolbar>"); deviceInfoBuilder.Append(@"False"); deviceInfoBuilder.Append(@"</Toolbar>"); deviceInfoBuilder.Append(@"<Parameters>"); deviceInfoBuilder.Append(@"False"); deviceInfoBuilder.Append(@"</Parameters>"); deviceInfoBuilder.Append(@"<DocMap>"); deviceInfoBuilder.Append(@"True"); deviceInfoBuilder.Append(@"</DocMap>"); deviceInfoBuilder.Append(@"<Zoom>"); deviceInfoBuilder.Append(@"100"); deviceInfoBuilder.Append(@"</Zoom>"); deviceInfoBuilder.Append(@"</DeviceInfo>"); string deviceInfo = deviceInfoBuilder.ToString(); //Create an array of the values for the report parameters localhost.ParameterValue[] parameters = new localhost.ParameterValue[1]; localhost.ParameterValue parameterValue = new localhost.ParameterValue(); parameterValue.Name = "@WTRKCustomerCode"; parameterValue.Value = "B34186"; parameters[0] = parameterValue; //Create variables for the remainder of the parameters string historyId = string.Empty; string credentials = string.Empty; string showHideToggle = string.Empty; string extension = string.Empty; string mimeType = string.Empty; string encoding = string.Empty; localhost.Warning[] warnings; localhost.ParameterValue[] reportHistoryParameters; string[] streamIds; localhost.ExecutionInfo execInfo = new WebReportTester.localhost.ExecutionInfo(); localhost.ExecutionHeader execHeader = new WebReportTester.localhost.ExecutionHeader(); rsv.ExecutionHeaderValue = execHeader; execInfo = rsv.LoadReport(reportPath, null); rsv.SetExecutionParameters(parameters, "en-us"); try { //Execute the Report report = rsv.Render(reportFormat, deviceInfo, out extension, out mimeType, out encoding, out warnings, out streamIds); //Flush the pending response Response.Clear(); //Set the HTTP Headers for a PDF response. HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ContentType = "text/html"; //Filename is the default filename displayed //if the user does a save as. HttpContext.Current.Response.AppendHeader("Content-Disposition", "Customers.htm"); //Send the byte array containing the report as a binary response. HttpContext.Current.Response.BinaryWrite(report); HttpContext.Current.Response.End(); } catch (Exception ex) { if(ex.Message != "Thread was being aborted.") { HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ContentType = "text/html"; StringBuilder errorMessageBuilder = new StringBuilder(); errorMessageBuilder.Append(@"<HTML>"); errorMessageBuilder.Append(@"<BODY>"); errorMessageBuilder.Append(@"<H1>"); errorMessageBuilder.Append(@"Error"); errorMessageBuilder.Append(@"</H1>"); errorMessageBuilder.Append(@"<BR>"); errorMessageBuilder.Append(@"<BR>"); errorMessageBuilder.Append(ex.Message); errorMessageBuilder.Append(@"</BODY>"); errorMessageBuilder.Append(@"</HTML>"); string errorMessage = errorMessageBuilder.ToString(); HttpContext.Current.Response.Write(@errorMessage); HttpContext.Current.Response.End(); } } } Any direction here related to best practices on setting up users for code use with ASP.NET applications would be greatly appreciated... Thanks in advance...
View Replies !
Setup Issue For Security On Reporting Service Unknown User Or Password Error
I need security setup help as after I deploy the reports I get login failed error trying to run them. Invalid User name or bad password. I have it set up as a shared data source with credential supplied by the user running the report. This is to be setup for some users to access this to print the reports by logging into this server. I need to complete this as I am out of the office next week. w3wp!library!5!04/30/2008-08:43:37:: i INFO: Call to GetPermissions:/ w3wp!library!5!04/30/2008-08:43:37:: i INFO: Catalog SQL Server Edition = Standard w3wp!library!8!04/30/2008-08:43:38:: i INFO: Call to GetSystemPermissions w3wp!library!1!04/30/2008-08:43:43:: i INFO: Call to GetPermissions:/CBIZ Reports w3wp!library!1!04/30/2008-08:43:43:: i INFO: Call to GetSystemPermissions w3wp!library!1!04/30/2008-08:43:44:: i INFO: Call to GetPermissions:/CBIZ Reports/rptRequirements Summary by Type Sales w3wp!library!1!04/30/2008-08:43:45:: i INFO: Call to GetSystemPermissions w3wp!library!5!04/30/2008-08:43:48:: i INFO: Call to RenderFirst( '/CBIZ Reports/rptRequirements Summary by Type Sales' ) w3wp!library!5!04/30/2008-08:43:48:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon failed., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon failed. ---> System.Runtime.InteropServices.COMException (0x8007052E): Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E) at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo) at RSRemoteRpcClient.RemoteLogon.GetRemoteImpToken(String pRPCEndpointName, Int32 type, Guid dataSourceId, String pUserName, String pDomain, String pPassword) at Microsoft.ReportingServices.Diagnostics.ImpersonationContext.Login(CredentialsType credType, Guid dataSourceId, String userName, String userPwd, String domain) --- End of inner exception stack trace --- w3wp!webserver!5!04/30/2008-08:43:53:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: Logon failed. ---> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon failed. ---> System.Runtime.InteropServices.COMException (0x8007052E): Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E) at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo) at RSRemoteRpcClient.RemoteLogon.GetRemoteImpToken(String pRPCEndpointName, Int32 type, Guid dataSourceId, String pUserName, String pDomain, String pPassword) at Microsoft.ReportingServices.Diagnostics.ImpersonationContext.Login(CredentialsType credType, Guid dataSourceId, String userName, String userPwd, String domain) --- End of inner exception stack trace --- at Microsoft.ReportingServices.Diagnostics.ImpersonationContext.Login(CredentialsType credType, Guid dataSourceId, String userName, String userPwd, String domain) at Microsoft.ReportingServices.Diagnostics.ImpersonationContext..ctor(CredentialsType credType, Guid dataSourceId, String userName, String password, String domain) at Microsoft.ReportingServices.Library.SurrogateContextFactory.CreateContext(ExecutionType& execType) at Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext reportContext, ItemProperties properties, ParameterInfoCollection effectiveParameters, Guid reportId, ClientRequest session, String description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection thisReportDataSources, Boolean cachingRequested, Warning[]& warnings, ReportSnapshot& resultSnapshotData, DateTime& executionDateTime, RuntimeDataSourceInfoCollection& alldataSources, UserProfileState& usedUserProfile) at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters) at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute() at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() --- End of inner exception stack trace --- at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobType type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory streamFactory) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.DoStreamedOperation(StreamedOperation operation) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType itemType) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent() at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage() Thanks! Terry Beckman
View Replies !
User&&" Is Not Able To Access The Database &&"model&&" Under The Current Security Context
I have a restriced user on SQL Server that is only permitted to creat a new database and manage it only. All other database are hidden to that user. when that user login to SQL Server and create a database and try to change the default folder path for data and log files, gets an error, The Server principal "User" is not able to access the database "model" under the current security context. (MSSQL Server, Error: 916) Any idea??? Thanks,
View Replies !
Modifying XML In SQL
Greetings DECLARE @Xml AS XML Given the above variable I get using a SELECT @Xml ... FOR XML, AUTO, is it possible to change the value of one of the elements in the XML file in SQL 2005? Thanks
View Replies !
Modifying A Field
Hello, I need to modify a field in my table to add increment, the field already has data(1,2,3....). The thing is I am working with sqlserver 6.5(heard it all). How would I do that using the tool? Using SQL? Thanks in advance.
View Replies !
Modifying Data
I am trying to modify data in a tble using the Stored Procedure below. It doesnt work properly. I seem to only be getting one character. I think it has something to do with me using "nchar" with the variables. The value I am changing is actually a string. Alter Procedure usp_UpdateJobName @JobNameOld nchar, @JobNameNew nchar As UPDATE JobName SET JobName=@JobNameNew FROM tblRMADATA WHERE tblRMADATA.JobName=@JobNameOld
View Replies !
Modifying The DTS Package.
I have a DTS package package1 which imports an excel sheet into my DataBase.ANd its saved and scheduled and running fine.Actually the excel sheet will be located daily @ c:ruceexceldata.But now we wanted to change the location of the file to c:ruce1ewexceldata So how and where can I change my package. Thanks.
View Replies !
Modifying The DTS Package.
I have a scheduled DTS package which gets the data from a text file and imports into a sql table.The table will be droped and created whenever the DTS package runs.previusly the table has 6 rows and now i wanted to add a column row_id which is having an idenity seed.So how can I modify my package so that I can get done with my requirements.
View Replies !
Modifying Job Notification
Is there a way to modify the information sent on a SQL Server job notification? The information sent now is rather lengthy and exceeds my pager capacity. As a result, the job status (Succeeded, Failed), which is the most important information does not appear. The workaround I am using now is that I have a job step which utilizes a xp_sendmail to notify of job completion.
View Replies !
Modifying A Column
I have an existing table called OrderHeader with a column called ID. The ID column is currently set as an INT. I want to run a script to modify that column to be an IDENTIY. I've tried the following: ALTER TABLE OrderHeader ALTER COLUMN [ID] INT IDENTITY but this does not work. Any idea how to accomplish this? Thanks! Mike
View Replies !
Creating And Modifying A Job
I really hope someone can help me with this: I've been struggling with it all day to no avail. We have a db user (batch: is only used in overnight processes) who has some rights, but not all, and as a result of this, the user cannot run the execute command. And now it needs to. So we had the bright idea of getting batch to create a job to do what it needed to. But, of course, batch is the owner of the job, so the same rights issue occurs. So I thought about creating job using sysadmin, then letting batch modify it as it needs to (changing the run date, etc), but it looks to me like a job created by sa is 'invisible' to batch. Any update attempt is met with "The specified @job_id does not exist". If I create the job with batch, no prob, but that's not going to work for me. Please, does anyone have any ideas here?
View Replies !
Help In Modifying Query
Greetings I have 3 tables. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType grouped by the CallDetailID and ordered by the date created SCRIPTS DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30), StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT) INSERT @Request SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL SELECT '2232362', 'Buck', 'SA055', 67, 195, Null DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT) INSERT @Call SELECT 111, 1, 12123 UNION ALL SELECT 112, 1, 12123 UNION ALL SELECT 114, 2, 12123 UNION ALL SELECT 123, 2, 12123 UNION ALL SELECT 134, 3, 12123 UNION ALL SELECT 143, 1, 6532 UNION ALL SELECT 145, 1, 6532 UNION ALL SELECT 154, 2, 6532 UNION ALL SELECT 185, 2, 6532 UNION ALL SELECT 195, 3, 6532 DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime) INSERT @CallDetail SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL SELECT 6532, 1, '11/5/2007 12:12:34 PM' -- --select * from @Request Query written to achieve the requirement UPDATE r SET r.UniqueNo = p.RecID FROM @Request AS r INNER JOIN ( SELECT r.RequestID, ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID FROM @Request AS r INNER JOIN @Call AS c ON c.CallID = r.CallID INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID ) AS p ON p.RequestID = r.RequestID WHERE r.UniqueNo IS NULL select * from @Request OUTPUT : 324234 Jack SA023 12 111 1 223452 Tom SA023 12 112 2 456456 Bobby SA024 12 114 1 22322362 Guck SA024 44 123 1 22654392 Luck SA023 12 134 1 225652 Jim SA055 67 143 1 126756 Jasm SA055 67 145 2 786234 Chuck SA055 67 154 1 66234 Mutuk SA059 72 185 1 2232362 Buck SA055 67 195 1 EXPECTED OUTPUT: (See the last column for unique nos). 324234 Jack SA023 12 111 1 223452 Tom SA023 12 112 1 456456 Bobby SA024 12 114 1 22322362 Guck SA024 44 123 1 22654392 Luck SA023 12 134 1 225652 Jim SA055 67 143 2 126756 Jasm SA055 67 145 2 786234 Chuck SA055 67 154 2 66234 Mutuk SA059 72 185 2 2232362 Buck SA055 67 195 2 How can I modify the query to achieve the expected output? Thanks:
View Replies !
Modifying Report
Hi our reports server has some reports on it. we need to modify some of them. how can i get those reports on to "SQL BUSSINESS INTELLIGECSE DEVELOPMENT STUDIO". can we get rdl file from reporting server ?
View Replies !
Help With Modifying Query
Hi, I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it. What I want to add is the following data. For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members. Any assistance is much appreciated!! thanks once again mike123 SELECT CONVERT(varchar(10),referDate,112) AS referDate, SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0', SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1', SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2', SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3', SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3', SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent', count(*) as totalRefers, count(distinct(referUserID)) as totalUsers, SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail', SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk', SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca', SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk', SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail', SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol', SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo', SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual', SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto' FROM tblreferemails R WHERE DateDiff(dd, referDate, GetDate()) < 5 GROUP BY CONVERT(varchar(10),referDate,112) ORDER BY referDate DESC CREATE TABLE [dbo].[tblUserDetails] ( [UserID] [int] IDENTITY(1,1) NOT NULL, [NameOnline] [varchar](15) NULL, [EmailAddress] [varchar](50) NULL, [Date] [datetime] NULL, [Active] [tinyint] NULL ) CREATE TABLE [dbo].[tblReferEmails]( [emailID] [int] IDENTITY(1,1) NOT NULL, [referUserID] [int] NOT NULL, [destinationName] [varchar](50) NULL, [emailaddress] [varchar](50) NOT NULL, [referDate] [datetime] NOT NULL, [referalTypeID] [int] NULL, [deleted] [int] NULL, [emailSendCount] [int] NULL, [lastDateSent] [smalldatetime] NULL ) ON [PRIMARY] GO
View Replies !
SSRS -- Security Filter And Model Item Security Setting
Hi, I have posted this issue for a week, haven't got any reply yet, I posted it again and desperately need your help. The article http://msdn2.microsoft.com/en-us/library/ms365343.aspx says: Model Item Security can be set for differnt security filters, but when I use SQL Server Management Studio to set Model Item Security, it seems "Permissions" property surpass "Model Item Security" property. -- My report server is using Custom Authentication. For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder; in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder. My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID? The article also says: "Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows." So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID. I can only get one result at a time but not both: either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time. Your help is highly appreciated! Desperate developer
View Replies !
Needs Help With Modifying Stored Procedure
I need help with modifying this procedure to join JobTypeGallery, Remodel on JobTypeGallery.TypeID and Remodel.TypeID. I would like for it the procedure to not allow deleting a record from JobTypeGallery if there are any records in Remodel Table that is associated with JobTypeGallery. Can someone please help me modify this stored procedure? Create PROCEDURE [dbo].[spDeleteJobTypeGallery] @typeid int AS delete from jobTypeGallery where typeID = @typeid GO
View Replies !
Modifying A Lot Of Stored Procedures
I probably know that I don't want to do this, but I have an odd case. I have a database with a bunch of stored procedures and functions. 128 of them reference a different database than the one they're in. For testing puposes, I need to temporarily re-point these functions and procedures to a different database. I suspect the answer will be a resounding 'No', but I was wondering if it was possible to somehow run a query against syscomments where I could update all of those objects with the temporary database name rather than edit each and every one of them. Conceptually, it would just be an UPDATE using REPLACE. Pretty comfortable with that, but I'm always very reluctant to mess with stuff like that. I'll probably still just do it through QA, but I was wondering if it's possible to do something like that and thought it might be an interesting topic for discussion. Thanks, Pete
View Replies !
Problem Modifying Out-of-the-box TFS Report
My office has adopted Team Foundation Server to track and manage thedevelopment and deployment process at our site. I have been asked tomodify one of the standard work item reports, "Related Work Items,"that ships with TFS. I've successfully done some of these, whichinvolved changing the MDX code for the datasets that generate defaultparameter values, but I am running into problems modifying the maindataset, dsRelatedWorkItems. I am using VS2005. When I look at thecomponents of this dataset on the tree diagram for all datasets, allof the correct fields are present, I can Preview the report and itworks. When looking at the Data page, I see the MDX query on top anda results pane on the bottom. The results pane correctly lists all ofthe fields I saw in the tree diagram across the top. But the queryitself does not look like the query that would have generated thesecolumns. Rather, the columns in the result pane correspond to therows in the query, not the columns. When I execute the query on thedata page, all of those columns disappear in the result pane, and theonly column that shows up is the single field that precedes the ONCOLUMNS statement in the MDX. At this point, if I attempt to previewthe report, it fails because it doesn't have the expected fields inthe dataset. If I try to switch to the design mode to see whetherthere is any alternate definition of the dataset lurking somewhere, itlooks like all dataset definitions are lost. The properties page forthe dataset shows the fields correctly if I haven't yet executed thequery. If I try to make any of the modifications that I need to make(which are simple -- I just need to remove some of the slicerconditions in the WHERE clause), I am again unable to preview thereport.It looks like the query that I see in the query pane is not what isreally being used for the dataset, and I'm stumped. I am not anexperienced MDX user, but I can do fairly basic modifications of thetype that are needed here. It seems like my problem isn't MDX syntax,but rather how (or where) Reporting Services is keeping the query thatunderlies this dataset.Anybody out there have a clue that might get me back on track withthis? Thanks.
View Replies !
Deleting/modifying Of Job Fails
When I try to delete/modify a job on my sqlserver the following error occurs: error 14274: Cannot Add, update or delete a job that originated from a n MSX server. What does that mean and how can I solve this ?
View Replies !
Modifying Table Column
Can someone tell me the easiest/best way to make a modification to a table where you change a column constraint from not null to null? Would you rename the table, create the new one with the different column and then transfer the old data into the new definition? thanks
View Replies !
Modifying Table Size
Hi guys, I am trying to modify my table sizes. I need to extend the size of certain columns in several tables and keep the data presently in those tables also. On my first attempt I successfully made the changes but had no data after dropping the original tables. Then I had to restore from a back up and I was back to where I started. Does anyone know a reliable way of doing this with out having to create another DB and transferring the data after making the size changes to my tables ? Thanks, Alan
View Replies !
Modifying SYSJOBSTEPS Table
I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts? Thanks!
View Replies !
Using Update For Modifying Text
Hello All, I would like to update some values in my table that have this value: XXXX-XXXX. I would like to update the '-' and change it to a '_'. Does anyone know the best way to do this? I tried this but I get a "Subquery returned more than 1 value error" UPDATE PS_MTCH_RULES SET MATCH_RULE_ID = (SELECT SUBSTRING (MATCH_RULE_ID,1,4)+'_'+SUBSTRING(MATCH_RULE_ID,6, 4) FROM PS_MTCH_RULES WHERE MATCH_RULES_ID <> 'ERS') Any assistance would be appreciated. Thank you. E David Ramos
View Replies !
Modifying System Table
Quick Question: I want to change an execution time for 345 jobs and do not want to do it one by one in EM. I have changed the active_start_time and next_run_time in the sysschedules table but it is not showing up within the EM job folder under the next run date as being modified. Does anyone know what I am missing? TIA, Daimon
View Replies !
|
TRACKER
