Solved

How to create a user with the same exact SA-equivalency in Microsoft SQL 2008 and 2014

Posted on 2016-11-06
12
18 Views
Last Modified: 2016-11-07
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
Comment
Question by:rayluvs
  • 6
  • 6
12 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 

Author Comment

by:rayluvs
Comment Utility
Thanx!

How can we validate to our boss the new user has the same privileges as SA?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 

Author Comment

by:rayluvs
Comment Utility
Thanx will do right away!
0
 

Author Comment

by:rayluvs
Comment Utility
results in 0 rows, both of them.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:rayluvs
Comment Utility
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Have you created the new login, I am getting below- :)

name                        name
dbo                              db_owner
PawanKumar        db_owner
0
 

Author Comment

by:rayluvs
Comment Utility
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
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
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
 

Author Comment

by:rayluvs
Comment Utility
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Welcome rayluvs !!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now