MS SQL Server :: Only Members Of Sysadmin Role Are Allowed To Update Or Delete Jobs Owned By A Different Login

Error Message Only Members Of Sysadmin Role Are Allowed To Update .....

Mar 7, 2007

Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below

by the way I am a member of the sysadmin group

thanks in advance

km

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. 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 returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---

View 1 Replies View Related

Table Query Fails With Object Not Found Error After Assining Sysadmin Server Role To Login

May 14, 2015

I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.

View 5 Replies View Related

Create DB Role Owned By Public

Jul 20, 2007

I use the following script in order to create db role:

USE [MyDB]GOCREATE ROLE [myRole] AUTHORIZATION [public]GO

It doesn't work:
Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'public'.

However this code works fine:

USE [MyDB]GOCREATE ROLE [myRole] AUTHORIZATION [dbo]GOALTER AUTHORIZATION ON ROLE::[myRole] TO [public]GO

So the question is why?

View 6 Replies View Related

Only Members Of The Sysadmin And Bulkadmin Fixed Server Roles Can Execute BULK INSERT

Aug 29, 2007

We would like to use the bulk insert function to import large CSV files into a SSE database however we have serious concerns regarding giving all our users these high privleges. Is there some way around this can we give them the privleges temporarily do the insert and take it away again or some other solution.

View 5 Replies View Related

Sysadmin Role

Jan 30, 2006

Hi All,

Is it possible to give a user a sysadmin role and then deny some of the privileges?

I am a junior dba, I should be able to view only everything that the sysadmin can see, i.e. db properties, logins, packages, jobs etc.

View 5 Replies View Related

SysAdmin Role

Feb 10, 2004

Hi
I'm new to SQL Server. I have created a databased named Sample and
I hae created the user with login named "Sman".
SMan owns some tables and sp's. I'm able to access the tables and SP's when I was logged in as Sman in Query analyser. I have given a Sysadmin privilege to Sman then I'm not able to access the tables and sp's when i try to login with Sman.

ie, Select * From tabl1 is not working But
Select * From Sman.tabl1 is working. I dont know Why is it so?
Can any one help me!

Thanks in Advance

View 1 Replies View Related

SECURITY - Sysadmin Role

Feb 11, 2004

Hi all,

Our SQL2000 server now allows all member of the Windows 2000 local administrators group log in with SysAdmin role.

I only want couple of people with sysadmin role. What should I do
to prevent that. I was told once that I should delete the
BUILTINAdministrator ID and manually add each window login ID
to SQL server. Am I on the right track?

Thank you for your help.

View 1 Replies View Related

Sysadmin Role Problem

Oct 5, 2007

We set up a SQL Server 2005 box. When we set it up we did it with windows authentication, so the sa account is disabled. After everything was set up we were going to do some locking down. We added a domain account and gave it sysadmin rights. Next we went in to the sysadmin role and deleted the builtin/administrators group. This deleted all users except for the sa account. The sa account is disabled and we don't have a password for it. Not sure what to do next. Is there anyway to salvage this without have to completely reinstall? I can't enable sa because it says I don't have the rights, I can't add anyone to the sysadmin role because I don't have the rights. Why did it delete my domain account in the first place?

View 1 Replies View Related

Sysadmin Role - Easy Question

May 17, 2007

Alright, this should be a simple question, but I don't know the answer.

I created a group in our Active Directory, and added myself and another member of my team to the group. I then went into SQL 2005 management studio and added the group as a new login, and gave the group the sysadmin role.

The idea is for us to be able to connect with Windows Security to do administrative tasks on the server versus logging in with SA.

The problem, is that we cannot connect. It does not allow us to login. Do I have to do mappings for each database too?

View 4 Replies View Related

Is It Possible To Reconfigure The Sysadmin Server Role?

May 11, 2008

Hi all,

Is it possible to reconfigure the sysadmin server role?
How can I see it's permissions?
Thanks,
Assaf

View 1 Replies View Related

Can't Read Tables If Not In Sysadmin Server Role

Jan 16, 2008

Listed below are some basic info about my application.

.net 2.0 web application

SQL 2000 database

Single sign-on. Windows Authentication with impersonate set to true in web.config:

<authentication mode="Windows"/>

<identity impersonate="true"/>

Connection String in web.config: <add name="conn" connectionString="Data Source=MyRemoteSeverName;Initial Catalog=MyDBName;Integrated Security=SSPI; " providerName="System.Data.SqlClient;"/>

The problem I'm having now is that any users that are not in the sysadmin server role can't read any table. Say if I have 2 users:

Domain1User1 ( in db_datareader and db_datawriter Database Role, no sever role assigned)
Domain1User2 ( in db_datareader and db_datawriter Database Role, sysadmin Server Role)

Here is what's expected to happen if everything goes well.

A list of customers ( first name, last name, age, etc) in a gridview should show up after login.

After both users logged in, Domain1User1 received an exception message of "Object reference not set to an instance of an object".

Domain1User2, however, was able to see the list.

I checked SQL Server Logs, and found 2 items:
Login succeeded for user 'Domain1User1' ... Connection TRUSTED
Login succeeded for user 'Domain1User2' ... Connection TRUSTED.
Based on the log file, it appears that both users had good connections to the database. Then why is it that User1, which is not in sysadmin Server Role, was not allowed to make a query?

Now if I assign sysadmin Server Role to Domain1User1 as well, the User1 will be able to see the list without seeing the exception.

Can someone shed some light for me please ?

View 5 Replies View Related

How To Allow Non-SysAdmin, Non-Owner To Run Jobs?

Oct 25, 2002

I need to have a NON-sysAdmin, NON-Owner to be able to run specific jobs.

I'm ok on the non-sysadmin part, but how can I allow someone to run a job she does not own?

Thanks
Michael

View 3 Replies View Related

Can Not Sysadmin Member See And Run Jobs From EM?

Jan 25, 2002

Hello everybody .
I have a group - SQL support.I want to give them rights to run any job from EM but I don't want them be a part of sa group
What rights should I give them ?
All existing jobs owned by members of sysadmin group.

Thank you

View 1 Replies View Related

Master Data Services :: Hard Delete All Soft Delete Records (members) In Database

May 19, 2012

I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.

View 18 Replies View Related

Dbo Owned Stored Procedure Hitting Another User Owned Table

Aug 13, 2007

I am trying to come up with a solution that does not involve having a version of every stored procedure for every user I have...
Here is the problem...

I am going to have multiple users that need to have their own "product table". The structures are going to be the same for all. We currently only have one user and it is a DBO... all stored procedures are dbo.[sp name]... is there any way to get it so that the product table in the SP will be the user owned product table and not the dbo table??

I have tried just taking out the dbo prefix with no luck... the user's default schema will match the table they own so when they do a straight select they get the right information but it is just the SPs that I can't seem to get to work...

The only thing that I have come up with is making the SPs dynamic with having the username as parameter.

Is there anything else I can try??

and SQL 2005 SP2 on Win 2003 SP2

View 2 Replies View Related

Running Jobs Under Non-sysadmin Credentials

Mar 7, 2007

Hi,

Is it possible to make jobs visible to people other than the job owner?

Can jobs run under non-sysadmin account?

Thanks,

Gulden

View 3 Replies View Related

'sysadmin Fixed Server Role' Error When Running A SQL Trace

Apr 20, 2006

Hi there. I work in a support department and on great occasion (such as this morning), I am RASd in to a client and try running a SQL trace, only to receive an error when setting it up, 'In order to run a trace against SQL Server you have to be amember of sysadmin fixed server role.'

Today, I even called their DBA and asked him if he could set our userid up with the proper permissions to all us to run traces (I'm debugging a RTE). He stated that he was unfamiliar with the error and didn't know where to assign us to resolve this problem.

Does anyone know how to resolve this?
Many thx.

View 1 Replies View Related

SQL Security :: Sysadmin Role Not Taking Effect For Windows User?

Aug 11, 2015

My company has a Windows 2008 R2 server which is running SQL Server v11.0.5058. This server was previously running SQL Server 2008 and was recently upgraded. Since the upgrade I have noticed that when I connect to this server using SSMS and Windows authentication it seems as though I have a limited user context as I cannot see SQL Agent in the server tree at all and underneath the server security > logins folder I can only see the sa and SQL Server Windows service accounts (there are many more).

If I connect to the server using SSMS and the sa credential then I can see everything I expect to be able to see as a sysadmin.

I tried connecting as sa, then deleting my Windows AD account from the security > logins folder and reading my Windows AD account with the sysadmin role however this yielded the same result, when I connect using Windows authentication I still appear to be in a limited user context.

We have several other SQL 2008 / 2012 servers within our organization and all of them appear to be working fine / none of them exhibit this problem.

View 8 Replies View Related

Snaphot Fails Unless Sysadmin Fixed Server Role Permission Granted

Jan 16, 2007

Edition: SQL Server 2005 Standard
I am trying to take a snapshot of a database for use in a publication. The account under which the snapshot agent is running is set to have the db_owner role for the database and have write access to the snapshot share.

I can not get the snapshot to run unless the account under which the snapshot agent is running is granted the sysadmin fixed server role. Because of the security implications of this, I don't want to grant these permissions.

As far as I am concerned, the minimum requirements for the snapshot account have been met and I have tried every other alternate that I can think of. I've checked MSDN and the newsgroups but I still have not solved the problem.

The error that I get when I run the snapshot.exe from the command line is:
The remote server "TURING" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.

This error message has now inexplicably changed to:
You do not have sufficient permissions to run the command...

Any ideas? Thanks.

View 3 Replies View Related

Setup And Upgrade :: Users With Sysadmin Role Can't Access Network Drives

Oct 12, 2015

When I create logins for SQL Server 2012, something strange happens. When I assign the sysadmin role to a login, the login loses the access to the network drives (for example when creating or attaching databases). The only possible place is the c:drive of the computer that is running SQL Server.

View 11 Replies View Related

Sa Vs Sysadmin Login

Jul 23, 2005

is there a difference in the previleges of 'sa' login and other loginwith 'sysadmin' role (and 'db_owner' for all databases) ?can they do the exact same things ?

View 1 Replies View Related

Need A Sysadmin Login

Oct 20, 2006

We��re running mixed mode authentication on our SQL Servers. To make the server ��safer�? builtinadmininstrators no longer have sysadmin role on the sql server. If there is only one login with sysadmin role, and we lose track of the password, is there any way to recover it? How could we reset the password or create a new sa account with a new password? This situation has not occurred, but I��m worried about how to recover from it should it occur. This question relates to SQL 2000 and SQL 2005.

David Zokaites,
DBA & Software Engineer

View 4 Replies View Related

Non SysAdmin Accounts Cannot Login

Jun 12, 2007

I have a SQL2005 in a cluster environment, for some reason the only way that user accounts can login to either the database or SSMS is to grant them the SysAdmin role. This access is a little to high for my liking and am wondering if anyone else has come across this before.

Thank you

View 15 Replies View Related

Login Problem By Not Being In Sysadmin Group

Jul 23, 2007

Hi All

I experience a very strange login problem:

I create standard security login, let say test1/test1 with a default db test and assign it sysadmin group.

All is working well.

The moment remove sysadmin group from this login, i start getting errors:

Login failed for user 'test1'

... when I login from remote server. If I login from the same host - it continues with no problem.

When I go to sql server configuration manager, I see next:

sql native client configuration(32bit):

shared memeory enabled

tcp/ip enabled

named pipes enabled

VIA disabled

The same settings from sql server 2005 network configuration / protocols for mssqlserver

sql native client configuration / client protocols

sql 2005 surface area configuration / remote connections is configured:

local and remote connections (checked), using both tcp/ip and named pipes.

Does anybody have a clue?

View 3 Replies View Related

Group Login Members

Feb 20, 2008

Hi All,

'SACCAPRiskGroup' is my group login which has few users.
Now i want to know all users in this group. For this i am using
EXEC sp_helprolemember 'SACCAPRiskGroup'.

But no luck.

Can any one help me in this.

Thanks.
Malathi.

View 2 Replies View Related

Scheme Login Can't Access Tables Owned By Scheme

Dec 4, 2007

Hi,

I've having some baffling problems with a java applicaiton!

I have an application server called WA01 which used to access two tables on an MS SQL 2000 server via a scheme login. The two tables are:

_PurchaseOrderInterface
_PurchaseInvoiceInterface

Both tables were owned by the scheme user, with enough permissions to read & write. The java application on server WA01 could happily read the data within the tables and write a bit flag back to each row.

The MS database has been moved to a new server which no longer allows the java application on server WA01 to access the tables via the scheme login, the only way the java app can view and update the tables is by changing the owner of the tables to dbo. The new server is still MS SQL 2000, with comparible security settings.

The java app keeps complaing of an unknown source when trying to access via scheme, is this a domain trust issue between the two servers? Any ideas would be welcomed. I'm not an SQL expert but have a good grasp of the security structure etc.

Regards,

Alan

View 1 Replies View Related

SQL 2012 :: Create Login With Sysadmin And Add To Local Server Admin Group

Apr 30, 2015

Need script for below.

1)Add the user ''ADabc' to local admin group in server.
2)Create login 'ADabc' and Grant sysadmin access for ADabc

View 3 Replies View Related

Transact SQL :: Not Allowed To Write Delete Statements In A Function?

Jun 25, 2015

We are not allowed to write delete statement in a function. It will give the below error.

"Invalid use of a side-effecting operator 'DELETE' within a function."

We will get the same error for truncate/drop statements.

What is the reason behind this error?

View 8 Replies View Related

To Delete A Role...

Aug 30, 2001

Hi Everybody,
Experts........
i am using Sql server7.0
I want to Delete existing Role How Can I Delete it.
Anybody can please Tell me Immediately...
I tryied Sp_DropRole But it display's message successfully
completed but role of that name is still there...

Thanx a Lot in Advance.....(after solving this problem)
(Mohanlal)

View 1 Replies View Related

Analysis :: Cannot Delete Role

Aug 24, 2015

we are using a 2012 SSAS cube with many (about 100-200) roles. We use AdoMD to programmatically create, edit and drop roles. Everything works fine until we want to drop a role (this occurs only with some of the roles). I thought this was an AdoMD problem, but I actually get the same error when I delete such role in Management Studio. I attached the full exception message. I tried to remove all members before dropping the role to no avail, there must be some other dependency.

Program Location:

at Microsoft.AnalysisServices.Server.SendBatch(ArrayList objectsToDelete, ArrayList objectToAlter)
at Microsoft.AnalysisServices.MajorObject.DropPrivate(DropOptions options, XmlaWarningCollection warnings, ImpactDetailCollection impactResult, Boolean analyzeImpactOnly)
at Microsoft.AnalysisServices.MajorObject.Drop(DropOptions options)
at Microsoft.SqlServer.Management.SqlManagerUI.DropOlapObjects.DoDropObject(Int32 objectRowIndex)
at Microsoft.SqlServer.Management.SqlManagerUI.DropObjects.DropAllObjects(Boolean stopOnError)

View 2 Replies View Related

Slowly Changing Dimension:inferred Members Update

Jul 12, 2006

Hi,

Anybody who knows how inferred members update function in slowly changing dimension? I came across this when I started using some of the functions of the Slowly changing dimension object.

Thanks!

cherrie

View 4 Replies View Related

Removing Login's From Public Role

May 24, 2000

Hello all,

I'm having trouble copying my production database to a development version because I have a login user in the public role that is not a valid user. I can't find any reference to the login/user anywhere in my database or in NT security, on my server for that matter.

When I open the public role through Enterprise Manager, I can see the logins/users in the list, but the 'Remove' button is disabled. I also tried to use the stored procedure 'sp_droprolemember', but it says that 'public' is not a role in the database.

DTS keeps blowing up on this object when exporting, and I need to get this stuff copied ASAP.

Thanks for all help

Kevin

View 1 Replies View Related