Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
SQL Server 2012

Есть сервер логин vasya, есть юзер базы vasya, на который мэпнут серверный логин.

У юзера есть database role:
myrole
public

Вопрос: когда я для логина даю серверную роль , скажем, sysadmin, у юзера остается (в SSMS) роль myrole, а вот такой запрос, выполняемый из-под данного юзера, уже не возвращает эту роль:
SELECT	dbo.sysusers.uid, dbo.sysusers.name,IS_MEMBER(dbo.sysusers.name) as ismemeber,
*
FROM	dbo.sysusers 
WHERE dbo.sysusers.issqlrole = 1 
AND IS_MEMBER(dbo.sysusers.name)=1



Если же убрать серверную роль сисадмина у юзера - запрос возвращает, все ок.
6 сен 18, 13:03    [21666836]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
Члены роли sysadmin сопоставлются в БД с dbo.
Так что vasya перестает быть таковым.
6 сен 18, 13:26    [21666886]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
invm
Члены роли sysadmin сопоставлются в БД с dbo.
Так что vasya перестает быть таковым.


ясно,спасибо!Если убираю серверную роль - все восстанавливается, как и хочется юзеру.

И еще, правильно ли так:
т.е. несмотря на то, что я вижу назначение дб роли для него в SSMS - запрос не выдает принадлежность васи к этой роли.
И если я буду в базе этому юзеру еще назначать всякие дброли, то эти назначения не будут иметь влияния до снятия серверной роли?
6 сен 18, 13:39    [21666912]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
256k
И если я буду в базе этому юзеру еще назначать всякие дброли, то эти назначения не будут иметь влияния до снятия серверной роли?
Зависит от назначенной серверной роли или ролей.
Если в результате логин будет сопоставляться с dbo, то да.
6 сен 18, 13:46    [21666929]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5952
Для пользователя, у которого логин является участником роли sysadmin, роли лучше смотреть вот так:
select roles.*
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='U' and principals.name='DOMAIN\Login'

ну, или просто через
exec sp_helplogins 'DOMAIN\Login'
6 сен 18, 13:52    [21666935]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
Сон Веры Павловны
Для пользователя, у которого логин является участником роли sysadmin, роли лучше смотреть вот так:
select roles.*
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='U' and principals.name='DOMAIN\Login'

ну, или просто через
exec sp_helplogins 'DOMAIN\Login'


ясно, хотя, у меня это не доменный логин, а SQL Server-ный
6 сен 18, 13:55    [21666941]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
Сон Веры Павловны
Для пользователя, у которого логин является участником роли sysadmin, роли лучше смотреть вот так:
select roles.*
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='U' and principals.name='DOMAIN\Login'

ну, или просто через
exec sp_helplogins 'DOMAIN\Login'


в данном случае то же: при наличии у логина серверной роли сисадмин и этот запрос не показывает юзерские дб роли, как и было объяснено выше
6 сен 18, 13:58    [21666948]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5952
256k
в данном случае то же: при наличии у логина серверной роли сисадмин и этот запрос не показывает юзерские дб роли, как и было объяснено выше

У меня всё прекрасно показывает:
USE master
GO
select @@version
go
CREATE LOGIN [test_login] WITH PASSWORD=N'12345aA',
	DEFAULT_DATABASE=[master],
	DEFAULT_LANGUAGE=[us_english],
	CHECK_EXPIRATION=OFF,
	CHECK_POLICY=OFF
GO
use [stuff]
go
create user [test_login] for login [test_login]
go
create role [test_role];
exec sp_addrolemember 'test_role', 'test_login';
go
print('without sysadmin:');
select roles.name
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='S' and principals.name='test_login';
go
exec sp_addsrvrolemember 'test_login', 'sysadmin';  
go
print('with sysadmin:');
select roles.name
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='S' and principals.name='test_login';
go
exec sp_droprolemember 'test_role', 'test_login';
drop role [test_role];
drop user [test_login];
use master
go
drop login [test_login];
go


----------------------------------------------------------------------------------
Microsoft SQL Server 2014 (SP2-CU12) (KB4130489) - 12.0.5589.7 (X64)
Jun 9 2018 11:17:15
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


(1 row(s) affected)

without sysadmin:
name
-----------------
test_role

(1 row(s) affected)

with sysadmin:
name
-----------------
test_role

