.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SQL+SMO; how to get object level permission

Posted By:      Posted Date: October 09, 2010    Points: 0   Category :Sql Server

Hello Experts,

i wrote a little application to helps me to script the whole database with their tables;stored procedures; indexes; ...
Here is my code

Dim objServer As New Server("MyServer")

Dim objDatabase As Database

Dim objCollString As System.Collections.Specialized.StringCollection = Nothing

Dim objFile As New FileStream("c:\Documents and Settings\USER\Desktop\CreateAlyceDatabaseByApp.sql", FileMode.Create, FileAccess.Write)
Dim objFileWriter As New StreamWriter(objFile)
objFileWriter.BaseStream.Seek(0, SeekOrigin.End)

            objServer.ConnectionContext.LoginSecure = True
            objDatabase = objServer.Databases("myDataBase")
            objCollString = objDatabase.Script()

            For Each strScript As String In objCollString
            objFileWriter.WriteLine("USE [" & objDatabase.Name & "]")

            'SCRIPT SCHEMAS
            objFileWriter.WriteLine("-- SCHEMA SCRIPTS")
            For Each objschema As Schema In objDatabase.Schemas
                If Not objschema.Name.StartsWit

View Complete Post

More Related Resource Links

Insert permission was denied on Object '', database '', schema ''

i have a senario where in i am trying to insert data into a Database , i end up getting the above error 'Insert permission was denied on Object '', database '', schema '''.We have a DB which has all Storeprocedures and 90% of the tables . the remaining 10% of the tables are in a different database.I am having trouble inserting data into the second database.The storeprocedure is executed as a user who has permission on the DB and tables.Also the storeprocedure is given permission to a role in which the user is a member . The same role doesnt exists in the 2nd Database.I am able to do all DB operations in one environment . i am unable to do the same in the others.

Managing business object locks on application level

Today I worked out simple application side object locking solution for one server scenario. My motivation came from ASP.NET architecture forum thread How to solve concurrent site issue where one user asks for locking solution that works without changing database. Here is my simple and primitive solution that should help this guy out. I added also sample solution to this posting. My solution is simple: Create class for locked items. Create manager class that holds locks and manages them. Clear locks when session ends. Create page to release all locks. If you need something more serious then you should keep locks information in database or better than that - use some lock server. Also you may consider developing WCF service. LockItem class As a first thing let's create class that keeps lock information. I call this class as LockItem. This class doesn't hold references to locked objects - only type and ID as there are many business layers that doesn't use globally unique identifiers for objects. public class LockItem {     public Type ObjectType { get; set; }     public int ObjectId { get; set; }     public string SessionId { get; set; }     public DateTime LockedAt { get; set; } } LockItem class also contains property for session because otherwise it is not possible to kno

user permission in granula level in SharePoint version 2

Hi, everyone:   I am administrator of a SharePoint v2 site. I found that I can either give "view item" or "view item, insert item, remove item and update item" permission to users, no middle. However, I do wish to give some users "view, edit" permission, but no "insert and remove item" permission.   So, does SharePoint v2 support this in out of box feature? if yes, how to make it, if no how can we achieve in easiest way?   Thank u MySTfrom Finland :D

The SELECT permission was denied on the object 'sysjobs_view', database 'msdb', schema 'dbo'.

So, I have an SSIS package that uses a transfer jobs task between Server A and Server B.  When I run the package with my credentials, everything works fine.  However, when I run the package from an SSIS using a different user, I get the following error: Date 9/1/2010 8:00:00 AM Log Job History (xxx) Step ID 1 Server xxx Job Name xxx Step Name Run SSIS Duration 00:00:01 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:00:00 AM Error: 2010-09-01 08:00:01.39 Code: 0xC002F325 Source: Transfer Jobs Task Transfer Jobs Task Description: Execution failed with the following error: "An exception occurred while executing a Transact-SQL statement or batch.". End Error Error: 2010-09-01 08:00:01.39 Code: 0xC002F325 Source: Transfer Jobs Task Transfer Jobs Task Description: Execution failed with the following error: "The SELECT permission was denied on the object 'sysjobs_view', database 'msdb', schema 'dbo'.". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:00:00 AM Finished: 8:00:01 AM Elapsed: 0.75 seconds The strange thing is, the user running the job ahs sysadmin permissions

