Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to grant sysadmin to a user?

Posted on 2010-08-23
23
Medium Priority
?
3,513 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 2000 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 2000 total points
ID: 33654679
This should do it ..
 

USE fsze88
GO

EXEC sp_changedbowner 'fsze88' 
GO

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 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