Now that the holidays are over and the last college football bowl game has been played, I thought I would get back to blogging. I have been looking over CTP5 quite a bit lately while trying to rewrite my SQL Server DBA Bootcamp to include SQL Server 2008 topics. One of the items I have run across is the new Public fixed server role.

This new fixed server role shows up in SSMS has not made it into all of the Books Online articles yet. It shows up in a few articles that I will list below but not as a member of the server role tables, just as a comment in the remarks section. The system stored procedures which list information about the fixed server roles do not recognize this role yet although the IS_SRVROLEMEMBER function does. (I putting this down to limitations in CTP5).

–Does it show up: No

EXEC sp_helpsrvrole

–Does it show up: No

EXEC sp_helpsrvrolemember

–How about this: YES

SELECT IS_SRVROLEMEMBER (‘public’)

–Cryptic message that this may be depreciated

–Does not work correctly in SQL Server 2005

EXEC sp_srvrolepermission

So if the supplied system stored procedures do not recognize the new role yet, I guess we have to use the catalog views to get information on this role.

–In the catalog view: YES

SELECT * FROM

sys.server_principals

WHERE type = ‘R’

–Any members: No

SELECT sp1.name AS ‘Login Name’

,sp2.name AS ‘Server Role Name’

FROM sys.server_principals sp1

INNER JOIN sys.server_role_members srm

ON sp1.principal_id = srm.member_principal_id

INNER JOIN sys.server_principals sp2

ON sp2.principal_id = srm.role_principal_id

WHERE sp1.name = ‘public’

But what about one of the Books Online articles I found that talked about this new role?

 

“Every SQL Server login belongs to the public server role. When a server principal

has not been granted or denied specific permissions on a securable object, the user

inherits the permissions granted to public on that object.”

 

Books Online Article: Server-Level Roles

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/7adf2ad7-015d-4cbe-9e29-abaefd779008.htm

 

This article seems to state that all SQL Server logins belong to this role but the catalog view did not return any members.

The next step I did in my research was to create a new SQL Server login:

–Lets add a new SQL Login to check

CREATE LOGIN testpublic WITH PASSWORD = ‘testpublicrole’

,CHECK_EXPIRATION = OFF

,CHECK_POLICY = OFF

GO

–Successful?

SELECT * FROM sys.server_principals WHERE name = ‘testpublic’

–Is this new login a member of the public role? NO

SELECT sp1.name AS ‘Login Name’

,sp2.name AS ‘Server Role Name’

FROM sys.server_principals sp1

INNER JOIN sys.server_role_members srm

ON sp1.principal_id = srm.member_principal_id

INNER JOIN sys.server_principals sp2

ON sp2.principal_id = srm.role_principal_id

WHERE sp1.name = ‘public’

If you look in SSMS for this new login’s properties, you will find that it lists itself as a member of the public role, but if you look under the properties for the public role it does not include any members (limitations of CTP5 again).  Rerunning the IS_ SRVROLEMEMBER function also shows up the new login as a member.

–Make login a user in the master database

USE master

go

CREATE USER testpublic FROM LOGIN testpublic

GO

–Switch execution content

EXECUTE AS LOGIN = ‘testpublic’

GO

–Who are we?

SELECT USER_NAME()

GO

–How about this: YES

SELECT IS_SRVROLEMEMBER (‘public’)

–Go back to sysadmin (dbo)

REVERT

–Who are we?

SELECT USER_NAME()

GO

That seems to work even if the SSMS properties page doesn’t yet. So what permissions does this new server role have? Given that the system stored procedures do not work, we must go back to the catalog views.

–Okay what about permissions for the public role

SELECT

spr.name

,sp.permission_name

,sp.class_desc

,‘Server’ AS ‘Securable’

,sp.state_desc

FROM sys.server_permissions sp

INNER JOIN sys.server_principals spr

ON sp.grantee_principal_id = spr.principal_id

WHERE class = 100

AND sp.grantee_principal_id = ‘2’ –ID of public role

UNION

SELECT

spr.name

,sp.permission_name

,sp.class_desc

,spr2.name AS ‘Securable’

,sp.state_desc

FROM sys.server_permissions sp

INNER JOIN sys.server_principals spr

ON sp.grantee_principal_id = spr.principal_id

INNER JOIN sys.server_principals spr2

ON sp.major_id = spr2.principal_id

WHERE class = 101

AND sp.grantee_principal_id = ‘2’ –ID of public role

UNION

SELECT

spr.name

,sp.permission_name

,sp.class_desc

/*

We need to change collations of this column in order

to perform the UNION

*/

,ep.protocol_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ‘Securable’

,sp.state_desc

FROM sys.server_permissions sp

INNER JOIN sys.endpoints ep

ON sp.major_id = ep.endpoint_id

INNER JOIN sys.server_principals spr

ON sp.grantee_principal_id = spr.principal_id

WHERE sp.class = 105

AND sp.grantee_principal_id = ‘2’ –ID of public role

As you can see, the public role does have a few permissions. So does our new SQL login utilize any of these permissions by default?

–Can we see if our login will pick up permissions given to public

–Switch over to new SQL login to try to view names of all databases

EXECUTE AS LOGIN = ‘testpublic’

GO

–Who are we?

SELECT USER_NAME()

–Should work

SELECT * FROM sys.databases

GO

–Do we have this permission explicitly? NO

SELECT

spr.name

,sp.permission_name

,sp.class_desc

,‘Server’ AS ‘Securable’

,sp.state_desc

FROM sys.server_permissions sp

INNER JOIN sys.server_principals spr

ON sp.grantee_principal_id = spr.principal_id

WHERE class = 100

AND spr.name = ‘testpublic’

–Switch back over to dbo

REVERT

–Who are we?

SELECT USER_NAME()

So it does look like our login picks up the View Any Database permission. Makes sense as most logins need to find the list of databases.

Summary

As you can see, not much research since this role does not look like it is fully implement in all of the supplied system objects. I will admit that I was a little surprised to see that different columns within the catalog views have different collations. I just have never have run across this before for some reason.

Anyway, this is something new that we need to know about as we start moving our work over to the latest version of SQL Server.

We still have spots left in our February SQL Server DBA Bootcamp. Jump on over to our website as sign up today.