Link to home
Start Free TrialLog in
Avatar of jana
janaFlag for United States of America

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.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Try this..

CREATE LOGIN [testPawan] WITH PASSWORD=N'Pawan', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

GRANT CONTROL SERVER TO [testPawan] WITH GRANT OPTION;

Open in new window

Avatar of jana

ASKER

Thanx!

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..

--
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';
--

Open in new window

--
--
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';
--

Open in new window


Hope it helps  !!
Avatar of jana

ASKER

Thanx will do right away!
Avatar of jana

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

--

Open in new window

Avatar of jana

ASKER

It returns:

name name
---- ---------
dbo  db_owner

Open in new window


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
Avatar of jana

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)
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

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 !!