(1 row(s) affected)
6 сен 18, 14:14    [21666978]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
256k
в данном случае то же: при наличии у логина серверной роли сисадмин и этот запрос не показывает юзерские дб роли, как и было объяснено выше
Вы же для текущего юзера пытаетесь, поэтому и не показывает.
Если логин всегда совпадает с юзером, перепишите так:
select roles.*
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='U' and principals.name=system_user
6 сен 18, 14:29    [21667008]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
invm
256k
в данном случае то же: при наличии у логина серверной роли сисадмин и этот запрос не показывает юзерские дб роли, как и было объяснено выше
Вы же для текущего юзера пытаетесь, поэтому и не показывает.
Если логин всегда совпадает с юзером, перепишите так:
select roles.*
from sys.database_principals principals
join sys.database_role_members membership on membership.member_principal_id=principals.principal_id
join sys.database_principals roles on roles.principal_id=membership.role_principal_id
where principals.type='U' and principals.name=system_user

в частности - да,совпадает, но использоваться запрос будет для текущего юзера.

Так сработал, т.е. получается, что никуда юзерские роли не деваются при назначении логину севреной роли сисадмин?
6 сен 18, 15:14    [21667080]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5952
256k
т.е. получается, что никуда юзерские роли не деваются при назначении логину севреной роли сисадмин?

Куда ж им деться, если при исключении логина из sysadmin они вновь появляются. Не восстанавливаются же они ниоткуда.
Что же до проверки на вхождение в роль с помощью IS_MEMBER для участника sysadmin - в справке всё написано абсолютно однозначно:
Members of the sysadmin fixed server role enter every database as the dbo user. Checking permission for member of the sysadmin fixed server role, checks permissions for dbo, not the original login. Since dbo can't be added to a database role and doesn’t exist in Windows groups, dbo will always return 0 (or NULL if the role doesn't exist).
6 сен 18, 15:20    [21667089]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
Поскольку SSMS показывает датабазе роли юзера правильно, даже если его логин имеет серверную роль sysadmin - пытаюсь выловить в профайлере запрос, который выполняется SSMS.

Но не могу поймать именно тот запрос, который показывает принадлежность юзера базы к списку ролей

SSMS->сервер->Databases->mybase->Security->Users->myuser->rightclick->Properties->Membership
13 сен 18, 11:55    [21673305]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5952
И чем приведенный выше запрос с использованием sys.database_role_members не устраивает?
13 сен 18, 12:05    [21673326]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
komrad
Member

Откуда:
Сообщений: 5244
256k
Поскольку SSMS показывает датабазе роли юзера правильно, даже если его логин имеет серверную роль sysadmin - пытаюсь выловить в профайлере запрос, который выполняется SSMS.

Но не могу поймать именно тот запрос, который показывает принадлежность юзера базы к списку ролей

SSMS->сервер->Databases->mybase->Security->Users->myuser->rightclick->Properties->Membership


exec sp_helprolemember 
13 сен 18, 12:05    [21673327]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
Сон Веры Павловны
И чем приведенный выше запрос с использованием sys.database_role_members не устраивает?


да вроде устраивает, но хотел понять как студия это делает, но не увидел, а результат в Properties->Membership правильный
13 сен 18, 12:12    [21673337]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
256k
Сон Веры Павловны
И чем приведенный выше запрос с использованием sys.database_role_members не устраивает?


да вроде устраивает, но хотел понять как студия это делает, но не увидел, а результат в Properties->Membership правильный

профайлер запретили?
13 сен 18, 12:13    [21673341]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
256k
Сон Веры Павловны
И чем приведенный выше запрос с использованием sys.database_role_members не устраивает?


да вроде устраивает, но хотел понять как студия это делает, но не увидел, а результат в Properties->Membership правильный
Профайлером посмотрите.
13 сен 18, 12:13    [21673343]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
Сон Веры Павловны
И чем приведенный выше запрос с использованием sys.database_role_members не устраивает?


кстати, как использовать ваш запрос, если это, например, во вью, и я знаю юзера базы, а не его логин?
13 сен 18, 12:20    [21673353]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
TaPaK
256k
пропущено...


да вроде устраивает, но хотел понять как студия это делает, но не увидел, а результат в Properties->Membership правильный

профайлер запретили?


тремя постами выше я написал, что ловлю запросы профайлером
13 сен 18, 12:25    [21673361]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
alexeyvg
256k
пропущено...


да вроде устраивает, но хотел понять как студия это делает, но не увидел, а результат в Properties->Membership правильный
Профайлером посмотрите.


выше я так написал:
"Поскольку SSMS показывает датабазе роли юзера правильно, даже если его логин имеет серверную роль sysadmin - пытаюсь выловить в профайлере запрос, который выполняется SSMS.

Но не могу поймать именно тот запрос, который показывает принадлежность юзера базы к списку ролей

SSMS->сервер->Databases->mybase->Security->Users->myuser->rightclick->Properties->Membership"
13 сен 18, 12:26    [21673366]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5952
256k
кстати, как использовать ваш запрос, если это, например, во вью, и я знаю юзера базы, а не его логин?

