Assigning VIEW SERVER STATE to an account (preferably WITHOUT LOGIN)
-
I'm looking at a function that has been passed to me, to assign VIEW SERVER STATE, because of the access it needs.
Now I'm in the middle of writing a blog about assigning database-level permissions to a WITHOUT LOGIN account, to control access - so I thought this would be easy enough.
However, VIEW SERVER STATE is a server-level permission and it doesn't appear to behave the same way.
Firstly, it would appear the user account has to be within Master.
It also appears that Master doesn't like a user WITHOUT LOGIN.
And then it just complains it can't see the user that I've created, when I attempt to add the EXECUTE AS line to the function.
--My account is sysadmin
use SecurityTest;
gocreate user SteveTest without login;
gogrant view server state to SteveTest;
--Msg 4621, Level 16, State 10, Line 7
--Permissions at the server scope can only be granted when the current database is masterdrop user SteveTest;
gouse master;
gocreate user SteveTest without login;
gogrant view server state to SteveTest;
--Msg 15151, Level 16, State 1, Line 21
--Cannot find the login 'SteveTest', because it does not exist or you do not have permission.drop user SteveTest;
go--So, as guess, it might not like a user without a login (on master anyway)...
CREATE LOGIN [SteveTest] WITH PASSWORD=N'SteveTest', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GOCREATE USER [SteveTest] FOR LOGIN [SteveTest]
GOgrant view server state to SteveTest;
go
--No error this time, but I see no evidence that it actually did anything
Ideally, I would like the function within the SecurityTest database, with an EXECUTE AS of a user that has VIEW SERVER STATE permission. If that isn't possibel I would appreciate knowing how it should be done. -
It isn't simply that your intellisense hasn't updated is it? I can't appear to replicate your issue on SQL Server 2012; if i use the User created with the Login that is.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Monday, April 9, 2018 3:59 AM
Oh really, what are the odds that I was that stupid?
I'm just going to go into a corner and shoot myself....
Thank you for taking the time Thom and I do apologise. I'm still having fun trying to get this to work as I expect it to but I'll provide an update to that once I've played a little more.
And after I've refreshed Intellisense too...
-
I have no problems with a user who has a login.
For server level permissions, I believe it looks for a login/server principal not a database user/database principal. And the error when trying to grant the permissions to a user with no login indicates the same.Sue
- BrainDonor - Monday, April 9, 2018 7:41 AM
We've all done it. It's why I never trust the red squiggly line when I have scripts that are creating objects and then using those same said objects in the same query window; as half the time intellisense is actually unintellisense.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
OK, I'm back. So, having ascertained that Intellisense really does like to have a laugh at my expense I have now set up the desired permission.
Or rather, I have executed the relevant commands without getting an error- I'm not convinced.
I'm now getting errors regarding permissions, even when I eventually make the account sysadmin.
My account (where I'm executing all of this from) is sysadmin.
I have no doubt I'm missing a trick here but can't see what it must be:
USE [master]
GO
CREATE LOGIN [SteveTest] WITH PASSWORD=N'SteveTest', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GOCREATE USER [SteveTest] FOR LOGIN [SteveTest]
GOGRANT VIEW SERVER STATE TO SteveTest;
GO/* A severely butchered version of the function I'm trying to work with.
Notice that the important table is commented out at this point. */
CREATE FUNCTION [dbo].[fnGetReplicaDetails]
(
@AGREPLICAS INT
)
RETURNS INT
with execute as 'SteveTest'
AS
BEGINDECLARE @LAG INT = 0
DECLARE @COUNTAGREPLICAS INT
SET @COUNTAGREPLICAS =
(
SELECT COUNT(*) FROM(
SELECT
AG.name AS 'Name'FROM
master.sys.availability_groups AS AG
--LEFT JOIN master.sys.dm_hadr_availability_group_states as agstates
--ON AG.group_id = agstates.group_id) AS AGCOUNT
)RETURN @LAG
RETURN @LAG
END
GO/*Just in case it is this stupid */
GRANT EXECUTE ON [dbo].[fnGetReplicaDetails] TO [SteveTest]
GOSELECT [dbo].[fnGetReplicaDetails] (
4)
GO
/*Returns a value*//*Now add the dm_hadr_availability_group_states table into the mix*/
ALTER FUNCTION [dbo].[fnGetReplicaDetails]
(
@AGREPLICAS INT
)
RETURNS INT
with execute as 'SteveTest'
AS
BEGINDECLARE @LAG INT = 0
DECLARE @COUNTAGREPLICAS INT
SET @COUNTAGREPLICAS =
(
SELECT COUNT(*) FROM(
SELECT
AG.name AS 'Name'FROM
master.sys.availability_groups AS AG
LEFT JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id) AS AGCOUNT
)RETURN @LAG
RETURN @LAG
END
GO/*And repeat the test */
SELECT [dbo].[fnGetReplicaDetails] (
4)
GO
--The user does not have permission to perform this action./*So, just for a giggle....*/
ALTER SERVER ROLE [sysadmin] ADD MEMBER [SteveTest]
GO/*And try again */
SELECT [dbo].[fnGetReplicaDetails] (
4)
GO
--The user does not have permission to perform this action.I've also tried this on an instance that I have never used since installation - just in case I've left a mess somewhere I haven't seen, and I get the same behavior.
- BrainDonor - Thursday, April 12, 2018 8:11 AM
Glad you got there Steve. 🙂 Interesting solution in the end, not quite intuitive; but I find that those problems are actually some of the more enjoyable ones to solve.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - BrainDonor - Thursday, April 12, 2018 8:11 AM
Yep, this is what I would have suggested if I got to this thread earlier today. You need to sign the function that uses EXECUTE AS with a certificate. What I've done in cases like this where you have to have a login is to create it and then within the same script I do:
ALTER LOGIN [SpecualUser] DISABLE;
DENY CONNECT SQL TO [SpecialUser];
This keeps people from logging in as that user directly, but EXECUTE AS in stored procs and functions still works. Then you create the user in the appropriate databases it needs permissions in. Once the proc or function is signed, then it can access server level permissions that were granted or access objects in another database.
Also, if anyone who's not a DBA needs to write procs or functions with the EXECUTE AS, they need permissions like this:GRANT IMPERSONATE ON USER::[SpecialUser] TO [AppDevTeam];
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply