How To Assign Execute Permission To Batch Of Stored Procs To A User/role
Mar 25, 2008
Just wondering if anyone knows of a useful command to assign execute permissions to a batch of stored procs to a user/role. I've got too many stored procs to manually go thru the steps of browsing for them and scrolling thru each one and clicking "execute" for each one.
Also, would like to know if its possible to update a batch of stored procs that begin with a prefix like "spSomething_".
I get the following errors associated with trying to create an SP.
Server: Msg 170, Level 15, State 1, Procedure AddFortuneUser, Line 8 Line 8: Incorrect syntax near '@newuser'. Server: Msg 137, Level 15, State 2, Line 1 Must declare the variable '@newuser'.
Can anyone explain why I have to do a declare. I suspect I have to issue "declare @newuser sysname" somewhere but I'm not sure why.
The following is the code I'm trying to run. My intent would be to create a form for the Admin Clerk that would call this SP. That way they can create a generic login. They have an application that allows them to change the password after the fact. /* Created for Admin person to allow them to add a basic SQL Login Account forcing the user to be a member of a specific role 'helmsman' in a specific database 'Fortune' */ CREATE PROCEDURE AddFortuneUser @newuser char(128) AS EXEC master..sp_addlogin @loginame=@newuser, @passwd =substring(@newuser,1,8), @defdb =Fortune GO if not exists (select * from dbo.sysusers where name = @newuser and uid < 16382) EXEC sp_grantdbaccess @loginame=@newuser, @name_in_db=@newuser GO exec master..sp_addrolemember @rolename ='helmsman', @membername =@newuser GO
Hello All,I tried to set the access permissions for debugging stored procedure by reading the articlehttp://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspxandhttp://technet.microsoft.com/en-us/library/ms164014.aspxI have tried to add the role to sysaminas follows1)SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_sdidebug'(to find the sp)Error:--The stored procedure not found2)sp_addsrvrolemember 'Developmentswati.jain', 'sysadmin' though this is executed successfuly . Error is still persisting Cannot debug stored procedures because the SQL Server database is not setup correctly or user does not have permission to execute master.sp_sdidebug.
in SQL server 2005, Database User's permission will be overriden by the database Role's permission or ottherwise? For example, a userA is owner of table AA so it has all permisions on table AA but the user is a member of GroupB but group B has no permission to access to Table AA. What happen on User A?. has it permission to access to table BB or not? How can I find document or example about this? Please help me, thanks so much
Is there a better way of granting permission to a stored proc for a selected user other than (enterprise manager) select sp then accessing propertys then permissions, then user?
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
I have a stored procedure that generates some data and dumps it into a table. I need to export data using bcp based on the data that this procedure creates. So I know how to use bcp but don't know how to execute the procedure and pass it the two variables that it needs. I googled it and sqlcmd looks promising but can get the syntax right. The two variables are the current year and school number ie. 1112, 0021.
we've got a Windows Server 2003 environment with SQL Server 2000 Sp 3.
A stored procedure selects specific data from a user-table which depend on the user executing it. The users are granted execute permission on the stored procedure. But execution fails, if the user is not granted select permission on the user-table, too.
The problem is, that the user must not have the permission on all data in the user-table but on the data concerning him.
In earlier versions of SQL Server and Windows the execute permission has granted sufficient rights to select from the underlying tables. How can this be re-established?
Which fixed database role allows a user to execute a user defined stored procedure while minimizing the amount of permissions given. I think db_Datareader will do the trick.
I have create a batch file to execute a stored proc to import data.
When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?
I'm trying to get a stored procedure working for a website on my local machine that uses ASP.NET 1.1 and MSDE. (I have a single instance of the latter installed, using Windows Authentication mode.)
I've been able to run SQL queries and such directly (using SqlCommand and so forth) by adding the proper reader role to the account MACHINENAMEASPNET. (Substituting my actual machine name for MACHINENAME, of course.) However, when I try to run a stored procedure from an .aspx page, I get the following error:
I've researched this problem here and other places, and every time I get to a response that says to grant execute permission (via OSQL -E) with the following statements:
use mydbasename go grant execute on MySPName to MACHINENAMEASPNET go
(There are sometimes some other intervening statements to add ASPNET as a user account, but when I use those I'm told that the account already exists ... I had added it previously via the Web Data Administrator in order to get reader permissions for SELECT statements and so forth.)
My problem is that the GRANT EXECUTE statement always fails with the following error:
Line 1: Incorrect syntax near ''
Using a forward slash instead doesn't make any difference. If I put single quotes around 'MACHINENAMEASPNET', then the error changes to:
Line 1: Incorrect syntax near 'MACHINENAMEASPNET'
And if I eliminate the machine name, then the error is:
Msg 4604, Level 16, State 1, Server MACHINENAME, Line 1 There is no such user or group 'ASPNET'
So can someone please let me know what I am missing that doesn't allow the GRANT EXECUTE to work?
Here is the stack trace (note that I have altered some names and paths for purposes of security):
Hi I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2. For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure. In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user. Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this. If anyone can point me in the right direction on this... I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005? Thanking you in advance
Suddenly a stored procedure, very much like several others, is givingEXECUTE permission denied on object 'Add_Adjustment', database'InStab', owner 'zhoskin'.server:Msg 229, Level 14, State 5, Procedure Add_Adjustment, Line 18.I'm zhoskin. I am the dbo and created the procedure, and when I lookat its properties, I have EXEC permission. Line 18 is just the returnstatement. The values are all appropriate for the table. So what isusually going on when a stored procedure denies access to its owner?Thanks//Zeke HoskinCREATE Procedure Add_Adjustment (@AdjAcc Int, @AdjType Char, @AdjAmtMoney, @AdjYrMth Int, @AdjDate Datetime)/* Add a Dep Adj (Type Z, Negative) or WD Adj (type Y, Positive) */ASIF @AdjType = 'Z'BEGINInsert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,TxnDate)VALUES(@AdjAcc, @AdjType, @AdjAmt, -1, @AdjYrMth, @AdjDate)ENDIF @AdjType = 'Y'BEGINInsert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,TxnDate)VALUES(@AdjAcc, @AdjType, @AdjAmt, 1, @AdjYrMth, @AdjDate)/*this is just to afect line numbers*/END/* set nocount on *//*space holder*/return/*more space*/GO
In a SQL 2k instance (latest SP) some of my user databases show the public role with execute on a variety of stored procs named dt_* (i.e. dt_addtosourcecontrol). However, not all the user databases do this, some do not grant the public role execute on these procs .
So, can someone explain what generates these permissions and is it acceptable to remove them? If I have a database that does not grant public access, should I be concerned? I don't see any reference to these procs in BOL.
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
I have an application that uses Integrated Windows authentication. My Web.config looks like below <add key="dbconnection" value=" server=XXX;Initial Catalog=XXX;persist security info=False;Integrated Security=SSPI;Pooling=true" /> When users try to access my application, they get the below error: Execute permission denied on object 'SprocName', database 'DBNAME',Owner,'dbo' The Only way I could get rid off the error is if I set DBO permissions for the user group on the databse. Can someone suggest how to set up a security group with the βnecessaryβ permissions on SQL SERVER (ie read,write execute Sproc etc) and not too many extra ones, like DBO. Thanks,
I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?
Hi, Is there a way to script out a database role from SQL management studio? I can only get a script for create or drop. I am looking for a script that shows all object permissions that the role has in a database.
I have ddl triggers in place to watch what people do to our various database environments. I can see when someone does something to a login, but I can't tell what was done. I have a sneaky someone creating accounts with sysadmin privs and I want to catch the source. I also want to know when someone changes a password on a sql account. Does anyone know of a way to do this?
I need some clarification regarding the security inside of the master db.
I have a user stored procedure in master. I would like to be able to execute that stored procedure from an internal web app. Would I give execute permission on the stored procedure to the "public" or "guest" role? The web app would be using a userid/pw for an application database.
Also, is it a good idea to have user stored procedures inside of master? Could someone point me to where I can find a good article on master db best practices?
We are using a third party tool that does not store passwords in an encrypted format therefore we created a user with minimal rights. Isn't there a way to grant "execute any stored procedure" to a user/Login? Do we really have to grant execute on each procedure to the user? And then do the same for each new store procedure? The only other option we have found to be able to "see" and execute the stored procedures is by granting "db_owner". I would think that would negate the user being minimal rights.
BEGIN TRANSACTION @Tran1 ¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA] (, ,) SELECT ,, FROM [tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [tmptabloA]
COMMIT TRANSACTION @Tran1
When user [nuran] execute the procedure sp_yordam by a VB program, the procedure use [dbo].tmptabloA not [nuran].[tmptaboA]. If there are data in the [dbo].tmptabloA, the procedure insert data to [dbo].tabloA from [dbo].tmptabloA. But when I checked user name in the procedure during execution, the user was [nuran].
If I write the procedure like that:
(2) create PROCEDURE [dbo].[SP_tmpSil] AS
declare @tablo1 as varchar(50), DECLARE @sil as nvarchar(max) select @tablo1='[tmptabloA]'
And it executed by user [nuran],then it used the correct table [nuran].tmptabloA
Is there any way to use users table in an stored procedure without using the user name : (3) create PROCEDURE [dbo].[SP_yordam] AS BEGIN
BEGIN TRANSACTION @Tran1 ¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA] (, ,) SELECT ,, FROM [nuran].[tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [nuran].[tmptabloA]
COMMIT TRANSACTION @Tran1
I don't want to use (2) and (3) code methods, I prefer to use (1) script. Is there any compilation method, or any aditional way for using script (1) with correct user rights?
CREATE PROCEDURE PROC1 AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10) ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (23,12,10) ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1' else '0' end) + MAX(case when A.intpreferenceid = 12 then '1' else '0' end) + MAX(case when A.intpreferenceid = 10 then '1' else '0' end) AS PREFER FROM CUSTOMER GROUP BY A.INTCUSTOMERID ORDER BY A.INTCUSTOMERID END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
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...
I have a group of users that I have given db_datareader permissions to in an SQL Server 2000 database. I am also creating web pages on an intranet site that pulls data from the table. If I just use a select statement to pull the data from the table, the users dont have a problem. If I use a stored procedure with the exact same sql statement, the users get an error until I grant them execute permissions on the stored procedure.
I have heard that store procedures is the best way to handle data operations but having to make sure I assign execute permissions every time I create a stored procedures can be a pain. The only way I know of to make sure that they had permissions would be to make them a member of db_Owner which is definitely not an option.
Is this just the way it is, or is there some way to automatically grant them execute permissions on stored procedures that are nothing more than select statements and dont violate db_datareader permissions?
Hi :Can i call 2 procs within one task?I have sp_proc1 ? (and have declared one global variable as inputparameter)now i have another sp_proc2 which uses same input parameterbut if i write two statements like this within one task, i get anerrorexec sp_proc1 ?exec sp_proc2 ?I can solve the problem by writing them in 2 separate tasks, but wouldlike one task.Please help..thanksRashika
I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master] GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/ SET ANSI_NULLS ON GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
