Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

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.
0
rayluvs
Asked:
rayluvs
  • 6
  • 6
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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

0
 
rayluvsAuthor Commented:
Thanx!

How can we validate to our boss the new user has the same privileges as SA?
0
 
Pawan KumarDatabase ExpertCommented:
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  !!
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
rayluvsAuthor Commented:
Thanx will do right away!
0
 
rayluvsAuthor Commented:
results in 0 rows, both of them.
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
rayluvsAuthor Commented:
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?
0
 
Pawan KumarDatabase ExpertCommented:
Have you created the new login, I am getting below- :)

name                        name
dbo                              db_owner
PawanKumar        db_owner
0
 
rayluvsAuthor Commented:
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)
0
 
Pawan KumarDatabase ExpertCommented:
Try this updated... your user should have sysadmin role.

CREATE LOGIN [LoginSQLSameAsSA] 
WITH PASSWORD = 0x0200C612A516C8C41A5C6F642848AB8E3B809EB6917F277AF4C04951065FBF03FDF7F6364F1AF56D4C846FE2593F922865FF0D99C6D91D09A411F1F28FD368B2798C8F968149
HASHED, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

EXEC master..sp_addsrvrolemember @loginame = N'LoginSQLSameAsSA', @rolename = N'sysadmin'

Open in new window


Query ...


--

SELECT sr.name RoleName, sl.name UserName , sl.type_desc , sl.default_database_name FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE sl.name in ( 'sa' , 'LoginSQLSameAsSA' )

--

Open in new window


Output --

RoleName      UserName                                type_desc        default_database_name
sysadmin      sa                                                SQL_LOGIN        master
sysadmin      LoginSQLSameAsSA                SQL_LOGIN        master

Hope it helps!
0
 
rayluvsAuthor Commented:
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!).
0
 
Pawan KumarDatabase ExpertCommented:
Welcome rayluvs !!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now