Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-11-06
12
Medium Priority
?
41 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41876172
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
ID: 41876190
Thanx!

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

Expert Comment

by:Pawan Kumar
ID: 41876201
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:rayluvs
ID: 41876260
Thanx will do right away!
0
 

Author Comment

by:rayluvs
ID: 41876274
results in 0 rows, both of them.
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41876522
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
 

Author Comment

by:rayluvs
ID: 41876535
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 32

Expert Comment

by:Pawan Kumar
ID: 41876583
Have you created the new login, I am getting below- :)

name                        name
dbo                              db_owner
PawanKumar        db_owner
0
 

Author Comment

by:rayluvs
ID: 41876746
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 32

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41876751
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
ID: 41876765
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 32

Expert Comment

by:Pawan Kumar
ID: 41876769
Welcome rayluvs !!
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

618 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