August 19, 2024 at 2:50 pm
Hello, I need to create a script that allows access to a database at the Security level and the database level.
The following allows the ability to do this at the database level, but I cannot do the same at the Security level. Is this possible?
ALTER ROLE db_owner ADD MEMBER TestUser;
How can we do the same at the Security level?
August 19, 2024 at 3:04 pm
So you want to do a
CREATE LOGIN
CREATE USER
ALTER ROLE
Create the login first at the server level if that is needed.
Create the user at the database level for that login
Alter the role or some other script which gives the right permissions needed at the database level.
That is if I have understood your question correctly.
August 19, 2024 at 4:23 pm
Many thanks! How do I create the login at the sever level? Is that master db?
August 20, 2024 at 8:08 am
To see the actual db owner of a db, you only need a login.
CREATE LOGIN [test] WITH PASSWORD=N'123Test...........', DEFAULT_DATABASE=[master];
When logged in as that account, the following query will get the actual db owner.
Select name, suser_sname(owner_sid) as DbOwner
from sys.databases
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 20, 2024 at 2:09 pm
btw: if you also want an inventory of all members of db group db_owner in all db, you'll need to provide the account Connect to all db.
use [master]
GO
GRANT CONNECT ANY DATABASE TO [test]
GO
Then the account can connect to the instance and assemble the inventory:
CREATE TABLE #RoleMembership(
[DbName] [sysname] NOT NULL,
[RoleName] [sysname] NOT NULL,
[UserName] [sysname] NOT NULL,
[type] [char](1) NULL,
[type_desc] [nvarchar](60) NULL,
[authentication_type] [int] NULL,
[authentication_type_desc] [nvarchar](60) NULL
)
exec sp_MSforeachdb 'use [?];
insert into #RoleMembership
SELECT ''?'' as DbName
, DP1.name AS RoleName
, ISNULL(DP2.name, ''No members'') AS UserName
, DP2.type
, DP2.type_desc
, DP2.authentication_type
, DP2.authentication_type_desc
FROM sys.database_principals AS DP1
LEFT JOIN sys.database_role_members AS DRM
ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = ''R''
and DP1.name in ( ''db_owner'', ''Propriatary_Role_DBA'' )
and DP2.name <> ''dbo''
;
'
Select *
from #RoleMembership
order by DbName, RoleName, UserName;
go
drop table #RoleMembership;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply