The Database Principal Is Set As The Execution Context Of One Or More Procedures ...
Aug 14, 2007
I have database in the database there are a few users that no one is used. When I try to drop thpse users I got next error message:
"The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped." (Msg 15136)
Indeed, I think that those users have execute rights on store procedures.
How do I find for wich procedures or other database objects those users have grants?
How do I delete them from database (and maybe from logins of the server)?
How can I see what grants a user has?
How can I see what grants does STP has?
SQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:
The server principal "XYuser" is not able to access the database "Ydb" under the current security context
This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john
Hi guys. I created a database "MyDatabase" using an AD Account "user01" which is a member of a domain group "GroupA". I have created a login in my SQL Server 2005 for this AD group. I have also added the said login as a user of my database. I created other AD users and put them in the same group. When I try to access the database using the other users, I get this message
The server principal "MyDomainuser02" is not able to access the database "MyDatabase" under the current security context.
This KB tells me that my situation is triggered by the first case in which the owner of the database is windows authenticated. I have already set the database to be TRUSTWORTHY. I haven't tried CERTIFICATES though. I believe I haven't done anything complex to warrant this solution.
I'm really lost with the myriads of principals and securables that SQL2005 has. All I want is just to execute a certain set of stored procedures. I can't do this because I can't even execute "USE MyDatabase". What are the permissions I need to give to "GroupA" ?
I have a SSIS package developed by a different user which does a lot of DML. This package sits on the server.
The package needs to be executed on a regular basis.
I have given RO access for a regular user on production DB, he is executing the package from his client desktop.
I was expecting this execution fail, since the package is doing lot of INSERTS , where the user has ONLY RO access.
I understand from the above experience that there is an “execution context� for SSIS execution. Can someone tell me how can I define the execution context for SSIS?
------------------------ I think, therefore I am - Rene Descartes
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
Trying to get my hands around all the new security features of SQL Server 2005. In Management Studio did something I don't know how to undo. I added a database role ReadOnlyRole and clicked the box next to db_datareader in the owned schemas box. Then I tried to remove the ReadOnlyRole and could not. How do I undo what I did? Is it possible?
The below is the TSQL that generates the my issue.
Use [master] go create database [test] go
USE [test] GO
CREATE ROLE [ReadOnlyRole] GO
USE [test] GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReadOnlyRole] GO
I am trying to grant various users permissions and am using the following sql command
GRANT Delete ON Resident TO hector AS barbara
barbara is a member of the fixed server role sysadmin and has Delete permission on the Resident table.
I log in to the server as barbara and try and execute this command and get: Msg 15151, Level 16, State 1, Line 1 Cannot find the object 'Resident', because it does not exist or you do not have permission.
If I just run
GRANT Delete ON Resident TO hector
then everything works fine but the grantor is then dbo. I want to be able to have it recorded that barbara was the one that actually gave hector permissino to delete on the Resident table. Is there a way to do this?
We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.
This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.
Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?
I have checked my Production databases, both are went to Disconnected state, both are unable to connect to the application, i checked all the services and its working fine. and restarted the end points on Principal and Mirror, still issue not solved, after restart the witness server End point both the databases are working fine.
I have a .Net application which invokes an stored procedure (SQL Server 2005 Express installed on the same machine). When the stored procedure is called the first time, the application hangs because the sp never ends execution and the application's process has to be killed. But when the application is executed again, the sp runs as expected. What could be happening?
The stored procedure references remote tables by means of synonyms. If the Management Studio is used instead, the sp never ends execution when invoked the first time, but the query can be cancelled.
Now, if the sp is invoked first in the Management Studio first and then by the application, this does not hang (the sp executes as expected).
Background: We have SQL Server 2005 x64 running on a quad-core (dual dual-core) machine with 16GB of RAM. The database is about 10GB in size and we execute around a million stored procedures a day on it. Our application uses about 1000 different stored procedures on this machine. The application is a transactional B2B web-app with about 2000 users. The problem we have is a really odd one that I can't seem to find much information on. We have a small number (3-4) of stored procedures that's exibiting this problem. The stored proc in question takes on average 100ms CPU time to execute. It's a fairly complex stored proc, about 300 lines long, 6-7 select statements and it uses temp tables. No updates / inserts except for on the temp tables. It's executed about 5000 times per day. About once a week, though, execution times will suddenly jump up to 3000 ms average. This happens randomly during the day, although it seems to happen more often on Monday mornings (the DB is mostly unutilized over the weekend) To fix this, I force the DB to recalculate the execution plan by adding / removing (depending what I did last time around) the line 'set arithabort on' at the top of the stored procedure. I have no idea why this works, but it does. Within seconds of changing it, the stored proc execution time will go back to it's normal range of 60-150ms. I've tried setting the execution plan of the stored procedure but I can't get it to work - the execution plan is very long and I don't know how to debug the error I get. What is happening? This happens with a couple of stored procedures - usually the more complex ones. Has anyone seen anything like this?
Dear group,is it possible in SQL-Server to see when a stored procedure wasexecuted ?I would say it is only possible with some traces but not with thestandard settings.For a short answer on that matter i'd be thankful.RegardsUli
I have an application that executes the USE command as it's first order of businees with the database. According to the SQL Books On-line, all USE commands will result in the following message being generated:
However, my application has never reported this message, execept for at one customer site. I cannot figure out why at this one particular site, the customer is seeing this message each time the application starts. Once they ok the message, everything else with the application is fine, but it is a nuisance for them (gotta love customers!).
In reading Has_Perms_By_Name documentation in BOL, it appears that using a three-part name will make no assumption on the database context. However, when I run the code below, I get results that are database context sensitive.
I created a login called 'temp' and added a user for this login in the AdventureWords database. I then added a securable for sys.sysusers for this user in the Adventureworks database. I then selected the "Deny" checkbox for the Select permission. I connected as temp and ran the T-SQL above.
It appears that the database name is being ignored when included in the name of the securable. What am I missing? Is this a bug?
My version: Standard SQL Server 2005 9.0.3054 on Windows 2003 R2 SP2 (3790).
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I have 30+ databases in sql 2000. I want to dynalically loop though each of the databases and alter a column named 'EmpName' for all the 'Employees' tables in that database. Here is my attempt.use Master go declare @SQLString Nvarchar(1000) declare @DBName Nvarchar(100) declare @SQL2 NVARCHAR(1000)declare @TABLE_NAME NVarchar(100) DECLARE curDB CURSOR LOCAL FORWARD_ONLY STATIC FOR SELECT name FROM sysdatabases OPEN curDB FETCH NEXT FROM curDB INTO @dbname While @@FETCH_STATUS=0 Begin SET @SQL2 = 'USE ' + @DBNAME + ' ALTER TABLE ' + @TABLE_NAME + ' MODIFY EmpName varchar(100)' from information_schema.tables where table_type = 'BASE TABLE' and table_name like '%Employees%' PRINT @SQL2 EXECUTE SP_EXECUTESQL @SQL2 fetch next from curDB into @DBName end Close curDB End When i execute the above script in master, it says 'Query executed successfully' but when i see the all the Employees table in each of the database, the required change is not made. Can anybody explain this or give an alternate solution?
I am using Sql Server 2000. I have about 25+ databases . I want to run a series of commands on each database... how can I change the database context - the current database - dynamically in a loop... something like this below:
declare @SQLString Nvarchar(1000)
declare @DBName Nvarchar(100)
declare @SQL2 NVARCHAR(1000)
declare @TABLE_NAME NVarchar(100)
While @@FETCH_STATUS=0
Begin
SET @SQL2 = 'USE ' + @DB_NAME + ';GO;ALTER TABLE ' + @TABLE_NAME + ' MODIFY Name varchar(100)'
PRINT @SQL2
EXECUTE SP_EXECUTESQL @SQL2
End
I am getting an error when i run the above commands: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'GO'
I know sysprocesses has the information, but I can't seem to find how this is done using the dmv's. sessions doesn have a db context, and requests is only active while activity is occurring.
Hi, I have a MSSQL database running in a Microsoft cluster. I was working on troubleshooting for another SAP problem when they suggested I change the setting using hte SQL Server Client Network Utility to 'named pipes' from 'tcp/ip'. Every since, despite changing it back I'm unable to start the database in the cluster.
I'm kind of a newbie at this so I'd really appreciate any help in getting this database up and running.
Here is the error message from the event log: sqsrvres] checkODBCConnectError: sqlstate = HY000; native error = 0; message = [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
I have a login that is mapped to a Windows sysadmin account. I used it to login to Sql Server 2005. I then created a database called Freedom. I then added a Windows login and user called FreedomAdmin, with Freedom set as the default database. When I login in to Windows using FreedomAdmin and then try to login in to Sql Server 2005, I get the following error:
The server principal "FREEDOM1FreedomAdmin" is unable to access the database "master" under the current security context.
If the default database for FreedomAdmin is Freedom (and it is - I checked from my sysadmin login account), why can't I login. Must I give FreedomAdmin permissions to master?
I'm trying to write a procedure that having created a new database,will then create within that new database all the tables andprocedures that go with it.In doing this I'm hitting the problem that you can't issue a USEcommand within a procedure.So my question is either- how do I get around this?- if I can't, how can I create procedures etc in a *different*(i.e. the newly created) databaseor- is there a better way to do all this (*)I have SQL files that do this currently, but I need to edit in thename of the database each time before execution, so I thought aprocedure would be better. Also I'd like eventually to expose someof this functionality via a web interface.Although I'm a newbie, I feel I'm diving in the deep end. Any goodpointers to all the issues involved in this aspect of databasemanagement would be appreciated.(*) One thought that occurs to me is to have a "template" database,and to then somehow copy all procedures, tables, view etc from that.--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
I have deleted a windows login user under security in SQL 2012 Management Studio. The users have also been removed from the associated databases. When I try to add back the login, I receive the message 'The server principal 'xxxx' already exists'. What do I need to do so that I can re-add the login ?
