Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-21 : 09:14:11
|
| Herbert writes "Hi,Attempting to drop and an error message to the sysmessages table.On SQL Server 7Though logged in as a User, called this for now as "XYZ" (not SA), I was able to execute the sp_addmessage and sp_dropmessage succesfully.Having checked the Help file, I was rather amused by the fact that only sysadmin role have the permission!On SQL Server 2000With the same database and similar server setup as SQL 7, I tried to run this same stored procedure against the database but it has bombed me out with message "Server: Msg 15247, Level 16, State 1, Procedure sp_dropmessage, Line 11User does not have permission to perform this action."Question 1Why did the server allow me to run the sp_admessage & _dropmessage logged in as a "XYZ" user instead of "SA"?Question 2Is there something introduced in SQL2000 that has such restriction not available on SQL7?Hope to hear from you soon.Thanks, Herbie" |
|
|
Jay99
468 Posts |
Posted - 2002-03-21 : 09:55:40
|
quote:
PermissionsExecute permission defaults to the public role; however, only members of the sysadmin fixed server role can add messages with a severity level higher than 18 or with with_log set to true.-SQL 7 BOL
Assuming you are not being tripped up by the above . . .SA is a login.sysadmin is a roll that can be assigned to any login.XYZ on your SQL 7 box is a login that has the 'System Administrator' roll.XYZ on your SQL 2k box is a login that does not have the 'System Administrator' roll.Jay<O> |
 |
|
|
|
|
|
SQL Server 2000 Login Roles & permissions