Select Permission Denied on object

Hello all, I have just begun to develop a simple web application to maintain phone book / contact details of people. I have been facing problems wrt the connection to the database, while trying to execute the reader it throws this error - Server Error in '/phonebook' Application. SELECT permission denied on object 'PhoneBook', database 'Northwind', owner 'dbo'. 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.Data.SqlClient.SqlException: SELECT permission denied on object 'PhoneBook', database 'Northwind', owner 'dbo'. Error occurs at - reader = cmd.ExecuteReader(); The stacktrace looks like this -[SqlException: SELECT permission denied on object 'PhoneBook', database 'Northwind', owner 'dbo'.]   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742   System.Data.SqlClient.SqlCommand.ExecuteReader() +42   Phonebook.ResultPage.CreateDataSource() in c:\inetpub\wwwroot\phonebook\resultpage.aspx.cs:56   Phonebook.ResultPage.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\phonebook\resultpage.aspx.cs:39   System.Web.UI.Control.OnLoad(EventArgs e) +67   System.Web.UI.Control.LoadRecursive() +35    Syste

How to all the Subsites and Site Collections under a top level site collection using client object m

Hello Everyone, I need to know how to get all the subsites and the site collections under a top level site collection using client object model in SilverLight. When I use WebCollection webcoll= ctx.Web.Webs I'm getting the getting subites but i'm not getting the RootWeb and Othe SiteCollections created under the Top Level site collelction.

The EXECUTE permission was denied on the object 'proc_UpdateVirusInfo'

