Dear Readers,
Check Out my new article on MSSQLTips.com
Visit MSQLTips
Thanks,
Jugal Shah
How to check SQL Server Authentication Mode using T-SQL and SSMS?
Leave a reply
Category Archives: Database
Database Level Fixed Roles
| DB Levek Fixed Roles | Rights and Description |
| db_accessadmin | Members can manage Windows groups and SQL Server logins. |
| db_backupoperator | Issue DBCC, CHECKPOINT, and BACKUP statements. |
| db_datareader | Select all data from any user table in the database |
| db_datawriter | Modify any data in any user table in the database |
| db_ddladmin | Issue all Data Definition Language (DDL) statements |
| db_denydatareader | Cannot select any data from any user table in the database |
| db_denydatawriter | Cannot modify any data in any user table in the database |
| db_owner | Has full permissions to the database |
| db_securityadmin | Members can modify role membership and manage permissions. |
How can we remove Orphan User Instead of fixing it?
You can list of the list of orphan user by executing below query.
EXEC sp_change_users_login 'Report'
To remove/Delete Orphan User you can use below query.
exec sp_revokedbaccess 'DJ'
How to check SQL Server Version Or Build No?
Option 1
EXEC master..sp_MSgetversion
Output
Character_Value
——————– ———– ———–
10.0.2789.0 1 3
Option 2
select @@version
Output
Microsoft SQL Server 2008 (SP1) – 10.0.2789.0 (X64)
Jul 12 2010 19:21:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Option 3
SELECT SERVERPROPERTY('productversion')
Output Build Version
10.0.2789.0
Option 5
SELECT SERVERPROPERTY ('productlevel')
Output-Service Pack
SP1
Option 6
SELECT SERVERPROPERTY ('edition')
Output-Edition
Enterprise Edition (64-bit)
How to get the lists of System Stored Procedures in SQL Server?
Get the list of System Stored Procedure
SELECT * FROM SYS.ALL_OBJECTS WHERE type='P' and object_id < 0
You can get the list of CLR procedures using below query
SELECT NAME FROM SYS.ALL_OBJECTS WHERE type='PC'
aiDeeva.com 