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

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

Database trigger & permission

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
I created a DDL trigger to keep checking on who alter, create views/tables in a database. Almost most of the update are doing by poweruser. When I try to grant execute right to them.  SQL: GRANT EXECUTE ON [trgLogDDLEvent] to #ABC_Admin It prompts error: GCannot find the object 'trgLogDDLEvent', because it does not exist or you do not have permission. however, when I find the trigger by SQL. It does exist. What is the problem? select * from sys.triggers  

View Complete Post

More Related Resource Links

execute update timedout expires , table with trigger due to recovery of database


I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B is being recovered. Waiting until recovery is finished.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean ret

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.

Windows 7 CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 26

Hi I have migrated to a new computer using Windows 7, 6 gig memory, I7 chip (old machine had xp) and have installed Visual Studio 2008 and  SQL Server 2008 R2.  I get CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262) when I try to create a new database.  Other posts (example:http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/28fee0ed-c7e2-40df-8f79-f513c9848f09/) work for Vista but I cannot find how to grant permissions.  Run as administrator (vista fix) does not work. I have admin privaleges on my machine.  There are no other accounts.    What do I need to do to update privaleges? Thanks and have a nice day.    

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

Cant create new database / CREATE DATABASE permission denied in database master (error 262)

   I am using SQL express and Visual web developer on windows Vista. When I try to create a new database the following message appears.   CREATE DATABASE permission denied in database master (error 262) I log on to my computer as an administrator. Help appreciated  Prontonet      

A USE database statement is not allowed in a procedure, function or trigger.




I’m automating a large Archiving\Purging script on 1TB multi-databases.


I run all Sp’s from Archive database (Destination database).


The log file gets full fast in both source and destination databases and the whole process stopped, I have to clear all log files, as well I have to shrink file after clearing it.


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'.

DDL Database Trigger


I have a DDL Trigger 'FOR DDL_DATABASE_LEVEL_EVENTS'  that works great for all users that are "sysadmin", but does not fire for new users that have limited security.  I thought that a DDL trigger fired on an event and did not look at the users.  I have provided the DDL Trigger below.....

Thank you in advance


/* This will install a dll trigger on every database except tempdb */

       ,@v_MAX INT
       ,@v_db  SYSNAME
       ,@v_SQL NVARCHAR(MAX)

  col1   INT IDENTITY (1,1),
  dbname SYSNAME

INSERT @t_db
  FROM master.dbo.sysdatabases (NOLOCK)
 WHERE name <> 'tempdb'
 ORDER BY name

SELECT @v_MIN = MIN(col1)
      ,@v_MAX = MAX(col1)
  FROM @t_db

WHILE @v_MIN <= @v_Max

  SELECT @v_db = dbname
    FROM @t_db
   WHERE col1 = @v_MIN
  'EXEC ['

create database permission denied in database


Hi All,
Now i am using SQL Express Edition in my laptop but in my laptop no administrator rights.
so open sql server and create New DB i get the error for "Create database permission denied in database'Master'.(Microsoft SQL Server, Error: 262)"

please help me on this.



help: user "sa" cannot make DDL changes after I turned on DDL auditing via database trigger


I created a database trigger to track DDL changes to SQL 2008 database:



I have already created the AuditDB database and DDLAudit table:

   CREATE TABLE dbo.DDLAudit ...


SQL Express 2008 R2 - CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Serve


So, we have an application that we've written to leverage SQL Express 2008 R2 - which our setup installs on the command line using the following instructions:


The code that does the installation can be found here:


//Path to the SQL Installation files 

string strSQLPath = Application.StartupPath + "\\Install_Support_Files\\SQL_86\\Setup.exe"; 

//If we're on a 64-bit OS use the 64 bit setup

if (Wow.Is64BitOperatingSystem == true)


strSQLPath = Application.StartupPath + "\\Install_Support_Files\\SQL_64\\Setup.e

Published Access 2010 Database to Sharepoint 2010. You do not have permission to view the records.


I have published an Access 2010 database to Sharepoint 2010.  It is pretty basic and essentially it is just used for teachers to enter lesson plans.  When viewing the site there is a form that has several dropdown menus.  The user should be able to choose their name, date, etc. from these dropdown menus.  The problem is that when I click on a dropdown menu the menu just shows a message that says "This service isn't available right now".  I also get a message that pops up which says "You do not have permission to view the records.  To obtain permission contact your site administrator."

The weird thing is that when I was testing this at one point I clicked the dropdown menu and it did work.  I clicked off the dropdown menu and went right back to it again and it didn't work.

I have anonymous access enabled for this site.  However, I have tried it with an anonymous user as well as with the user account that installed Sharepoint and published the database to Sharepoint and it doesn't work either way.

Is this a permission issue on the site or something else?  Any help is greatly appreciated.

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

VIEW DEFINITION database permission


Am I looking at this wrong or is this how it is supposed to be?

Why is it that if I do


the properties window in Mgmt Studio for some_db_user doesn't show this in Securables? Don't you think this is confusing? If someone is not familiar with system views and doesn't know to a query like

select p.name, perm.*
from sys.database_permissions perm
join sys.database_principals p
on p.principal_id = perm.grantee_principal_id
perm.permission_name = 'VIEW DEFINITION'


then you would never know that some_be_user had this permission.

Will a ROLLBACK in a subscriber database trigger cause the replication engine to abort?


We have some data retention policy deletes for a table in the publisher database that we do not want to be replicated to the subscriber database.  Our method of implementation is to write a delete trigger for the table in the subscriber database that intercepts the deletes by checking the date associated with the deleted data, and if the date is < 2 months ago we execute a ROLLBACK in the trigger.  The ROLBACK is effective and we get the desired results, but this however produces the message:

    Msg 3609, Level 16, State 1, Line 2 

    The transaction ended in the trigger. The batch has been aborted.

Is this message a problem for replication?  Will the replicaton engine abort?  Is this just an informational message, a warning message, or error message?

The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.


I am getting the following error when I execute an SSRS report.

The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

The report is executing a stored proc. Which in turn is running an Agent Job.

When I run this report through BIDS it works fine. It is only when I run it though Report Manager I get a problem.

Can anybody help?

Mr Shaw

SQL Express 2008 R2 - CREATE DATABASE permission denied in database 'master'


I have created a setup application with SQLEXpress 2008 as a prerequisite. On test SQLExpress installs OK

after the application is installed it must create and install a database using a mixture of SMO and Scripting. I used SMO to add the current user (who is a Windows Administrator) as a Login to the SQL Express server.

Dim l As Login = New Login(srv, My.User.Name)
                l.LoginType = LoginType.WindowsUser
                l.DefaultDatabase = "MASTER"

It fails on srv.CreateDatabase with error message "permission denied in database MASTER"

this thread talks about converting the server to single user mode before adding a login


how is it possible to scrip this? there is no&nbs

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