Hello,
I want to see the permissions assigned for all objects (tables, views, stored procedures) in all my databases. Has anyone written a script to do this? Just thought I'd ask before reinventing the wheel. I'm on SQL Server 7.0.
Thanks,
Dan
For the life of me I cannot figure out why i'm getting the "Object Refence Not Set To an Instance of An Object" error after I try and create a Report Services project in Business Intelligence Studio. I've followed all of the steps in similar posts such as checking the ReportServer/web.config file but everything checks out.
Does anyone have any other ideas as to how I can get rid of this annoying error and start creating some reports?
Running on a SQL 6.5 server, I want to know how can I get a report from Options,Permissions that will list me By User the Views,Tables,Store Procedures and Privileges to be able to import this information into a Excel spreadsheet.
I just finished doing a search of the archives and found two references in April 98 to what appears to a similiar proglem to what I have. I found no comments after that.
I have recently converted my 4.2x databases to 6.5. Since then, I have been unable to revoke object permissions completely for my users. My front end gives user permissions on tables and views to the users but I only want them to have group permissions. When I use EM or DBArtisan or ISQL to revoke all the user`s permissions, the response is that they are successful but the permission still display and are shown in sp_helprotect. Is this a known "undocumented feature" or a new twist on an old 6.5 problem?
I would like to be able to get a listing of the object permissions for a particular group in a database. I can't find a procedure for doing this. I know I must be overlooking an obvious solution. Does anyone have any suggestions for doing this? Thanks.
I created two differnt objects in a database. Let say user1 is the database object owner.
Now the probelm starts when I use the query analyzer for just selecting the data using sa account(sa is the admin to all db) and when happens is that I cant't read the data using SQL QUERY ANALYZER but if I go to the TBALES through explorer then I can open the table using the same sa account.
Hi all,My project modules in MS-Access rely heavily on DAO permissions. Now the access front end is being hooked up to a SQL Server 2005 back end, and I'd like to sync the permissions of linked table objects with the corresponding views when they are created/recreated.I'm vaguely aware there are a substantial number of differences between the the SQL Server and Jet security models. Initially, I'd like to create a lookup table to handle this. My problem is that I don't seem to be able to find much info on groups and object permissions in the SQL Server security model - perhaps I'm asking the wrong questions.:confused: Can anyone provide a link to a decent site for SQL Server novices?Ta
I'm looking into processes to move to using database specific roles and migrating specifically assigned user permissions to these roles. I have made progress on listing permissions in the public role and assigning these same permissions to the new role that I have created for the given database. However, when I inspect permissions on tables and other objects, permissions have also been specifically granted for application users in the db. My ojective is to script out the permissions against the database objects (mainly user tables) and then use the output to assign the same permissions to the appropriate role instead. However, I'm having difficulty finding which system table stores the table and column level permissions.
If I do select * from sysobjects, I can see the user tables. However, I can't find the related tables that store user object permissions. For example, sysprotects doesn't store this info and, if I do an inner join on 'id' between these two tables, I don't see any user tables. Where do I look? Are there any useful 'already done' scripts/procs around for these purposes?
I'm trying to identify the objects in master that the role public has select permissions on, but when I run this query, I get 4 results where the default schema is null and the major_id column does not correspond to any records in the sys.all_objects table. Where else can I look to find what objects these are. DBO is listed as the grantor.
I appreciate your help.
SELECT * FROM SYS.DATABASE_PERMISSIONS P, SYS.DATABASE_PRINCIPALS R WHERE P.GRANTEE_PRINCIPAL_ID=R.PRINCIPAL_ID and permission_name='SELECT' and class_desc='OBJECT_OR_COLUMN' and r.name='public' order by r.name desc
I have recently restored a backup of a SQL Server 2000 Database (from my production server €“ which is a shared hosting service) on my dev machine so I can do some testing. The issue I am having is with the permission of objects. Some of the objects were created with the dbo user and some with another login (myLogin). The ones that were created with dbo work fine on my dev machine. The others do not work unless I prefix them with €œmyLogin€?€¦ I get an €œInvalid object name€? if I don€™t prefix them. This is problematic because there are hundreds of stored procs and tables that I would need to modify in order to use them on my dev machine.
Can anyone tell me how to create a new user with the necessary permissions so I can execute my procs and access tables without having to prefix them?
Hope this makes sense €“ please let me know if it doesn€™t.
Using Management Studio how do you script only user and object permissions? I don't want to script the corresponding "Create" statements for each object, only their permissions.
Using Management Studio how do you script only user and object permissions? I don't want to script the corresponding "Create" statements for each object, only their permissions. This was possible in 2000.
I have been looking at forums trying to resolve my problem and now my brain hurts. I really need help with this and no one seems to have a definitive answer.
When launch Reporting Services, the page is blank and the settings options are missing. Even though I am logged on to the server with a local admin account.
I have tried taking off annonymous access but when I do this I just get the page can not be displayed.
Also when connecting to ReportingServices through the Management Studio I get the following message:
The permissions granted to user 'servernameusername' are insufficient for performing this operation. (rsAccessDenied) (Report Services SOAP Proxy Source)
The permissions granted to user 'Servernameusername' are insufficient for performing this operation. (rsAccessDenied) (Report Services SOAP Proxy Source)
Any help on this would be much appreciated. I really need to get this sorted out.
Hi. I created user: "Reporter" This User is set as my anonomoys login user in IIS 6.0. I gave him permissions on both the database server & the Report server, however I keep getting an error. "The permissions granted to user 'SRVRReporter' are insufficient for performing this operation. (rsAccessDenied).
I inherited the task of syncing up these new reports & I'm trying to follow what the last guy did (not exactly though, that's why I created the new user)
I gave the new user login rights on both servers as well as access to the SQL 2005 database. I have the identity impersonate="true"
Can anyone out there tell me what permission I need to grant to get my reports to run?
I have a server with a dozen or so databases created by a 3rd party for some software that we've purchased. Because of the mess involved I've created a separate database for reporting purposes contains a large number of objects that reference the other databases using 3-part naming.
Now I'm on to securing this stuff up.
- I don't want my users having any write permissions so have added them to the db_denydatawriter role.
- I don't want them having access to certain schemas within the reporting database so have granted select and execute permissions to the specific schemas.
I like simple.
Unfortunately because of the 3 part naming they require access to the other databases... but I would love to not have to do this as I want to force them in to using the objects I have created.
We have a reporting server which was working fine. All of a sudden me ( admin) and the users who have content manager permissions donot see the properties tab on the web interface and I donot find the permissions tab. I also get "you are not authorized to access this page" error on systemsecurity page.
If I disable the allow anonymous access option from the directory security in IIS, me and the person with content manager permissions see the properties/permissions tab but users are getting 401 unauthorized error while accessing the reports ( some of them are configured as content managers).
Not sure what happend and what to do .... any help would be greatly appreciated.
We have a SQL 2012 SSRS instance. We need to modify the permissions on one of the reporting folders. I went to the Security setting of the folder, clicked "New role assignment", then entered a group name into the box and selected desired permission ("Browser" in this case).
However this always fails with the same error: "The user or group name 'BUILTINBUILTIN' is not recognized. (rsUnknownUserName)"
I always get the same error no matter if I enter user or group, domain or local. And it always says BUILTINBUILTIN. I've tried changing the SSRS service account to a domain account (it was a local one), but that didn't work.
User 'DMNServerBreanch' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account.Control (UAC) restrictions have been addressed.
In order to enable the report cache and set data refresh options, in sharepoint library on report file,I am selecting "Manage processing options" as mentioned in MSDN
[URL] ....
But I am getting error "EXECUTE permission was denied on the object xp_sqlagent_enum_jobs" I have granted the execution rights for all procedures as mentioned here but still error isnot going
[URL] .....
Where can I find SSRS failure logs, I can see execution3 but it is not storing this information...
.NET Permissions Error in Reporting Services when not using a custom assembly: I need help resolving a permissions error I€™m taking in a SQL RS 2005 report. I have a report that that is includes the following code fragment in Report Properties -> Code: Function rtf2text(ByVal rtf As String) As String Dim rtfcontrol As New System.Windows.Forms.RichTextBox Try rtfcontrol.Rtf = rtf Return rtfcontrol.Text Catch ex as Exception Return ex.Message End Try End Function I reference the .NET System.Windows.Forms DLL under Report Properties -> References -> References, Assembly Name (heading): System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 I have a text box with the following expression: =code.rtf2text(First(Fields!EndingQuoteComment.Value, "QuoteHeader")) And I€™ve verified, by removing the code.rtf2text command that is populated with the following: { tf1ansiansicpg1252deff0deflang1033{fonttbl{f0fromanfprq2fcharset0 Times New Roman;}{f1fnilfcharset0 Arial;}} viewkind4uc1pardif0fs32 ** Ending Quote Comments **par 0i0f1fs17par } When I run the preview in Visual Studio is correctly strips the RTF and displays just €œ** Ending Quote Comments **€?. When I €˜RUN€™ locally or deploy to a SQL RS 2005 Server and run the report I take the following error: Request for the permission of type 'System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. I€™ve tried everything that I can think of on the server to make this work. I finally put together a Win 2003 box with SQL 2005, IIS, and RS 2005 running on it in a virtual machine to be 100% sure I had a standard clean install and deployed the report and I€™m getting the same error. Below I€™ve included a basic standalone RDL file that demonstrates my issue. I get the error referenced above when I deploy the RDL below. Any ideas or suggestions are greatly appreciated?
<?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <BottomMargin>0.25in</BottomMargin> <RightMargin>0.25in</RightMargin> <PageWidth>7.75in</PageWidth> <rdrawGrid>true</rdrawGrid> <InteractiveWidth>7.75in</InteractiveWidth> <rdnapToGrid>true</rdnapToGrid> <Body> <ReportItems> <Textbox Name="textbox21"> <Left>0.25in</Left> <Top>0.25in</Top> <rdefaultName>textbox21</rdefaultName> <Width>6.375in</Width> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <FontSize>7.5pt</FontSize> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Height>1.375in</Height> <Value>=code.rtf2text("{ tf1ansiansicpg1252deff0deflang1033{fonttbl{f0fromanfprq2fcharset0 Times New Roman;}{f1fnilfcharset0 Arial;}} viewkind4uc1pardif0fs32 ** Ending Quote Comments **par 0i0f1fs17par } ")</Value> </Textbox> </ReportItems> <Height>5.25in</Height> </Body> <rd:ReportID>8804486c-882f-493c-8dfb-b2f778a24b21</rd:ReportID> <LeftMargin>0.25in</LeftMargin> <CodeModules> <CodeModule>System.Windows.Forms, Version=2.0.50727.42, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule> </CodeModules> <Code>Function rtf2text(ByVal rtf As String) As String Dim rtfcontrol As New System.Windows.Forms.RichTextBox Try rtfcontrol.Rtf = rtf Return rtfcontrol.Text Catch ex as Exception Return ex.Message End Try End Function </Code> <Width>7.25in</Width> <InteractiveHeight>10in</InteractiveHeight> <Language>en-US</Language> <TopMargin>0.25in</TopMargin> <PageHeight>10in</PageHeight> </Report>
I have a On_hand_Quantity report which has a parameter drop down to select about 200 stores.
When i run this report in BI studio , selecting all the 200 stores for the parameter it works fine.
But when i upload the same report to report manager and try selecting all the store_numbers it errors out saying 'operation cannot run due to current state of the object ' This report was running fine since the past 2 years, on 30th dec we did some .NET updates (KB2656356 and KB2656351) after which this report stopped working.
I tried troubleshooting all ways but it errors out only in report manager.
Yesterday, literally I was banging my head to the wall, kept getting this error:
A Connection could not be established with the information provided. Hit OK to change your connection settings. Invalid object name 'sysdatabases'.
I uninstall and re-install back the reporting services couple times and I kept getting the same error. I have 2 instances of SQL Server on the same Server:
SQL Server 2000
SQL Server 2005 The 2 instances work side by side, I was wondering if this causing the error.
Hi There,This is related to a ms access database but since I use the SqlDataSource control I thought I should post here.I have a project that I was working on with this ms access db and using sql controls, everything was working just finesince one day I started getting "Object reference not set to an instance of an object" messages when I try to designa query or retrieve a schema, nothing works at design time anymore but at runtime everything is perfect, its a lotof work for me now to create columns,schemas and everything manually, I've tried reinstalling visualstudio, ado componentsbut nothing seems to fix it, did this ever happen to any of you guys?any tip is really appreciated thanks a lot
Does any one has any clue for this error ? I did went through a lot of articles on this error but none helped . I am working in Visual studie 2005 and trying to upload image in sql database through a simple form. Here is the code
using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Web.Configuration; using System.IO; public partial class Binary_frmUpload : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) { }protected void btnUpload_Click(object sender, EventArgs e) {if (FileUpload.HasFile == false) { // No file uploaded!lblUploadDetails.Text = "Please first select a file to upload..."; } else {string str1 = FileUpload.PostedFile.FileName; string str2 = FileUpload.PostedFile.ContentType; string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString; //Initialize SQL Server Connection SqlConnection con = new SqlConnection(connectionString); //Set insert query string qry = "insert into Officers (Picture,PictureType ,PicttureTitle) values(@ImageData, @PictureType, @PictureTitle)"; //Initialize SqlCommand object for insert. SqlCommand cmd = new SqlCommand(qry, con); //We are passing Original Image Path and Image byte data as sql parameters. cmd.Parameters.Add(new SqlParameter("@PictureTitle", str1)); cmd.Parameters.Add(new SqlParameter("@PictureType", str2));Stream imgStream = FileUpload.PostedFile.InputStream; int imgLen = FileUpload.PostedFile.ContentLength;byte[] ImageBytes = new byte[imgLen]; cmd.Parameters.Add(new SqlParameter("@ImageData", ImageBytes)); //Open connection and execute insert query. con.Open(); cmd.ExecuteNonQuery(); con.Close(); //Close form and return to list or images.
} } }
Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.Source Error:
Line 32: Line 33: string str2 = FileUpload.PostedFile.ContentType; Line 34: string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString; Line 35: Line 36: //Initialize SQL Server Connection Source File: c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs Line: 34 Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.] Binary_frmUpload.btnUpload_Click(Object sender, EventArgs e) in c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs:34 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Not sure if this is the right forum to post this question to, so if it's not, please accept my apologies.
I'm working in SQL Server 2005 with a database that was migrated to 2005 from SQL Server 2000. I have to alter a trigger on a table for some functionality changes, and when I modify the trigger and then access it through the application the database is working with, I receive this error:
There was a error in the [stored procedure name] procedure. Error Number: -2147217900 Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The definition of object '[trigger name]' has changed since it was compiled.
[stored procedure name] and [trigger name] are where the actual names appear in the message.
I've tried running sp_recompile on the trigger, stored procedure, and table that are associated with this, and nothing works. I have dropped the trigger, which allows the save process to complete (but doesn't perform the required functionality, of course), and then re-created the trigger, but the error message still comes up. The compatibility level for the database is SQL Server 2000 (80) (as it was migrated from SQL Server 2000 as I mentioned above).
Has anyone seen this, and if so, how can I fix it?
Help! I have posted this before and I had hoped that the VS2005 SP1 would help my problem. It didn't. My code is shown below. I have dropped a sqlconnection, sqldataadapter and a strongly-typed dataset from the toolbox onto the component designer for my page and written my code. It compiles without any errors but at runtine I receive the system error "Object reference not set to an instance of an object." The error occurs at the first line where the sqldataadapter is mentioned. I have shufflled the code and the error still occurs at first mention of the dataadapter. I have set parameters to a simple string such as "myemail." It hasn't helped. I have used the "Dim" statement as "Dim DaAuthorLogin as System.Data.SqlClient.SqlDataadapter and Dim DaAuthorLogin as New ......) at the start of the private sub generated by the event requiring the data. Nothing helps. Here is my simple code to select data from a sqlserver 2000 database. Why do I continue to get this error? Partial Class AuthorLogin Inherits System.Web.UI.Page Protected WithEvents AuthorInformation As System.Web.UI.Page #Region " Web Form Designer Generated Code " 'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand Me.DaAuthorLogin = New System.Data.SqlClient.SqlDataAdapter Me.MDData = New System.Data.SqlClient.SqlConnection Me.DsAuthorLogin = New MedicalDecisions.DsAuthorLogin CType(Me.DsAuthorLogin, System.ComponentModel.ISupportInitialize).BeginInit() ' 'SqlSelectCommand1 ' Me.SqlSelectCommand1.CommandText = "SELECT AuthorAlias, AuthorEmail, AuthorPassword, LastName, PreferredName" & Global.Microsoft.VisualBasic.ChrW(13) & Global.Microsoft.VisualBasic.ChrW(10) & "FRO" & _ "M T_Author" & Global.Microsoft.VisualBasic.ChrW(13) & Global.Microsoft.VisualBasic.ChrW(10) & "WHERE (AuthorEmail = @AuthorEmail) AND (AuthorPassword =" & _ " @AuthorPassword)" Me.SqlSelectCommand1.Connection = Me.MDData Me.SqlSelectCommand1.Parameters.AddRange(New System.Data.SqlClient.SqlParameter() {New System.Data.SqlClient.SqlParameter("@AuthorEmail", System.Data.SqlDbType.NVarChar, 50, "AuthorEmail"), New System.Data.SqlClient.SqlParameter("@AuthorPassword", System.Data.SqlDbType.NVarChar, 50, "AuthorPassword")}) ' 'DaAuthorLogin ' Me.DaAuthorLogin.SelectCommand = Me.SqlSelectCommand1 Me.DaAuthorLogin.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "T_Author", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("AuthorAlias", "AuthorAlias"), New System.Data.Common.DataColumnMapping("AuthorEmail", "AuthorEmail"), New System.Data.Common.DataColumnMapping("AuthorPassword", "AuthorPassword"), New System.Data.Common.DataColumnMapping("LastName", "LastName"), New System.Data.Common.DataColumnMapping("PreferredName", "PreferredName")})}) ' 'MDData ' Me.MDData.ConnectionString = "Data Source=CIS1022DAVID;Initial Catalog=CGData;Integrated Security=True;Pooling" & _ "=False" Me.MDData.FireInfoMessageEventOnUserErrors = False ' 'DsAuthorLogin ' Me.DsAuthorLogin.DataSetName = "DsAuthorLogin" Me.DsAuthorLogin.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema CType(Me.DsAuthorLogin, System.ComponentModel.ISupportInitialize).EndInit() End Sub Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand Friend WithEvents MDData As System.Data.SqlClient.SqlConnection Friend WithEvents DaAuthorLogin As System.Data.SqlClient.SqlDataAdapter Friend WithEvents DsAuthorLogin As MedicalDecisions.DsAuthorLogin 'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it. Private designerPlaceholderDeclaration As System.Object Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub #End Region Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) 'no code here End Sub Private Sub AuthorLoginRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AuthorLoginRegister.Click 'for new author registration Response.Redirect("AuthorInformation.aspx") End Sub Private Sub AuthorLoginBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AuthorLoginBack.Click 'to navigate back Response.Redirect("MainPaths.aspx") End Sub Protected Sub AuthorLoginPassword_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AuthorLoginPassword.TextChanged 'pass the parameters to the dataadapter and return dataset DaAuthorLogin.SelectCommand.Parameters("@AuthorEmail").Value = AuthorLoginEmail.Text DaAuthorLogin.SelectCommand.Parameters("@AuthorPassword").Value = AuthorLoginPassword.Text MDData.Open() DaAuthorLogin.Fill(DsAuthorLogin, "T_Author") MDData.Close() 'set session objects If DsAuthorLogin.T_Author.Rows.Count > 0 Then Session("AuthorAlias") = DsAuthorLogin.T_Author(0).AuthorAlias Session("LastName") = DsAuthorLogin.T_Author(0).LastName Session("PreferredName") = DsAuthorLogin.T_Author(0).PreferredName Response.Redirect("AuthorPaths.aspx") Else : AuthorLoginNotValid.Visible = True AuthorLoginEmail.Text = "" AuthorLoginPassword.Text = "" End If End Sub End Class