Menu
I need a T-SQL statement to check if a user is member of a database role in SQL Server. Specifically I need to know if the user is member of the dbo role, because then I don't have to grant additional authority to that user.
If I try to add additional authority when the user is dbo it fails, and my script fails...
262
To find all the role assignments to users in SQL Server database, you can use the following query. SELECT r.name role_principal_name, m.name AS member_principal_name FROM sys. database_role_members rm JOIN sys. database_principals r ON rm.
The dbo user is a special user principal in each database. All SQL Server administrators, members of the sysadmin fixed server role, sa login, and owners of the database, enter databases as the dbo user. The dbo user has all permissions in the database and cannot be limited or dropped.
To determine whether the current user is a member of the specified Windows group or SQL Server database role, use IS_MEMBER (Transact-SQL). To determine whether a SQL Server login is a member of a server role, use IS_SRVROLEMEMBER (Transact-SQL).
IS_ROLEMEMBER?
IF IS_ROLEMEMBER ('db_owner') = 1
BEGIN
PRINT 'Is owner'
END
Or, if querying for a different user:
IF IS_ROLEMEMBER ('db_owner','other user') = 1
BEGIN
PRINT 'Is owner'
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
