Solved

how to grant sysadmin to a user?

Posted on 2010-08-23
23
3,460 Views
Last Modified: 2012-05-10
Hi,
I want to know how to grant  sysadmin right to a user by sqlcmd?
and how to grant a full right of database (fsze88) to a user by sqlcmd?

Thanks
0
Comment
Question by:fsze88
[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
23 Comments
 
LVL 11

Expert Comment

by:aelliso3
ID: 33501079
exec sp _addsrvrolemember N'fsze88', sysadmin
0
 
LVL 15

Author Comment

by:fsze88
ID: 33501306
how about

grant a full right of database (fsze88) to a user by sqlcmd?
0
 
LVL 3

Expert Comment

by:Dave_LaSalle
ID: 33501354
Is this what you are looking for?
http://msdn.microsoft.com/en-us/library/ms189595.aspx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Author Comment

by:fsze88
ID: 33501422
I means a database named 'fsze88'
and I would like to know how to grant full right of database 'fsze88' to a user 'fsze88'
Untitled.png
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33501496
EXEC fsze88..sp_addrolemember 'db_Owner', 'UserName'
0
 
LVL 5

Expert Comment

by:TIGERMARK
ID: 33501533
http://www.adriancolquhoun.com/WindowsWorkflow/UsingtheSqlWorkflowPersistenceService/tabid/138/Default.aspx

Simply create a new Database Project under Other Project Types, creating a reference to the target database.  From there it is easy to right-click and add existing .sql files.  Then just right-click and run.
0
 
LVL 15

Author Comment

by:fsze88
ID: 33501598
1> EXEC fsze88..sp_addrolemember 'db_Owner', 'fsze88'
2> go
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line 75
¿¿¿¿¿¿ 'fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿
1>
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33501729
EXEC sp_addrolemember 'db_Owner', 'domain\fsze88'
or
EXEC sp_addrolemember 'db_Owner', 'fsze88'
We have a little bit of confusion. SYSADMIN is a server role and not a database role. It sounds like you are looking for db_owner permission on db fsze88
0
 
LVL 15

Author Comment

by:fsze88
ID: 33505137
I don't know why it said no this user (fsze88)


C:\Users\fsze88>sqlcmd
1> EXEC sp_addrolemember 'db_Owner', 'fsze88'
2> go
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line 75
¿¿¿¿¿¿ 'fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿
1> EXEC sp_addrolemember 'db_Owner', 'domain\fsze88'
2> go
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line 75
¿¿¿¿¿¿ 'domain\fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿
1>

Open in new window

0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33505499
Is this ID an NT Authentication, or SQL auth?
0
 
LVL 15

Author Comment

by:fsze88
ID: 33507355
should be SQL auth
Untitled.png
0
 
LVL 11

Assisted Solution

by:aelliso3
aelliso3 earned 500 total points
ID: 33510576
after you started SQLCMD, did you go into the database you are trying to grant permissions too:
 

USE fsze88
GO

EXEC sp_addrolemember 'db_Owner', 'fsze88'
GO 

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33510822
sorry same

C:\Users\fsze88>sqlcmd
1> use fsze88
2> go
¿¿¿¿¿¿¿¿¿¿ 'fsze88'¿
1> EXEC sp_addrolemember 'db_Owner', 'fsze88'
2> GO
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line 75
¿¿¿¿¿¿ 'fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿
1>

Open in new window

0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33511099
start off with
SQLCMD -S FSZE88-PC
the error, Msg 15410, is saying that the user does not exist, which is strange since you logged in with that user ... hopefully with stating the server name in the command above it will direct it to the correct place.
0
 
LVL 15

Author Comment

by:fsze88
ID: 33511340
very strange

same
C:\Users\fsze88>SQLCMD -S FSZE88-PC
1> EXEC sp_addrolemember 'db_Owner', 'fsze88'
2> go
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line
 75
¿¿¿¿¿¿ 'fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿
1> use fsze88
2> go
¿¿¿¿¿¿¿¿¿¿ 'fsze88'¿
1> EXEC sp_addrolemember 'db_Owner', 'fsze88'
2> go
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line 75
¿¿¿¿¿¿ 'fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿

Open in new window

0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33511743
Can you try the following to see if that login is listed ... start SQLCMD and then use the statement below.
SELECT name, sid, type, type_desc FROM sys.server_principals
   WHERE type = 'S';

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33511834
Thanks
1> SELECT name, sid, type, type_desc FROM sys.server_principals
2>    WHERE type = 'S';
3> go
name                                                                                                                             sid
                 type type_desc
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------
---------------- ---- ------------------------------------------------------------
sa                                                                                                                               0x01
                 S    SQL_LOGIN
##MS_PolicyEventProcessingLogin##                                                                                                0x0A6983CDF023464B9E86E4EEAB92C5DA
                 S    SQL_LOGIN
##MS_PolicyTsqlExecutionLogin##                                                                                                  0x8F651FE8547A4644A0C06CA83723A876
                 S    SQL_LOGIN
fsze88                                                                                                                           0xE57DD7E18C89D440AB4942AD213F64A2
                 S    SQL_LOGIN

(4 rows affected)
1>

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33565875
anyone interested in follow UP?
Thanks!
0
 
LVL 15

Author Comment

by:fsze88
ID: 33651960
no more reply?
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33652022
Sry for leaving you hanging on this one. Let me take a look in the morning and we'll see if we can come up with an answer for it...
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33652023
Sry for leaving you hanging on this one. Let me take a look in the morning and we'll see if we can come up with an answer for it...
0
 
LVL 15

Author Comment

by:fsze88
ID: 33652048
sure
0
 
LVL 11

Accepted Solution

by:
aelliso3 earned 500 total points
ID: 33654679
This should do it ..
 

USE fsze88
GO

EXEC sp_changedbowner 'fsze88' 
GO

Open in new window

0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 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