login issue - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-04-13 : 14:38:22

Hi All,

I have a security very typical scenario. I have a SQL Server 2008.
I have client which is having this wiered scenario.
Basically, he wants to test View Server State permission so that he can access all the processes running on that particular instance.
I tried the below and tried to reproduce the issue and am able to do it at my but it is not working on client instance.

USE [master]
GO
CREATE LOGIN [testing] WITH PASSWORD=N'testing', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [db1]
GO
CREATE USER [testing] FOR LOGIN [testing]
GO
USE [db1]
GO
EXEC sp_addrolemember N'db_datareader', N'testing'
GO

--logged in a testing login and run below cmd
select * from sys.sysprocesses
/*
spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch ecid open_tran status sid hostname program_name hostprocess cmd nt_domain nt_username net_address net_library loginame context_info sql_handle stmt_start stmt_end request_id
54 1428 0 0x0000 0 SOS_SCHEDULER_YIELD 1 2 0 0 2 2011-04-13 23:39:22.140 2011-04-13 23:40:47.660 0 0 runnable 0x9CB24DF2069E4D4CADBCC972ABED53C600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WIN-SERV Microsoft SQL Server Management Studio - Query 6116 SELECT 000FFEEE3A29 LPC testing 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000100B0DAD51AF0B3E78B0000000000000000 0 -1 0
*/

-- checked the msdn link
--As "sa" user now i have granted the
use master
go
GRANT VIEW SERVER STATE to testing
go

-- As "testing" login, tried to access the sysprocesses. Am able to do that but on client instance we are not able to get all the process information.

select * from sys.sysprocesses
/*
spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch ecid open_tran status sid hostname program_name hostprocess cmd nt_domain nt_username net_address net_library loginame context_info sql_handle stmt_start stmt_end request_id
1 2380 0 0x0000 0 PREEMPTIVE_XE_CALLBACKEXECUTE 0 1 2506078 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 RESOURCE MONITOR sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
2 2400 0 0x0158 21345 XE_TIMER_EVENT 0 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 XE TIMER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
3 2364 0 0x015A 430863300 XE_DISPATCHER_WAIT 0 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 XE DISPATCHER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
4 2624 0 0x0060 769 LAZYWRITER_SLEEP 0 1 12760 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 LAZY WRITER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
5 2404 0 0x007F 14733 LOGMGR_QUEUE 0 1 2340 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 LOG WRITER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
6 2628 0 0x0080 1448 REQUEST_FOR_DEADLOCK_SEARCH 0 1 265 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 LOCK MONITOR sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
7 2632 0 0x009D 1146837089 KSOURCE_WAKEUP 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 SIGNAL HANDLER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
8 0 0 0x0000 0 MISCELLANEOUS 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
9 2396 0 0x00A2 3231 SQLTRACE_INCREMENTAL_FLUSH_SLEEP 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TRACE QUEUE TASK sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
10 2652 0 0x0069 923 SLEEP_TASK 1 1 265 1 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 BRKR TASK sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
11 2636 0 0x0081 423827872 CHECKPOINT_QUEUE 10 1 483 884 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 CHECKPOINT sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
12 2640 0 0x007E 1146889947 ONDEMAND_TASK_QUEUE 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
13 2720 0 0x00AE 83495369 BROKER_EVENTHANDLER 1 1 46 27 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 BRKR EVENT HNDLR sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
14 0 0 0x0000 0 MISCELLANEOUS 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
15 2784 0 0x00A9 1146880123 BROKER_TRANSMITTER 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 BRKR TASK sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
16 2648 0 0x00A9 1146880123 BROKER_TRANSMITTER 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 BRKR TASK sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
17 0 0 0x0000 0 MISCELLANEOUS 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
18 0 0 0x0000 0 MISCELLANEOUS 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
19 0 0 0x0000 0 MISCELLANEOUS 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
20 0 0 0x0000 0 MISCELLANEOUS 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
21 0 0 0x0000 0 MISCELLANEOUS 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
22 0 0 0x0000 0 MISCELLANEOUS 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
23 0 0 0x0000 0 MISCELLANEOUS 1 1 0 0 0 2011-03-31 17:08:26.970 2011-03-31 17:08:26.970 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0
51 0 0 0x0000 0 MISCELLANEOUS 1 1 640 64 3 2011-04-09 00:51:36.780 2011-04-13 23:38:43.507 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WIN-SERV Microsoft SQL Server Management Studio 6116 AWAITING COMMAND 000FFEEE3A29 LPC sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000100EACCAE2A5069028C0000000000000000 0 0 0
52 0 0 0x0000 0 MISCELLANEOUS 1 1 76 4 2 2011-04-13 22:45:31.317 2011-04-13 23:42:20.373 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WIN-SERV Microsoft SQL Server Management Studio - Query 6116 AWAITING COMMAND 000FFEEE3A29 LPC sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0100010064C3040580BAE78B0000000000000000 0 0 0
53 0 0 0x0000 0 MISCELLANEOUS 1 2 46 0 3 2011-04-13 23:38:56.733 2011-04-13 23:38:59.040 0 0 sleeping 0x9CB24DF2069E4D4CADBCC972ABED53C600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WIN-SERV Microsoft SQL Server Management Studio 6116 AWAITING COMMAND 000FFEEE3A29 LPC testing 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000100E9630214A075028C0000000000000000 0 0 0
54 6520 0 0x0000 0 PREEMPTIVE_OS_WAITFORSINGLEOBJEC 1 2 15 0 2 2011-04-13 23:39:22.140 2011-04-13 23:42:31.270 0 0 runnable 0x9CB24DF2069E4D4CADBCC972ABED53C600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WIN-SERV Microsoft SQL Server Management Studio - Query 6116 SELECT 000FFEEE3A29 LPC testing 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x010001007BFE2E1580BDE78B0000000000000000 0 -1 0
55 0 0 0x0000 0 MISCELLANEOUS 4 1 156 32 2 2011-04-07 15:35:00.050 2011-04-07 18:36:01.110 0 0 sleeping 0x0101000000000005120000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WIN-SERV SQLAgent - Generic Refresher 7376 AWAITING COMMAND NT AUTHORITY SYSTEM 000FFEEE3A29 LPC NT AUTHORITY\SYSTEM 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000400EB08F51F606443860000000000000000 0 0 0
57 0 0 0x0000 0 MISCELLANEOUS 1 1 0 0 2 2011-04-13 23:37:31.487 2011-04-13 23:37:31.487 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WIN-SERV Microsoft SQL Server Management Studio - Query 6116 AWAITING COMMAND 000FFEEE3A29 LPC sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x010001008F85BD000065028C0000000000000000 0 0 0
58 0 0 0x0000 0 MISCELLANEOUS 1 2 0 0 2 2011-04-13 23:39:22.167 2011-04-13 23:39:22.167 0 0 sleeping 0x9CB24DF2069E4D4CADBCC972ABED53C600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WIN-SERV Microsoft SQL Server Management Studio - Query 6116 AWAITING COMMAND 000FFEEE3A29 LPC testing 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x010001008F85BD000065028C0000000000000000 0 0 0
59 0 0 0x0000 0 MISCELLANEOUS 4 1 15 17 2 2011-04-07 18:27:43.883 2011-04-07 18:27:44.630 0 0 sleeping 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WIN-SERV Microsoft SQL Server Management Studio 6116 AWAITING COMMAND 000FFEEE3A29 LPC sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x010004004F016F1410B56F850000000000000000 0 0 0
61 0 0 0x0000 0 MISCELLANEOUS 4 1 46 183 2 2011-04-08 00:00:00.600 2011-04-13 02:00:00.630 0 0 sleeping 0x0101000000000005120000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WIN-SERV SQLAgent - Job invocation engine 7376 AWAITING COMMAND NT AUTHORITY SYSTEM 000FFEEE3A29 LPC NT AUTHORITY\SYSTEM 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000400EB08F51F606443860000000000000000 0 0 0
*/

-- This problem is specific to only single instances. On another instance, we are able to run like a CHARM.
-- only on this instance it is behaving in this manner.

I even tried to checking if there is any DENY PERMISSIONS using below query.

-- Server level permissions
SELECT ToWhom = grantee.name,
WhoHasGiven = grantor.name,
dp.class_desc,
dp.permission_name,
dp.state_desc,
ObjectName = OBJECT_NAME(major_id)
FROM sys.server_permissions dp
JOIN
sys.server_principals grantee on
dp.grantee_principal_id = grantee.principal_id
JOIN
sys.server_principals grantor on
dp.grantor_principal_id = grantor.principal_id
order by grantee.name

/*
ToWhom WhoHasGiven class_desc permission_name state_desc ObjectName
##MS_AgentSigningCertificate## sa SERVER CONNECT SQL GRANT NULL
##MS_PolicyEventProcessingLogin## sa SERVER CONNECT SQL GRANT NULL
##MS_PolicySigningCertificate## sa SERVER CONTROL SERVER GRANT NULL
##MS_PolicySigningCertificate## sa SERVER VIEW ANY DEFINITION GRANT NULL
##MS_SQLReplicationSigningCertificate## sa SERVER VIEW ANY DEFINITION GRANT NULL
##MS_SQLReplicationSigningCertificate## sa SERVER VIEW SERVER STATE GRANT NULL
##MS_SQLResourceSigningCertificate## sa SERVER VIEW ANY DEFINITION GRANT NULL
NT AUTHORITY\SYSTEM sa SERVER CONNECT SQL GRANT NULL
public sa SERVER VIEW ANY DATABASE GRANT NULL
public sa ENDPOINT CONNECT GRANT NULL
public sa ENDPOINT CONNECT GRANT sysrscols
public sa ENDPOINT CONNECT GRANT NULL
public sa ENDPOINT CONNECT GRANT sysrowsets
ram sa SERVER CONNECT SQL GRANT NULL
sa sa SERVER CONNECT SQL GRANT NULL
testing sa SERVER CONNECT SQL GRANT NULL
testing sa SERVER VIEW SERVER STATE GRANT NULL

*/

use db1
go
SELECT ToWhom = grantee.name,
WhoHasGiven = grantor.name,
dp.class_desc,
dp.permission_name,
dp.state_desc,
ObjectName = OBJECT_NAME(major_id)
FROM sys.database_permissions dp
JOIN
sys.database_principals grantee on
dp.grantee_principal_id = grantee.principal_id
JOIN
sys.database_principals grantor on
dp.grantor_principal_id = grantor.principal_id
order by grantee.name

Now my question is there anything we can check from "PUBLIC" role and "GUEST" user?
But before that, i would like to know how the permissions will be inherited and want to an efficient
way to check for the DENY and nail it from where it is getting inherited.

Is there any set of queries by which we can nail it down?????
I EVEN tried dropping and creating the LOGIN at his end. Still same issue....!!!
This is really frustrating...!!!

Now am not even able to repro his scenario at my end.

Any help would be greatly appreciated.

Thanks in advance.

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-04-13 : 19:04:31

Checked if DENY is coming from public role. even that is not working.

Infact, if i try to grant VIEW SERVER STATE to public then it is working fine.

Does anyone has comments on this issue?

Thanks in Advance.

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-16 : 01:57:32

SQL Server Version and any SP ?

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources