Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3553
  • Last Modified:

how to grant sysadmin to a user?

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
fsze88
Asked:
fsze88
2 Solutions
 
aelliso3Commented:
exec sp _addsrvrolemember N'fsze88', sysadmin
0
 
fsze88Author Commented:
how about

grant a full right of database (fsze88) to a user by sqlcmd?
0
 
Dave_LaSalleCommented:
Is this what you are looking for?
http://msdn.microsoft.com/en-us/library/ms189595.aspx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
fsze88Author Commented:
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
 
wittysloganCommented:
EXEC fsze88..sp_addrolemember 'db_Owner', 'UserName'
0
 
TIGERMARKCommented:
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
 
fsze88Author Commented:
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
 
aelliso3Commented:
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
 
fsze88Author Commented:
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
 
aelliso3Commented:
Is this ID an NT Authentication, or SQL auth?
0
 
fsze88Author Commented:
should be SQL auth
Untitled.png
0
 
aelliso3Commented:
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
 
fsze88Author Commented:
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
 
aelliso3Commented:
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
 
fsze88Author Commented:
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
 
aelliso3Commented:
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
 
fsze88Author Commented:
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
 
fsze88Author Commented:
anyone interested in follow UP?
Thanks!
0
 
fsze88Author Commented:
no more reply?
0
 
aelliso3Commented:
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
 
aelliso3Commented:
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
 
fsze88Author Commented:
sure
0
 
aelliso3Commented:
This should do it ..
 

USE fsze88
GO

EXEC sp_changedbowner 'fsze88' 
GO

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now