jana
asked on
How to create a user with the same exact SA-equivalency in Microsoft SQL 2008 and 2014
Pleases advice on the steps to create a user with the same privileges as SA. We need for an application to have its own login separate from any real user and that login then needs to have SA-equivalency.
ASKER
Thanx!
How can we validate to our boss the new user has the same privileges as SA?
How can we validate to our boss the new user has the same privileges as SA?
Use this ... run below queries and check
Supply NewLoginName in the query..
Hope it helps !!
Supply NewLoginName in the query..
--
SELECT u.name, r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
INNER JOIN sys.database_principals AS u
ON u.principal_id = m.member_principal_id
WHERE u.name = 'NewLoginName';
--
----
SELECT u.name, r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
INNER JOIN sys.database_principals AS u
ON u.principal_id = m.member_principal_id
WHERE u.name = 'sa';
--
Hope it helps !!
ASKER
Thanx will do right away!
ASKER
results in 0 rows, both of them.
Try this and see what records you get
SELECT u.name, r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
INNER JOIN sys.database_principals AS u
ON u.principal_id = m.member_principal_id
--
ASKER
It returns:
Don't understand the results; how do I explain my boss with this validation that the new user has the same privileges as SA?
name name
---- ---------
dbo db_owner
Don't understand the results; how do I explain my boss with this validation that the new user has the same privileges as SA?
Have you created the new login, I am getting below- :)
name name
dbo db_owner
PawanKumar db_owner
name name
dbo db_owner
PawanKumar db_owner
ASKER
Yes.
Corerct us if we are wrong, but are you saying that if the user created is db_owner, that username has the same exact equivalency in Microsoft SQL as the SA account? (please explain)
Corerct us if we are wrong, but are you saying that if the user created is db_owner, that username has the same exact equivalency in Microsoft SQL as the SA account? (please explain)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent Thanx!!! By showing our boss that the new sa user is in RoleName we think is more than sufficient (we were printing out different screens in SSMS to how our boss, thanx for the help!).
Welcome rayluvs !!
Open in new window