SQL Security :: How To Disable User Access To The Critical And System Stored Procedures
Sep 22, 2015disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?
View 5 Replies
ADVERTISEMENT
All My Stored Procedures Are Getting Created As System Procedures!
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
SQL Security :: How To Stop System Database Access
Jun 2, 2015We created one SA login [login name: test_db] and give the Db_owner permission for particular user db, but this test_db login able to access the all system database except model database.Here problem is I am giving the only one particular user database but that login why able to access the system database.Here how to stop the system database access…
View 8 Replies View RelatedSQL Security :: Create Database User And Give Grants To Few System Tables
Aug 12, 2015Need to create a user in sql server provide grants to few system tables to the above user.
View 10 Replies View RelatedHow To Save Stored Procedure To NON System Stored Procedures - Or My Database
May 13, 2008Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
Why User Defined Procedure Shows Under System Procedures In Object Explorer
Jan 17, 2008I have created a store procedure in sql server 2005.
At first, it shows up as a normal user defined store procedure in Object explorer.
Recently, I found I could not found the procedure in normal place. But the execution is fine.
Finally I found it stays under system procedure folder.
How this could happen?
Security Stored Procedures
Feb 26, 2004Hello, everyone:
How to security the stored procedures? I want to prevent the stored procedures to be changed accidently. Thanks.
ZYT
Stored Procedures/security
Jul 23, 2005First off I am a rookie at Sql Server. Ok let's give this a try. Mycompany has bought a new software package called Viewpoint. It's OnSql Server and written in VP. We do not have access to the code.There is a option in the software package called "ApplicationSecurity". When this is clicked you are unable to access the ViewpointDatabase from an outside application. Since I can't get to the code Ihave no idea how this feature works.I would like to keep the "Application Securty" ON so no one can accessthe Viewpoint Database from an outside application but I would like towrite my own outside application where I can access the Viewpointapplication tables while the Application Security in ON. Does anyoneknow what I am talking about? I know it is not very clear and I amworking with limited information, but any help would be appreciated.Thanks
View 1 Replies View RelatedStored Procedure Being Saved In System Stored Procedures
Apr 7, 2006We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
Stored Procedures, Security, Xp_cmdshell
Sep 27, 2000To try to secure an outside web application we set up a user that
only has permission to execute a series of stored procedures that are
related to the appliation. Unfortunately a couple of those stored
procedures have to access system resources outside SQL Server so we
are using a call to xp_cmdshell from inside the stored procedure
SQL Server apparently won't let us do that unless we give our
restricted user (who is calling the initial stored procedure) execute
permission on xp_cmdshell. This, of course, negates most of the benefit
of setting up a restricted user. Is there some simple way I am missing
of running xp_cmdshell from inside s stored procedure without the user
calling the stored procedure having execute permission on xp_cmdshell?
How To Provide Security To Stored Procedures
Jul 20, 2005Hi all,I know that it is possible to encrypt Stored Procedures using 'withencyption'.But my problem is that when there are so many decriptingmethods available how far will the encyption be secure.Is there any other method to encrypt the stored procedures that areresiding on the customer sites.We do not want the customers to meddle with the SPs.If anyone knows can u please let me know.ThanksDilini
View 1 Replies View RelatedSecurity, Dynamic SQL, And CLR Stored Procedures
Aug 1, 2006Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. I'm suspect there's a simple answer, but I'm unaware of it. Basically, here's the scenario...
I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.
The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.
Basically, the connection context is impersonating a low-privilaged Windows account ("UserX") coming from a .NET application. UserX has no permission to the table referenced in the dynamic SQL and because of the dyanmic nature of the query, the stored procedure apparently adopts the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.
So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?
I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may use the same connection, and be as secure, but simpler.
SQL Server 7.0 System Stored Procedures
May 1, 2001About a year ago we inherited a SQL server (7.0) from another division of our company. The time has come to migrate the SQL Server functionality onto a new machine. Before I do this though, I need to figure out what the initial installation configuration was so I can set up the new server with the same specs. I am looking for a system stored procedure(s) that can tell me the following:
Case sensitivity, accent sensitivity, SQL build (SP), character set, etc. Basically I need to know what checkboxes were ticked during the initial set up of the server.
Any help would be greatly appreciated. TIA.
Model Db Got No System Stored Procedures
Nov 7, 2001So every database i created has no system stored procedures. I didn't know how that could happen as I am not the person who installed sql2000. What should I do to get all the system stored procedures back?
TIA
Modify System Stored Procedures
Oct 31, 2002I am trying to edit a system stored(sp_add_dtspackage) procedure and cannot for the life of me find where to edit. This procedure does a check to keep users from saving changes to a package they do not own. I need users to be able to change the the packages when they are not always the one who created.
View 2 Replies View RelatedMissing System Stored Procedures
Aug 28, 2002I have a SQL Server installation that is missing the stored procedures used for mail (xp_startmail, xp_stopmail, etc.). Is there a way that I can put these on the server without having to reinstall SQL? I tried to generate them from other servers but SQL won't allow you to do this with these particular stored procedures.
View 1 Replies View RelatedMissing System Stored Procedures
Feb 25, 2005When I create a new database, the system stored procedures are missing;
dt_addtosourcecontrol_u
dt_checkinobject_u
etc.
I'm logged in as sa.
Stored Procedures Getting Created As System
Nov 8, 2007I need to create a stored procedure in the master database (yes, I know it's not that good of an idea). I'm working with SQL 2K5, SP2 Whenever I create it, it is marked as a system stored procedure no matter what I name it, what schema I put it in, or what user I use to create it (sysadmin or minimal permissions).
As soon as I create it, if I do any of the following, I can see it to be a system stored procedure and not a regular user sp.
1) SELECT * FROM sys.objects where is_ms_shipped = 1
2) SELECT * FROM sys.procedures where is_ms_shipped = 1
3) Looking in SSMS... There is a special folder for system stored procedures in SSMS, and mine is in there.
At least in my case, the only thing it hurts is that you have to be a sysadmin to execute that stored procedure (and I need to have a non sysadmin be able to execute it). Other than that, it executes normally when run by a sysadmin.
Any suggestions on why this is happening? It's only happening on 1 out of about 80 SQL servers we have.
System Stored Procedures Do Not Execute
Oct 23, 2006I tried this morning to check some of the system stored procedures and ran into trouble. Only four of them executed: sp_alterdiagram, sp_creatediagram, sp_dropdiagram and sp_helpdiagramdefinition. I could not check all the rest, I did it selectively. The typical error message was: Invalid object on RETURN statement. Some had syntactical errors.
sp_ActiveDirectory_Obj
Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171
sp_ActiveDirectory_SCP
Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171
sp_ActiveDirectory_Start
Invalid object name 'sys.sp_ActiveDirectory_Start' Line 19
Invalid object name 'sys.sp_add_agent_parameter' Line 60
Invalid object name 'sys.sp_add_agent_profile' Line 123
Invalid object name 'sys.sp_add_data_file_recover_suspect_db' Line 17
sp_addalias
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 44
Incorrect syntax near '%'.
Msg 195, Level 15, State 10, Procedure sp_addalias, Line 64
'get_sid' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 78
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 119
Incorrect syntax near '%'.
sp_bindefault
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 95
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 134
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 182
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 208
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 228
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 264
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 273
Incorrect syntax near '%'.
sp_databases
Invalid object name 'sys.sp_databases'. Line 6
sp_tables
Invalid object name 'sys.sp_tables'
Is there a chance that these errors are due to the fact that I executed them without parameters?
Thanks
Undocumented System Stored Procedures
May 15, 2007I know there are a lot of undocumented system stored procedures such as xp_ntsec_enumdomains, xp_instance_regread etc, that exist on SQL Server.
Does anyone know of any good websites that contain descriptions of what these stored procedures do? In particular I know that a default installation of SQL Server 2000 leaves permission to execute many of these granted to public and I am interested in finding out what the implications of each one of these are? I have tried Googling this topic but there doesn't seem to me much or there (or what is there is in Chinese and I don't really want to click on to!)
Once again thanks for the help.
Disabling Extended Stored Procedures For Security
Jun 29, 2007Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?
View 5 Replies View RelatedAbout Default Created System Stored Procedures
May 12, 2008i found that some stored procedure get created automatically in sqlserver 2000 (system stored procedures) ,while doing my work i accidentally deleted those stored procedures can any body answer following questions1: why these stored procedures are there and automatically created2: what happen en if i deleted those stored procedures3: how to recreate those stored procedures with limited user permission thanks in advance
View 2 Replies View RelatedSystem Stored Procedures/behavior Not Understood
May 11, 2004I recall that stored procedures created in the master database and having a name beginning with sp_ are available from all databases. However, I've found varying results when invoking such a procedure from a different database. For example:
DECLARE tables CURSOR FOR
select name from sysobjects
where type='U'
order by name
for read only
populates a cursor with the list of tables in the database from which you are running the procedure. However this query in the same stored procedure:
SELECT count(*)
FROM sysExcludeMaint
WHERE @tablename like tablename
fails with "Invalid object name 'sysExcludeMaint'." if the table sysExcludeMaint
doesn't exist in the master database (it does exist in the database from which I've invoked the proc). I'm not clear on why the different results. Anyone know?
Recreating/reloading System Stored Procedures
Jul 21, 2006A system stored procedure got accidentally deleted, and all backups aresince the stored procedure was deleted (wonderful!)Can the SQL for the stored procedure be extracted from another serverand loaded as opposed to removing everything and then rebuilding theserver?Thanks in advance!
View 1 Replies View RelatedExport/import A Non System Stored Procedures From One DB To Another
Aug 23, 2006Hello, I want to export/import a non system stored procedures from one DB to another. How Should I proceed ?
Thanks in advance.
Controlling Security Through Stored Procedures -- 2005 Behaviour
Aug 23, 2005Hi!
View 4 Replies View RelatedLogging With Another System User Cannot Access My Database
Mar 3, 2008Hi,
I am using SQL Server Express Edition 2005 as a backend database working with Visual Basic 2005.
I am using Vista and having two users to access to my computer. User-1 and User-2.
I created a database in User-1 and works fine Visual Basic 2005.
Now the problem is when i login to my computer system with User-2. I cannot able to access the database with encountering error like "User-1/SQLExpress". I know that i cannot able to access to database which was created in User-1.
Do you any solution to this problem. when i login with user-1 and user-2 it should able to access database.
Thanks.
Regards
Kashif Chotu
SQL Security :: How To Grant Two Tables (select Only) Access To New User
May 5, 2015i want to create new sql user and grant him two tables access. we have several databases created on same server so we want to allow only two table in ABC database. user should not be able to see other databases and their tables. And user also should not be able to access any other tables in ABC database except two tables.
is there any query to deny all tables in schema for all clauses (Select, Update, Insert) then grant two tables to user with select clause?
SQL Security :: Giving Access To A User Using DOS-Batch Script
Oct 20, 2015Is it possible for a batch command to give access or add a new role for the user to database? Basically:
USE my_Database
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=
[dbo];
EXEC sp_addrolemember 'Application1', 'User1'
EXEC sp_addrolemember 'SQL_Read_Only', 'User1'
If User1 was already created then ignore that and run the exec sp_addrolemember. Next,
EXEC sys.sp_change_users_login
@Action = 'Update_One',
@UserNamePattern = 'User1',
@LoginName = 'User1',
@Password = NULL
to link the orphan user within database.
How To Implement The Replication With The System Stored Procedures Or TransactSQL?
Jun 8, 2004how to implement the replication with the System Stored Procedures or the Transact-SQL ?
i want to encapsula my replication and make it be reusable and ease to use.So i want to do the replication in a script file.how can i do this?
Thanks.
Replication System Stored Procedures Parameter Defaults ?
Oct 27, 2005Hi there
View 5 Replies View RelatedWhat System Stored Procedures Have Changed Between SQL 2000 And SQL 2005?
Aug 2, 2006What system stored procedures have changed between SQL 2000 and SQL 2005?
View 4 Replies View RelatedMerge Agent System Stored Procedures And Sp_Recompile
Jul 13, 2006Hello,
We are trying to be proactive and stop a potential performance issue by reducing the number of recompiles in our SQL 2000 database application. This database is replicated. After viewing output from Profiler and PerfMon it seems that over 90% of the recompiles are due to system stored procedures generated by replication merge agents. Can anything be done about this?
Thanks