 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Missing "Public" SQL Server 2005 role - but everything works fine.
Gurus, Running SQL Server 2005 SP2 default instance on top of Windows Server 2003 SP2. Everything is working fine however I noticed that under server roles, the roles are all there EXCEPT for "Public". Really strange. Anyone ever seen that before? I am the only one with access to the server and I know I didn't delete it. I guess things are working fine b/c my one application which communicates with a certain database on the server uses the credentials of an account which has the sysadmin role. I guess I'd be out of luck if I had to provision anything with just the Public role. Any thoughts?  SignatureSpin Hi You can not drop the public role in a database you will get Msg 15150, Level 16, State 1, Line 1 Cannot drop the role 'public'. What does exec sp_helprole 'public' return? John > Gurus, > [quoted text clipped - 7 lines] > out of luck if I had to provision anything with just the Public role. Any > thoughts? The results of the exec sp_helprole 'public' are: 1 single row with three columns in it. RoleName with value public, RoleID with value 0 and IsAppRole with value 0. > Hi > [quoted text clipped - 18 lines] >> guess I'd be out of luck if I had to provision anything with just the >> Public role. Any thoughts? I think you are confusing the public database role with the public server role. John's reply was about the database rile, where your initial question seemed to be about the server role. Exposing the public server role was a change in some service pack for 2005. I'm not sure how much of this is client side (Management Studio) or server side. Or possibly both. But I wouldn't be surprised if a down-level SSMS won't show the public roe regardless of what you connect to. And perhaps that a recent SSMS (sp2) only show the public roe if you connect to a sp2 server... SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > The results of the exec sp_helprole 'public' are: > [quoted text clipped - 21 lines] >>> which has the sysadmin role. I guess I'd be out of luck if I had to provision anything with >>> just the Public role. Any thoughts? Interesting response. Yes, my initial question was about the Public server role missing. There are ten roles and that is the only one I am missing. I am running SP2. What is SSMS? >I think you are confusing the public database role with the public server >role. John's reply was about the database rile, where your initial question [quoted text clipped - 34 lines] >>>> sysadmin role. I guess I'd be out of luck if I had to provision >>>> anything with just the Public role. Any thoughts? SSMS is SQL Server Management Studio. You have two parts involved here, and either., one, the other or both can be service packed: the database engine and the client tool (SQL Server Management Studio). But guess is that this (magical combination) holds the answer. SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > Interesting response. Yes, my initial question was about the Public server role missing. There > are ten roles and that is the only one I am missing. I am running SP2. What is SSMS? [quoted text clipped - 32 lines] >>>>> which has the sysadmin role. I guess I'd be out of luck if I had to provision anything with >>>>> just the Public role. Any thoughts? Ah yes. SQL Server 2005 is installed on a single box. Includes the DB server components and Management Studio. All in one. > SSMS is SQL Server Management Studio. You have two parts involved here, > and either., one, the other or both can be service packed: the database [quoted text clipped - 43 lines] >>>>>> the sysadmin role. I guess I'd be out of luck if I had to provision >>>>>> anything with just the Public role. Any thoughts? So you are saying that if you connect with Management Studio sp2 (Microsoft SQL Server Management Studio 9.00.3042.00) to the database engine which is also sp2 (9.00.3054.00), and expand the ServerRoles folder that you don't see a "public" entry? If so, I don't know that it might be, I'm afraid... SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > Ah yes. SQL Server 2005 is installed on a single box. Includes the DB server components and > Management Studio. All in one. [quoted text clipped - 40 lines] >>>>>>> an account which has the sysadmin role. I guess I'd be out of luck if I had to provision >>>>>>> anything with just the Public role. Any thoughts? If I go into the User Mapping field of any of my existing users, or even create a new user, the checkbox for 'Public' to specific databases is available! > So you are saying that if you connect with Management Studio sp2 > (Microsoft SQL Server Management Studio 9.00.3042.00) to the database [quoted text clipped - 53 lines] >>>>>>>> I had to provision anything with just the Public role. Any >>>>>>>> thoughts? I'm not sure what your point is here. A user in not the same thing as a login, and a database role is not the same thing as a server role. The public *database role* (which is what you refer to below) exists in all databases, this has been the same way since 1.0. The public *server role* is something different. I thought that this is what this thread was about? SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > If I go into the User Mapping field of any of my existing users, or even create a new user, the > checkbox for 'Public' to specific databases is available! [quoted text clipped - 48 lines] >>>>>>>>> the credentials of an account which has the sysadmin role. I guess I'd be out of luck if >>>>>>>>> I had to provision anything with just the Public role. Any thoughts? My bad! exec sp_helpsrvrole 'public' /* Msg 15412, Level 11, State 1, Procedure sp_helpsrvrole, Line 10 'public' is not a known fixed role. */ even when it shows up in SSMS! Looking at the code this relies on an entry in spt_values which for 'public' isn't there, but SSMS looks at sys.server_principals to get it's list of roles. John >I think you are confusing the public database role with the public server >role. John's reply was about the database rile, where your initial question [quoted text clipped - 34 lines] >>>> sysadmin role. I guess I'd be out of luck if I had to provision >>>> anything with just the Public role. Any thoughts? John, what did you mean by all of this (I quoted you) below? Is there something you want me to try? "exec sp_helpsrvrole 'public' /* Msg 15412, Level 11, State 1, Procedure sp_helpsrvrole, Line 10 'public' is not a known fixed role. */ even when it shows up in SSMS! Looking at the code this relies on an entry in spt_values which for 'public' isn't there, but SSMS looks at sys.server_principals to get it's list of roles." > My bad! > [quoted text clipped - 51 lines] >>>>> the sysadmin role. I guess I'd be out of luck if I had to provision >>>>> anything with just the Public role. Any thoughts? Hi If you look at sys.server_principals you should see a public server role. John > John, what did you mean by all of this (I quoted you) below? Is there > something you want me to try? [quoted text clipped - 67 lines] >>>>>> the sysadmin role. I guess I'd be out of luck if I had to provision >>>>>> anything with just the Public role. Any thoughts? Hi If you get the same results if you looked at the tables/ran the procedure then it your system is "normal"!! When you look at the user mappings this has database roles in the bottom half of the screen and all database users will be members of the public database role. John > John, what did you mean by all of this (I quoted you) below? Is there > something you want me to try? [quoted text clipped - 67 lines] >>>>>> the sysadmin role. I guess I'd be out of luck if I had to provision >>>>>> anything with just the Public role. Any thoughts? Hello, It seems the Public role is working fine inside, but other things are going wrong. The Public role appears under Server Roles. However, the problem is that when I double click the Public icon (under Server Roles), the Server Role Properties window should list all the logins that are members of the Public role. However, none of these logins are shown. In addition, all of the stored procedures and functions that I have tried do not work correctly for all logins. For example: -- Doesn't work: sp_srvrolepermission 'public' -- works for the current login, but not for other logins: SELECT is_srvrolemember('public') -- Doesn't work: SELECT is_srvrolemember('public', 'mary') -- Although the Is_SrvRoleMember function returned 0, meaning that Mary is not a member of the Public role, I logged in using the Mary login and succeeded even though Mary doesn't have any user in the database. So, it seems that the Public role member is working properly internally but the problem is with the stored procedures and functions that deal with the server roles. And the second problem is with the SSMS because it doesn't show the members of the Public role. Michel |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.