Ну, вообще sys.database_principals - это именно пользователи базы, а не логины. И поле name содержит именно имя пользователя, а не имя логина.
13 сен 18, 14:05    [21673555]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
Сон Веры Павловны
256k
кстати, как использовать ваш запрос, если это, например, во вью, и я знаю юзера базы, а не его логин?

Ну, вообще sys.database_principals - это именно пользователи базы, а не логины. И поле name содержит именно имя пользователя, а не имя логина.


да, протормозил
13 сен 18, 14:37    [21673634]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
256k
Но не могу поймать именно тот запрос, который показывает принадлежность юзера базы к списку ролей

SSMS->сервер->Databases->mybase->Security->Users->myuser->rightclick->Properties->Membership"
Кроме этих запросов, студии неоткуда взять "принадлежность юзера базы к списку ролей"
Так что просто внимательно смотрите.
Выполняйте их, смотрите, что возвращают.
13 сен 18, 14:40    [21673639]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
alexeyvg
256k
Но не могу поймать именно тот запрос, который показывает принадлежность юзера базы к списку ролей

SSMS->сервер->Databases->mybase->Security->Users->myuser->rightclick->Properties->Membership"
Кроме этих запросов, студии неоткуда взять "принадлежность юзера базы к списку ролей"
Так что просто внимательно смотрите.
Выполняйте их, смотрите, что возвращают.


да, конечно, что-то выполняется, но я,собственно, потому и спросил, что не нашел.
Профайлером выловил от старта студии до уже показанной формы Проперти.
13 сен 18, 14:47    [21673655]     Ответить | Цитировать Сообщить модератору
 Re: Login and user: не вижу роли  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
256k
да, конечно, что-то выполняется, но я,собственно, потому и спросил, что не нашел.
Профайлером выловил от старта студии до уже показанной формы Проперти.
Студия сначала читает все роли из sys.database_principals, а потом для каждой (в цикле) проверяет соответствие батчем:
+
exec sp_executesql N'
set NOCOUNT OFF
CREATE TABLE #tmp_role_member_ids (id int not null, role_id int null, sub_role_id int null, generation int null)
declare @generation int
set @generation = 0
INSERT INTO #tmp_role_member_ids (id) SELECT
rl.principal_id AS [ID]
FROM
sys.database_principals AS rl
WHERE
(rl.type = ''R'')and(rl.name=@_msparam_0)
UPDATE #tmp_role_member_ids SET role_id = id, sub_role_id = id, generation=@generation
WHILE ( 1=1 )
BEGIN
	INSERT INTO #tmp_role_member_ids (id, role_id, sub_role_id, generation)
		SELECT a.member_principal_id, b.role_id, a.role_principal_id, @generation + 1
            FROM sys.database_role_members AS a INNER JOIN #tmp_role_member_ids AS b
			ON a.role_principal_id = b.id
            WHERE b.generation = @generation
	if @@ROWCOUNT <= 0
		break
	set @generation = @generation + 1
END
DELETE #tmp_role_member_ids WHERE id in (SELECT
rl.principal_id AS [ID]
FROM
sys.database_principals AS rl
WHERE
(rl.type = ''R'')and(rl.name=@_msparam_1) )
UPDATE #tmp_role_member_ids SET generation = 0;
INSERT INTO #tmp_role_member_ids (id, role_id, generation) 
    SELECT distinct id, role_id, 1 FROM #tmp_role_member_ids
DELETE #tmp_role_member_ids WHERE generation = 0



SELECT
''Server[@Name='' + quotename(CAST(
        serverproperty(N''Servername'')
       AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/'' + (CASE u.type 
				WHEN ''A'' THEN N''ApplicationRole''
				WHEN ''R'' THEN N''Role''
				ELSE N''User'' END)
			+ ''[@Name='' + quotename(u.name,'''''''') + '']'' AS [Urn],
u.name AS [Name],
u.principal_id AS [ID],
suser_sname(u.sid) AS [Login]
FROM
sys.database_principals AS rl
INNER JOIN #tmp_role_member_ids AS m ON m.role_id=rl.principal_id
INNER JOIN sys.database_principals AS u ON u.principal_id = m.id
WHERE
(u.name=@_msparam_2)and((rl.type = ''R'')and(rl.name=@_msparam_3))

			drop table #tmp_role_member_ids
		
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'testRole',@_msparam_1=N'testRole',@_msparam_2=N'testUser',@_msparam_3=N'testRole'

Внутри батча используется, например, database_role_members
13 сен 18, 17:48    [21674030]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить