tags:

views:

123

answers:

1
+2  Q: 

schema sys/dbo - extended stored procedure rights

I know that sys schema has execute access on all extended stored procedures. If my sys admin logins are having schema - dbo, what is "sys" schema used for?

Or can I deduce that sys admins have execute permissions on all extended stored procedures? How can I prove it with tsql?

+1  A: 

sys' is magic. Is recognized in code as special and it looks up its contained objects in the special resource database:

SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

The ordinary ownership and permission rules don't apply to sys.

Sysadmins don't need any permission, they have all privileges and cannot be denied nor revoked anything.

Remus Rusanu  2009-07-22 23:32:13
So, do they have execute permission on extended stored procedures? how can i check it?
Manjot  2009-07-22 23:43:54
Check as in 'test it' or check as in 'runtime check in code'?
Remus Rusanu  2009-07-23 00:01:03
check as in some tsql command to say"please show me who has execute access to extended stored procedures"Will that be possible?I really didnt understand your description for sys schema. Is it used by SQL server itself or users too?
Manjot  2009-07-23 00:12:56
I got my answer for sys schema btw
Manjot  2009-07-23 00:32:47
I already answered "who has access to ..." in http://stackoverflow.com/questions/1156812/object-permissions-tsql/1156889#1156889. Your question here was about sysadmin role and the answer to that is: sysadmins have access to everything by definition. Which is also already answered on the other post question.
Remus Rusanu  2009-07-23 01:31:28
Thanks very much
Manjot  2009-07-23 02:54:14

related questions

*= in Sybase SQL
sql missing rows when grouped by DAY, MONTH, YEAR
Adapt Replace all strings in all tables to work with text
Can you call a webservice from TSQL code?
How to tell the data types after executing a stored procedure?
C# - SQLClient - Simplest INSERT
SQL Server: Get data for only the past year
Get last item in a table - SQL
Is there a way to make a TSQL variable constant?
Create a database from another database?
SQL Server PIVOT examples?
What are some references, lessons and or best practices for SQL optimization training.
What's a good way to check if two datetimes are on the same calendar day in TSQL?
Trigger without a transaction?
How do I do an Upsert Into Table?
What is the easiest way using T-SQL / MS-SQL to append a string to existing table cells?
How do I avoid using cursors in Sybase (T-SQL)?
Best .NET Solution for Frequently Changed Database
T-Sql date format for seconds since last epoch / formatting for sqlite input
HOWTO - Compare a date string to datetime in SQL Server?
Differences Between MySql and MS SQL
Best method for varchar date validation in Sybase (T-SQL)?
interrogating table lock schemes in T-SQL
Testing for inequality in T-SQL
T-Sql Remove Decimal Point From Money Data Type