Hi All,   SharePoint 2007 x64 w/SP1, SQL 2005 x64, ForeFront for SharePoint w/SP1, running on Windows Server 2003 x64 R2 w/SP2.   We originall setup this server using only a couple service accounts. That was a bad idea so a few weeks ago I went over everything and setup or service accounts according to Microsofts recommendations. (http://technet.microsoft.com/en-us/library/cc263445.aspx)   I've gone over the requirements a dozen times and everything is set perfect.   I have two issues.   1. Randomly we have a SQL permission issue popping up, this abouts a couple times a day (some days none), the event log will fill about with anywhere from 15 to 800 of the same event.   Event Type: ErrorEvent Source: Windows SharePoint Services 3Event Category: Database Event ID: 5214Date:  3/11/2008Time:  1:28:30 PMUser:  N/AComputer: TRUMANDescription:Insufficient SQL database permissions for user 'PortalPool' in database 'SharePoint_AdminContent_45f0f48e-c1b1-463c-99e3-ea6648beaf37' on SQL Server instance 'Truman'. Additional error information from SQL Server is included below. The EXECUTE permission was denied on the object 'proc_UpdateVirusInfo', database 'SharePoint_AdminContent_45f0f48e-c1b1-463c-99e3-ea6648beaf37', schema 'dbo'. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.   PortolPool is our service account for the IIS Poo

Access is denied when creating Alert me on a folder level permission is this a known bug?

Hi, A user is getting an Access Denied error when creating an Alert Me on a folder level. The security set to the user is, Read access to the list but without permission to Create an alert, and he has a Contributor access to the folder. The security was perfect, he can't create an alert to a list level, but Alert Me is available on a folder level. When the user clicks on the Alert Me on a specific folder it will bring him to the alert configuration page, BUT, he would get an Access Denied error when hitting the OK(Save) button. Is this a known issue in Item/folder level security? The fact that Sharepoint allows user to see Alert Me function I assume that it should function correctly, but this is not the case. Hope to hear from you guys! TIA!  

WPF TreeView child nodes - collection is not a member of the first level nodes object



I was wondering if i could use a TreeView to display data from 2 separate collections where both collections are members of the same object. For example i have a view model that has 2 observable collections:



Each model has a property Brand which specifies the brand for each model. What i want to do is bind the first level to the Brands collection and the second level to the Models collection and specify a filter.

Thanks in advance.

how to store the values in a object variable (package level scope) in a script component

I have declared a variable of object type with package level scope.  I want to assign values to this variable inside a script component.  My object should hold 5 columns in a row.  How to implement this ?

Item Level Permission does not work as designed


Here is the problem. 

  1. We have a site with a site members group with created permission level called vnContributor that differs from contributor in that they cannot edit delete items or versions, or create alerts. 
  2. We have a site owners group who have a custom permission level called vnOwner that allows them to add, edit, delete, view, and open items and view versions.  They do not have "Manage Lists" 
  3. The list permission settings were set to view everyone and edit everyone.  In this setting, however members could not see or edit anything, and owners could see and edit everything, regardless of whether it was theirs or not. 
  4. So I changed the list permissions level to edit only their own and changed the members to the OOTB contributor permission level.  I then opened the calendar and added a new item as a user in the members  group.
  5. There was no change.  Members still could not edit their items.  Owners could see and edit everyone's items.
  6. This is not how this is advertised to work. 


SQL Server 2005 permissions error. The EXECUTE permission was denied on the object "xp_instance_reg


I have a SQL Server host running SQL 2005 9.00.4294 x86 Standard Edition running on Windows build 2195 SP4.  My client workstation is running only SQL Server 2005 workstaion components.   When a user of the host who has db_owner access attempts to view the properties of a table by right-clicking the table and then clicking Properties, the following error mesage is displayed:  

"The EXECUTE permission was denied on the object "xp_instance_regread", database 'mssqlsystemresource', schema 'sys'

For security purposes, I do not want to grant execute on xp_instance_regread to these users. Does anyone know of a workaround that will allow members of db_owner to access table properties using the abovementioned method and does not require execute access to be granted?




EXECUTE permission denied on object 'sp_OACreate', database 'mssqlsystemresource'


HI There

I have a user login that is executing an sp. It gets the follwoing error:

Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1

EXECUTE permission denied on object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1

EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1

EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.

Can't add item using client object model of sharepoint 2010 with read only permission to user.



I have already developed Silverlight application in SharePoint 2010 using client object model.

But I have one scenario that user can comment on the item, so there is a update operation come in picture for user, but user have read only permission to site.

So please help me resolved this problem.

We can use Run with elevated privileges method in SP object model. I think Silverlight client object model don’t support this method.

Is there any alternative workaround to solve this problem?


EXECUTE permission was denied on the object 'proc_putObject',



I am getting the following error, while I try to take the backup of sharepoint application.

EXECUTE permission was denied on the object 'proc_putObject',


Srikanth Reddy

Item level permission for Custom List in sharepoint


Hi all,

  I have created a custom list with work flow associated with that. The workflow takes the item through different levels of approval.

My workflow scenario is like Say an initiator add an item, which will go to manager for approval. When the manager approves, few columns in the current list will get updated. On manager approval it will be forwarded to head of department. Again when the Dept head takes an action, the column values of the list get updated. For all these users i have set Contribute permission. But the problem is that an item started by an initiator should not be editable or deleted by other users using the pull down menu that appears for each item. Only the owner of the item and manager should have permission to edit it using the pull down menu. When i tried changing the edit access for the item through Advance settings-->Item level permission --Edit access being set to "Only their own" while manager or dept head approving i get an access denied error message.

Can any one please suggest me what is the work around for this?



Regards Reshma

The SELECT permission was denied on the object 'aspnet_Membership', database 'Employee', schema


I am having IIS server and SQL server on two different machines. 

In the SQL server I created a new user using the SQL Authentication.

In my application I am using a connection string like this:

<add name="UsrConnectionString"
      connectionString="Data Source=\GEOGSQL;Initial Catalog=ITAM; 
      User Id=itam; 
Password=itam_web;" providerName="System.Data.SqlClient" />

<add name="UsrConnectionString" connectionString="Data Source=x.y.z.w\instancename;Initial Catalog=Employee;  User Id=xyz;  Password=xyz;" providerName="System.Data.SqlClient" />

I can access the other tables within the Employee database. But I am unable to access the tables created by ASP.NET autom

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend