?
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
?
39 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 29

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 29

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 29

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 29

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 29

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 29

Expert Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

770 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