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.
